Import Error

1.IMP-00058; ORA-00904;
clause : Table Defination changed.
Suggestion : imp + show=y check table defination.

2.IMP-00019; IMP-00003; ORA-01722;
clause : Table.column DATATYPE is changed
Suggestion : imp + show=y check table.column DATATYPE

3.IMP-00003; ORA-01114; IMP-00017; ORA-O1115; OSD-04026; ORA-27069;
OS Platform :-> UNIX
Suggestion : ULIMIT is low.increase and try again.

4.IMP-00046; IMP-00008; IMP-00051;
Suggestion : use DIRECT=n and use BINARY MODE for transfer dump file through FTP.

Export Error

1.Export command never export SYS objects.

2.EXP-00014; EXP-00008; ORA-01013;
Suggestion : You have sqlnet.expire_time set in your "sqlnet.ora" file. Removing this parameter,
or
setting it to a period longer than the duration of the export will allow the export to run without error.

3.EXP-00008; ORA-06550; PLS-00201;
Clause : Upgradion process is not done in proper way.
Suggestion : Check upgrade manual doc. dictionary didn't get upgraded.

4.EXP-00056; ORA-31600; ORA-06512;
Suggestion : Run catpatch.sql (ORAHOME/RDBMS/ADMIN)

exp + date

E:\>echo %date%
01-27-07

E:\>exp scott/tiger file=d:\test_%date% log=d:\test_%date% tables=DEPT

Export: Release 9.0.1.1.1 - Production on Sat Jan 27 10:32:40 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.

E:\>d:

D:\>dir
Volume in drive D has no label.
Volume Serial Number is 54DF-1372

Directory of D:\

01-27-07 10:32 AM 6,144 test_01-27-07.DMP
01-27-07 10:32 AM 407 test_01-27-07.LOG
Note : We can edit default date format >>>start ---> control panel--->Regional and language option.
------------------------------------------------------------------------------
SQL> column today new_val dt
SQL> select to_char(sysdate,'ddmmyy') today from dual;

TODAY
------
270107

SQL> host exp userid=scott/tiger tables=emp file=d:\test_&dt..dmp log=d:\test_&d
t..log

Export: Release 9.0.1.1.1 - Production on Sat Jan 27 10:40:08 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.

SQL> host d:

SQL> host dir
Volume in drive D has no label.
Volume Serial Number is 54DF-1372

Directory of D:\

01/27/07 10:40 AM 8,192 test_270107.dmp
01/27/07 10:40 AM 407 test_270107.log

SQL>

Hope this helps
Taj

ORA-12638: Credential retrieval failed

1.Domain issue
When Server DOMAIN is different FROM client DOMAIN.

2.Password Change Issue
When 0S password is different FROM APPLICATION password.

3.NTS (Network Telephony Services)makes the Oracle client attempt to use your current Windows domain credentials to authenticate you with the Oracle server. This could fail for a couple of reasons:

- The Oracle server is not configured to support Windows authentication
- The credentials you use to login to your local machine are not sufficient to allow you to login to the server.

In my case, our Network Administrator had changed the password OS user, while rebooting the system, the Oracle popped up with the Credential Failure.

1.Replacing the line: NTS -----------> NONE (nts to none)

SQLNET.AUTHENTICATION_SERVICES= (NTS)

with

SQLNET.AUTHENTICATION_SERVICES= (NONE)

2. Install Oracle Client with LOCAL Administrator A/c.

"exp-00091 exporting questionable statistics"

"exp-00091 exporting questionable statistics"


The precalculated optimizer statistics are flagged as questionable at export time if:

* There are row errors while exporting
* The client character set or NCHAR character set does not match the server character set or
NCHAR character set
* A QUERY clause is specified
* Only certain partitions or subpartitions are exported

If a table is missing the columns specified in the QUERY clause, an error message will be produced, and no rows will be exported for the offending table.

Restrictions
The parameter QUERY cannot be specified for full, user, or tablespace mode exports.
The parameter QUERY must be applicable to all specified tables.
The parameter QUERY cannot be specified in a direct path export (DIRECT=y)
The parameter QUERY cannot be specified for tables with inner nested tables.
You cannot determine from the contents of the export file whether the data is the result of a QUERY export.


Suggestion:
When OS character set different that ORACLE character set then BEFORE export you should set NLS_LANG=.CHARACTERSET according Oracle Database Character set.
Example : Oracle 10gr2 on Linux version 4
OS NLS_LANG = US7ASCII
Oracle NLS_LANG = WE8IS0XXXXX

Just Before export set NLS_LANG like
$export NLS_LANG=.WE8ISOXXXXX
$export your_data

Example on windows

First check "NLS_CHARACTERSET" of database through below query

SQL> select VALUE
2 from nls_database_parameters
3 where PARAMETER = 'NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252


On command prompt

c:\> set NLS_LANG=.WE8MSWIN1252

"Archivelog for thread % sequence % will NOT be compressed"

"Archivelog for thread % sequence % will NOT be compressed"

Check Metalink Note : 284618.1 Oracle Version 10.1.0.x.x
Just an informational message and can be ignored.

Archivelog compression is not available in 10gR1.

Oracle Database 10g Release 2 (10.2) New Features

Oracle Database 10g Release 2 (10.2) New Features

1.Transparent data encryption

2.Increased maximum number of partitions per schema object

3.DML error logging

4.Enhancements to Automatic Shared Memory Management

5.Improved automatic tuning of undo retention results in fewer "ORA-01555: snapshot too old" messages.

6.The Segment Advisor now reports tables with excessive row chaining.

7.Enhancements to the online segment shrink capability

8.Enhancements to online table redefinition

9.Support for XMLTypes in the transportable tablespace facility

10.Enhancements to space management

11.Control files no longer need to be recreated when changing certain configuration parameters

12.Tablespace low-space alert thresholds by free space remaining

13.Fast partition split is now supported for partitioned index-organized tables.

14.Automatically enabled resource manager

15.Enhanced Resource Manager monitoring

16.Automatic Storage Management (ASM) files are now accessible through an XML DB virtual folder

17.Automatic Storage Management now has a command-line utility (ASMCMD)

18.Automatic Storage Management now supports high-redundancy (3-way mirrored) files in normal redundancy disk groups

19.Automatic Storage Management (ASM) supports multiple database versions

20.The DBMS_FILE_TRANSFER package can now copy files between a local file system and an Automatic Storage Management (ASM) disk group.

21.The ALTER DISKGROUP command has a new REBALANCE WAIT clause.

22.Enhancements to the Scheduler

23.Resource optimized DROP TABLE...PURGE for partitioned tables

New in the Oracle Database 10gr1

New in the Oracle Database 10gr1
1.Server manageability features

2.Automatic system task maintenance

3.Bigfile tablespaces

4.Multiple default temporary tablespace support for SQL operations

5.Rename tablespace

6.Cross-platform transportable tablespaces

7.SYSAUX tablespace

8.Automatic Storage Management

9.Drop database

10.Oracle Flashback Transaction Query

11.Oracle Flashback Version Query

12.Oracle Flashback Table

13.Oracle Flashback Drop

14.Enhanced online redefinition

15.Automatic statistics collection

16.Scheduler

17.Database Resource Manager enhancement

18.New initialization parameter RESUMABLE_TIMEOUT

19.Application services

20.Simplified recovery through resetlogs

21.Automated shared server configuration and simplified shared server configuration parameters.

22.Consolidation of session-specific trace output

23.Block remote access to restricted instances

24.Dynamic SGA enhancements

25.Irreversible database compatibility

26.Flash recovery area

27.Sorted hash clusters

28.Copying Files Using the Database Server

29.Deprecation of MAXTRANS physical attribute parameter

30.Deprecation of use of rollback segments (manual undo management mode)

31.Deprecation of the UNDO_SUPPRESS_ERRORS initialization parameter

32.Deprecation of the PARALLEL_AUTOMATIC_TUNING initialization parameter

Temporary Tablespace

Temporary Tablespace

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrence of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations
You can view the allocation and deallocation of space in a temporary tablespace sort segment

1.V$sort_segment
2.V$tempseg_usage
3.V$tempfile
4.dba_temp_files
5.V$temp_space_header

Creating a Locally Managed Temporary Tablespace

create temporary tablespace TEMP1
tempfile 'c:\oracle\product\10.1.0\oradata\catdb\temp02.dbf' size 3m reuse
autoextend off
uniform size 50k;

The AUTOALLOCATE clause is not allowed for temporary tablespaces


The following statement drops a temporary file and deletes the operating system file:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP
INCLUDING DATAFILES;

COLD_BACKUP_windows

Database Version : 10.1.0.2.0
OS Platform : WinXP sp2
-------------------------------
Archive log : Disabled
Cold Backup
--------------------------------
I create three SQL scripts
1.shutdown.sql
2.startup.sql
3.copy.sql
----------------
Content of files.
1.shutown.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>set headning off echo off verify off feedback off pagesize 0
SQL>spool d:\bkp_scripts\db01\shutdown.sql
SQL>PROMPT shutdown immediate;
shutdown immediate
SQL>PROMPT exit;
exit
SQL>spool off;
-----------------------------------
2.startup.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>set heading off echo off verify off feedback off pagesize 0
SQL>spool d:\bkp_scripts\db01\startup.sql
SQL>PROMPT startup open pfile=d:\oracle\product\10.1.0\db_1\database\initDB01.ora
startup open pfile=d:\oracle\product\10.1.0\db_1\database\initDB01.ora
SQL>exit;
exit
SQL>spool off;
--------------------------------------------
3.copy.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>spool d:\bkp_scripts\db01\copy.sql
SQL> select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$datafile
2 union all
3 select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$controlfile
4 union all
5 select ' host COPY ' || member || ' d:\coldbkp_db01\ ' from v$logfile;
6 union all
7 select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$tempfile;

host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\SYSTEM01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\UNDOTBS01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\SYSAUX01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\USERS01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL02.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL03.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO03.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO02.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO01.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\TEMP01.DBF d:\coldbkp_db01\

SQL> PROMPT host copy c:\oracle\product\10.1.0\db_1\database\initDB01.ora d:\coldbkp_db01\
host copy c:\oracle\product\10.1.0\db_1\database\initDB01.ora d:\coldbkp_db01\
SQL> PROMPT host copy c:\oracle\product\10.1.0\db_1\database\pwdDB01.ora d:\coldbkp_db01\
host copy c:\oracle\product\10.1.0\db_1\database\pwdDB01.ora d:\coldbkp_db01\
SQL>PROMPT exit;
exit
SQL> spool off
----------------------------------------------------------------
I schedule my backup every friday 11 pm. i create COLDBKP.CMD file
4.coldbkp.cmd
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\shutdown.sql
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\copy.sql
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\startup.sql
-------------------------------------------------------
Schedule through SCHEDULE TASK windows utility.

Note : above scripts is my own workshop example. Test first any scripts before using.i am not responsible for any damage.


Note : If you don't include redolog files in COLD BACKUP. then you must open your database with RESETLOGS ( recover database through COLDBACKUP)

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORACLE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host del c:\oracle\product\10.1.0\oradata\oracle\*.LOG

SQL> host xcopy c:\oracle\product\10.1.0\oradata\oracle c:\ORACLEBKP
C:\oracle\product\10.1.0\oradata\oracle\CONTROL01.CTL
C:\oracle\product\10.1.0\oradata\oracle\CONTROL02.CTL
C:\oracle\product\10.1.0\oradata\oracle\CONTROL03.CTL
C:\oracle\product\10.1.0\oradata\oracle\SYSAUX01.DBF
C:\oracle\product\10.1.0\oradata\oracle\SYSTEM01.DBF
C:\oracle\product\10.1.0\oradata\oracle\TEMP01.DBF
C:\oracle\product\10.1.0\oradata\oracle\UNDOTBS01.DBF
C:\oracle\product\10.1.0\oradata\oracle\USERS01.DBF
8 File(s) copied

SQL> HOST del c:\oracle\product\10.1.0\oradata\oracle
c:\oracle\product\10.1.0\oradata\oracle\*, Are you sure (Y/N)? Y


SQL> host xcopy c:\ORACLEBKP c:\oracle\product\10.1.0\oradata\oracle
C:\ORACLEBKP\CONTROL01.CTL
C:\ORACLEBKP\CONTROL02.CTL
C:\ORACLEBKP\CONTROL03.CTL
C:\ORACLEBKP\SYSAUX01.DBF
C:\ORACLEBKP\SYSTEM01.DBF
C:\ORACLEBKP\TEMP01.DBF
C:\ORACLEBKP\UNDOTBS01.DBF
C:\ORACLEBKP\USERS01.DBF
8 File(s) copied


SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

SQL> recover until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
oracle OPEN

SQL>

Hot_full_rman_bkp_scirpt

Database Version : 10.1.0.2.0
OS platform : Window XP sp2
--------------------------------
Archive log >>> Enabled
Hot Backup
--------------------------------
My backup procedure.
Fri Sat Sun Mon Tue Wed Thur Fri

Incremental Level 0 - - - - - - 0
Incremental Level - 1 1 - 1 1 1 1
Inc_level_cumulative - - - 1 - - - -
---------------------------------------------------------------------
Incremental Level 0 Script

Backup.rcv file content
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CHANNEL DEVICE TYPE disk RATE 2M;

RUN
{
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 100M;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK MAXPIECESIZE 100M;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK MAXPIECESIZE 100M;
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG Weekly_Friday_0_Hotdb02_bkp;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP DATABASE ARCHIVELOG ALL DELETE ALL INPUT TAG Weekly_Friday_0_Hotdb02_bkp;
BACKUP VALIDATE DATABASE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}




-------------------------------------------------
Incremental Level 1 Script
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CHANNEL DEVICE TYPE disk RATE 2M;

RUN
{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 100M;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK MAXPIECESIZE 100M;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK MAXPIECESIZE 100M;
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG Increment_Lvl_1_Hotdb02_bkp;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP DATABASE ARCHIVELOG ALL TAG Increment_Lvl_1_Hotdb02_bkp;
}




-----------------------------------------------------------------
Incremental_cumulative_level_1 script


CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CHANNEL DEVICE TYPE disk RATE 2M;

RUN
{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 100M;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK MAXPIECESIZE 100M;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK MAXPIECESIZE 100M;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG Increment_Lvl_1_CUM_Hotdb02_bkp;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP DATABASE ARCHIVELOG ALL TAG Increment_Lvl_1_CUM_Hotdb02_bkp;
}


---------------------------------------------------------

Above backup procedure i scheduled every day 11.00 pm according backup procedure.
for that create one windows NT COMMAND script.
My command script file is.
backup.cmdbelow is contents.
c:\oracle\product\10.1.0\db_1\bin\RMAN TARGET /@db02 CATALOG rman/cat@catdb CMDFILE=d:\bkp_scripts\db02\backup.rcv LOG=d:\bkp_scripts\db02\backup.log

About RMAN Backups

About RMAN Backups
An image copy is an exact copy of a single datafile, archived redo log file, or control file

Backups of Archived Logs
Deletion of Archived Logs After Backups

--DELETE INPUT option
RMAN backs up exactly one copy of each specified log sequence number and thread from an archive destination to tape, and then deletes the specific file.
it backed up while leaving the other copies on disk
--DELETE ALL INPUT option
RMAN backs up exactly one copy of each specified log sequence number and thread, and then deletes that log from all archive destinations

Filenames for Image Copies
FORMAT variables are also used to specify the names of image copies. The default format %U.

Tags for RMAN Backups
The maximum length of a tag is 30 bytes.
Tag Weekly_Hot_db02_bkp

Size of Backup Pieces
RMAN will, by default, put the entire contents of a backup set into one backup piece
specify the MAXPIECESIZE option of the CONFIGURE CHANNEL or ALLOCATE CHANNEL commands


I/O Read Rate of Backups
By default, RMAN uses all available I/O bandwidth to read/write to disk. You can limit the I/O resources consumed by a backup job with the RATE option of the ALLOCATE CHANNEL or CONFIGURE CHANNEL commands. The RATE option specifies the maximum number of bytes for each second that RMAN reads on the channel.

CONFIGURE CHANNEL DEVICE TYPE disk RATE 2M;


RMAN Backup Types

Full or incremental
Open or closed
Consistent or inconsistent

Note: A full backup is different from a whole database backup, which is a backup of all datafiles and the current control file.

Closed Backup
A backup of any part of the target database when it is mounted but not open. Closed backups can be consistent or inconsistent
The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.


A level 1 incremental backup can be either of the following types:

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0
Incremental backups are differential by default.


Control File and Server Parameter File Autobackups

RMAN can automatically back up the control file and server parameter file (SPFILE) in situations in which the RMAN repository data for your database has been updated in a way that affects RMAN's ability to restore your database.
with a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.

If control file autobackups are OFF and the backup includes datafile 1, then RMAN includes the current control file and SPFILE in the same backup set as the datafiles.
The control file autobackup filename has a default format of %F for all device types.

Backup Retention Policies
RMAN considers backups of datafiles and control files as obsolete, that is, no longer needed for recovery, according to criteria that you specify in the CONFIGURE command. You can then use the REPORT OBSOLETE command to view obsolete files and DELETE OBSOLETE to delete them.

RMAN>report obsolete;
RMAN>delete obsolete;
If you have a flash recovery area configured, however, then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.

The term obsolete does not mean the same as expired.
RMAN>crosscheck backup;
RMAN> crosscheck archivelog all;
In short, obsolete means "not needed," whereas expired means "not found."
RMAN> delete expired backup;
RMAN> delete expired archivelog all;
The CROSSCHECK command does not delete operating system files or remove repository records. You must use the DELETE command for these operations


You can also disable the retention policy completely.
CONFIGURE RETENTION POLICY TO REDUNDANCY

Recovery Window
Backup Redundancy
A redundancy-based retention policy specifies how many backups of each datafile must be retained
The default retention policy is REDUNDANCY = 1

Batch Deletes of Obsolete Backups
Run the REPORT OBSOLETE command to determine which backups are currently obsolete according to the retention policy.

Backup Optimization
Backup optimization is a feature that avoids creating identical backup copies of files that have not changed since the last time they were backed up. If you enable backup optimization, then the BACKUP command skips the backup of a file when the identical file has already been backed up to the allocated device type.

Type of File Criteria to Determine an Identical File
Datafile Same DBID, checkpoint SCN, creation SCN, and RESETLOGS SCN and time. The datafile must be offline-normal, read-only, or closed normally.

Archived redo log Same thread, sequence number, and RESETLOGS SCN and time.

Backup set Same backup set recid and stamp.


RMAN Backup Errors
RMAN detects and responds to two primary types of backup errors: I/O errors and corrupt blocks
If RMAN encounters datafile blocks that have not already been identified as corrupt, then RMAN stops the backup
Check about corruption block on V$DATABASE_BLOCK_CORRUPTION view

Tests and Integrity Checks for Backups
You can use the BACKUP VALIDATE and RESTORE VALIDATE commands to test backup and restore operations
RMAN> backup validate database;
RMAN> backup validate database archivelog all;

RMAN> restore validate database;
RMAN> restore validate database archivelog all;

Detecting Physical and Logical Block Corruption
At the end of a backup, RMAN stores the corruption information in the recovery catalog and control file. Access this data using the V$DATABASE_BLOCK_CORRUPTION view.


Detection of Logical Block Corruption
the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry.
If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used,
By default, error checking for logical corruption is disabled.

Recovery Manager Architecture

RMAN Enviourment
Recovery Manager (RMAN) is a client application that performs backup and recovery operations


Components of the RMAN Environment
1.Target database
2.RMAN client
3.Recovery catalog database
4.Recovery catalog schema
5.Standby database
6.Duplicate database
7.Media management application
8.Media management catalog
9.Enterprise Manager


Using RMAN with Command Files

E:\>c:\oracle\product\10.1.0\db_1\bin\RMAN TARGET /@db02 CATALOG=rman/cat@catdb
CMDFILE=d:\test.rcv LOG=d:\test.txt
RMAN> 2> 3> 4> 5> 6> 7>
E:\>

Content of CMDFILE.

configure controlfile autobackup on;
backup database;
backup database archivelog all;

Controlling RMAN Output
v$RMAN_OUTPUT
We can also see job status completed and currently running.
v$RMAN_STATUS


Contents of the Recovery Catalog

1.Datafile and archived redo log backup sets and backup pieces
2.Datafile copies
3.Archived redo logs and their copies
4.Tablespaces and datafiles on the target database
5.Stored scripts, which are named user-created sequences of RMAN commands
6.Persistent RMAN configuration settings

Resynchronization of the Recovery Catalog
When Should We Resynchronize?
1.Resynchronizing When the Recovery Catalog is Unavailable
2.Resynchronizing in ARCHIVELOG Mode When You Back Up Infrequently
3.Resynchronizing After Physical Database Changes
--Adding or dropping a tablespace
--Adding a new datafile to an existing tablespace
--Adding or dropping a rollback segment

"Encountered problem(s) when trying to collect OUI inventory.

"Encountered problem(s) when trying to collect OUI inventory.: Failed to collect OUI inventory: no oraInstaller in java.library.path Failed to collect.
1.Stop the dbconsole:/bin/emctl stop dbconsole
2.Backup the file emctl.bat and emtgtctl.bat
3.Edit the files emctl.bat and emtgtctl.bat by changing into the line set PATH the following: %ORACLE_HOME%\oui\lib\win32 with %ORACLE_HOME%\oui\lib\win64
4.Try to start dbconsole and see if you issue still exist or not.

ORA-03297

ORA-03297: file contains used data beyond requested RESIZE value

SQL> select bytes/1024 from dba_data_files where file_id = 6;

BYTES/1024
----------
7168

SQL> select sum(bytes/1024) from dba_segments where tablespace_name = 'EXAMPLE';


SUM(BYTES/1024)
---------------
5440

SQL> alter database
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\example01.dbf' resize 5500k
;
alter database
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> ed
Wrote file afiedt.buf

1 select
2 a.file_name,
3 a.bytes file_size_in_bytes,
4 (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
5 a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
6 from dba_data_files a,
7 (select file_id,max(block_id) maximum
8 from dba_extents
9 group by file_id) b,
10 dba_extents c
11 where a.file_id = b.file_id
12 and c.file_id = b.file_id
13 and c.block_id = b.maximum
14* and c.tablespace_name = 'EXAMPLE'
SQL> /
Enter value for _block_size: 8192
old 4: (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
new 4: (c.block_id+(c.blocks-1)) * 8192 HWM_BYTES,
Enter value for _block_size: 8192
old 5: a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
new 5: a.bytes - ((c.block_id+(c.blocks-1)) * 8192) SAVING

FILE_NAME
--------------------------------------------------------------------------------

FILE_SIZE_IN_BYTES HWM_BYTES SAVING
------------------ ---------- ----------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB02\EXAMPLE01.DBF
7340032 6356992 983040


SQL> alter database
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\example01.dbf' resize 63569
92;

Database altered.

Table Size

SQL> create table ORACLE as select * from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('SCOTT','ORACLE');

PL/SQL procedure successfully completed.
SQL> column segment_name format a25
SQL> column table_name format a25
SQL> select segment_name,round((bytes/1024),2) ||'kb' "SIZE"
2 from user_segments
3 where segment_name = 'ORACLE';

SEGMENT_NAME SIZE
------------------------- ------------------------------------------
ORACLE 5120kb

SQL> --Table Size
SQL> select table_name, round((blocks*8),2)||'kb' "SIZE"
2 from user_tables
3 where table_name = 'ORACLE';

TABLE_NAME SIZE
------------------------- ------------------------------------------
ORACLE 4504kb

SQL> --Actual Data Size in Table
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "SIZE"
2 from user_tables
3 where table_name = 'ORACLE';

TABLE_NAME SIZE
------------------------- ------------------------------------------
ORACLE 3575.67kb

SQL>

Note :
SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

Dictionary Tablespace

Dictionary Managed Tablespace

SQL> create tablespace DICTBS
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\dictbs01.dbf' size 1m
3 extent management DICTIONARY
4 DEFAULT STORAGE (
5 INITIAL 50K
6 NEXT 100K
7 MINEXTENTS 1
8 MAXEXTENTS 100
9 PCTINCREASE 0);

For Coalesce tablespace.
SQL>alter tablespace &tablespace_name COALESCE;


Note:

The ALTER TABLESPACE ... COALESCE statement does not coalesce free extents that are separated by data extents. If you observe many free extents located between data extents, you must reorganize the tablespace (for example, by exporting and importing its data) to create useful free space extents.
Monitoring Free Space
DBA_FREE_SPACE

Example:
select block_id, bytes,blocks
from dba_free_space
where tablespace_name = '&tbs_name'
order by block_id;

Statistics for coalescing activity
DBA_FREE_SPACE_COALESCED

BIGFILE TABLESPACE

Bigfile Tablespace.
If tablespace with 8k blocks can cantain a 32 tb datafile.
if tablespace with 32k blocks can cantain a 128 tb datafile.

Bigfile only supported Locally Managed tablespace with three exception.
Bigfile also supported below three tbs is extent management is local or segment space management is manual;

Undo Tbs
Temp Tbs + Extent Management LOCAL + Segment Space management MANUAL
SYSTEM tbs

Create Statement for Bigfile tablespace.
1.You have to specify "BIGFILE" keyword in create tablespace statement.
2.No need to specify "EXTENT MANAGEMENT" or "SEGMENT MANAGEMENT" clause.
3.If you specify extent management "DICTIONARY" or segment management "AUTO" database return error.
4.You can only specify one datafile.
5.If default tablespace type was set to "BIGFILE" at database creation time then no need to specify
"BIGFILE" keyword at create tablespace statement.
.But if you want to create smallfile datafile you have to specify "SMALLFILE" keyword at create tablespace statement

SQL> create BIGFILE tablespace BIGTBS
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\bigtbs01.dbf' size 10m
3 autoextend on next 1m maxsize 50m
4 autoallocate;

Tablespace created.

SQL> select extent_management, segment_space_management
2 from dba_tablespaces
3 where tablespace_name = 'BIGTBS';

EXTENT_MAN SEGMEN
---------- ------
LOCAL AUTO

Locally Managed Tablespace

Oracle Version : 10.1.0.2.0
OS Platform : WinXP sp2
-------------------------------------------------------------------------------------
Locally Managed Tablespace
Benefits
1.Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.

2.Coalescing free extents is unnecessary for locally managed tablespaces
-------------------------------------------------------------------------------------
1.We can create Locally Managed Tbs specify "LOCAL" extent management clause.
create tablespace &tablespace_name
datafile 'path' size xxxk
EXTENT MANAGEMENT LOCAL;

2.If we want database extent manage automatically we should choose "AUTOALLOCATE" clause. It is default.
create tablespace &tablespace_name
datafile 'path' size xxxk
extent management LOCAL
AUTOALLOCATE;

3.If we want exact control on unused space and we can predict allocation for objects then "UNIFORM" size is best.This setting ensures that you will never have unusable space in your tablespace.
create tablespace &tablespace_name
datafile 'path' size xxxk
UNIFORM SIZE XXXK;
Note: If you omit SIZE clause with UNIFORM, then the default size is 1M

4.When we not specify explicity "EXTENT MANAGEMENT CLAUSE" then database determines extent management as fellows.
1.If we omit DEFAULT storage clause in create tablespace statement then database create tablespace in "Locally Managaned + Autoallocated".
create tablespace &tablespace
datafile 'path' size xxxk;

2.If we specify DEFAULT storage clause then
1.If MINIMUN EXTENT + INITIAL + NEXT are EQUAL AND PCTINCREASE is 0.then database create "Locally Managed + Uniform".

SQL> create tablespace TEST
2 datafile 'c:\test.dbf' size 2m
3 default storage (
4 initial 100k
5 next 100k
6 pctincrease 0);

Tablespace created.
SQL> create table scott.f as select * from all_objects where rownum <= 9000;

Table created.

SQL> select extent_id,block_id,bytes,blocks
2 from dba_extents
3 where owner = 'SCOTT' and segment_name = 'F';

EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 9 106496 13
1 22 106496 13
2 35 106496 13
3 48 106496 13
4 61 106496 13
5 74 106496 13
6 87 106496 13
7 100 106496 13
8 113 106496 13

9 rows selected.

SQL> alter database default tablespace test;


2.if MINIMUN EXTENT + INITIAL + NEXT are NOT EQUAL OR PCTINCREASE is 0.then database create "Locally Managed + Autoallocated".ignore any storage settings.

SQL> create tablespace TEST
2 datafile 'c:\test.dbf' size 7m
3 default storage (
4 initial 100k
5 next 200k
6 pctincrease 0);

Tablespace created.

SQL> alter database default tablespace test;

Database altered.

SQL> select extent_id,block_id,bytes,blocks
2 from dba_extents
3 where owner = 'SCOTT' and segment_name

EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 9 65536 8
1 17 65536 8
2 25 65536 8
3 33 65536 8
4 41 65536 8
5 49 65536 8
6 57 65536 8
7 65 65536 8
8 73 65536 8
9 81 65536 8
10 89 65536 8

EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
11 97 65536 8
12 105 65536 8
13 113 65536 8
14 121 65536 8
15 129 65536 8
16 137 1048576 128
17 265 1048576 128
18 393 1048576 128
19 521 1048576 128
20 649 1048576 128

21 rows selected.

5.Segment Space Management
Two option
1.Manual ( default)
Manual segment-space management uses free lists to manage free space within segments.
you must specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for schema objects created in the tablespace

2.Auto
Automatic segment-space management uses bitmaps to manage the free space within segments.
You can specify automatic segment-space management only for permanent, locally managed tablespaces

Note :
The segment-space management you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot subsequently change the segment-space management mode of a tablespace.

emctl start agent

The service name is invalid


E:\>set oracle_sid=orcl1

E:\>emctl start agent
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
The service name is invalid.

More help is available by typing NET HELPMSG 2185.


E:\>net helpmsg 2185

The service name is invalid.


EXPLANATION

You tried to start a service that is not configured on this system.

ACTION

Check the spelling of the service name or check the configuration information fo
r the service using the Services option from Server Manager.



E:\>net start oracleserviceorcl1
The OracleServiceORCL1 service is starting...
The OracleServiceORCL1 service was started successfully.


E:\>set oracle_sid=orcl1

E:\>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://taj.domainname.local:5501/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The OracleDBConsoleor
cl1 service is starting........................................
The OracleDBConsoleorcl1 service was started successfully.


E:\>emctl status agent
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.1.0.2.0
OMS Version : 10.1.0.2.0
Protocol Version : 10.1.0.2.0
Agent Home : C:\oracle\product\10.1.0\Db_1\taj.domainname.local_o
rcl1
Agent binaries : C:\oracle\product\10.1.0\Db_1
Agent Process ID : 2960
Agent Process ID : 2960
Agent URL : http://taj.domainname.local:1830/emd/main
Started at : 2007-01-13 16:48:27
Started by user : SYSTEM
Last Reload : 2007-01-13 16:48:27
Last successful upload : (none)
Last attempted upload : (none)
Total Megabytes of XML files uploaded so far : 0.00
Number of XML files pending upload : 160
Size of XML files pending upload(MB) : 3.23
Available disk space on upload filesystem : 59.73%
---------------------------------------------------------------
Agent is Running and Ready

E:\>

OC4J Configuration issue...

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips






First you should check database/instance configure for DATABASE CONTROL


If a hostname_sid directory does not exist in the Oracle Database 10g home directory, then the Oracle Enterprise Manager 10g Database Control Console was not configured for the database instance.

E:\>set oracle_sid=db01

E:\>emctl getemhome
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
EMHOME=C:\oracle\product\10.1.0\Db_1/taj.domainname.local_db01

If you get above output mean DATABASE configure FOR DB CONTROL otherwise DATABASE is not configure for DB CONTROL and you will see message like "EM Configuration issue".

We can configure DATABASE/INSTANCE for DB CONTROL through TWO WAY
1.DBCA
2.EMCA

DBCA
E:\>set oracle_sid=sh

E:\>emctl status dbconsole
OC4J Configuration issue. C:\oracle\product\10.1.0\Db_1/oc4j/j2ee/OC4J_DBConsole
_taj.domainname.local_sh not found.

E:\>dbca
Note : dbca ---> Configure Database option ---> Select DB and give PWD ---> Check Mark on Configure database with enterprise manager. ---> Finish
E:\>set oracle_sid=sh

E:\>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://taj.domainname.local:5503/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory C:\oracle\product\10.1.0\Db_1/taj.domainname.local_sh/sysman/log

--------------------------------------------------------------
You can also Configure Database Control through EMCA command line utility.
E:\>set oracle_sid=db01

E:\>emca

STARTED EMCA at Thu Jan 11 10:39:37 GST 2007
Enter the following information about the database to be configured
Listener port number: 1521
Database SID: db01
Service name: db01
Email address for notification:
Email gateway for notification:
Password for dbsnmp:
Password for sysman:
Password for sys:

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1
Enterprise Manager ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1

Database host name ................ taj.domainname.local
Listener port number ................ 1521
Database SID ................ db01
Service name ................ db01
Email address for notification ...............
Email gateway for notification ...............

-----------------------------------------------------------------
Do you wish to continue? [yes/no]: yes
Jan 11, 2007 10:40:22 AM oracle.sysman.emcp.EMConfig checkConfiguration
SEVERE: Repository already exists. Fix the error(s) and run EM Configuration As
sistant again in standalone mode.
Could not complete the configuration. Refer to the log file for details

emca -r :skip creation of repository schema
E:\>emca -r

STARTED EMCA at Thu Jan 11 10:42:33 GST 2007
Enter the following information about the database to be configured
Listener port number: 1521
Database SID: db01
Service name: db01
Email address for notification:
Email gateway for notification:
Password for dbsnmp:
Password for sysman:
Password for sys: :
Password for sys:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1
Enterprise Manager ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1

Database host name ................ taj.domainname.local
Listener port number ................ 1521
Database SID ................ db01
Service name ................ db01
Email address for notification ...............
Email gateway for notification ...............

-----------------------------------------------------------------
Do you wish to continue? [yes/no]: yes
Jan 11, 2007 10:42:59 AM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\emdrep\config\repositor
y.variables ...
Jan 11, 2007 10:43:05 AM oracle.sysman.emcp.util.PortQuery findUsedPorts
INFO: Searching services file for used port
Jan 11, 2007 10:43:08 AM oracle.sysman.emcp.EMConfig addPortEntries
INFO: Updating file C:\oracle\product\10.1.0\Db_1\install\portlist.ini ...
Jan 11, 2007 10:43:09 AM oracle.sysman.emcp.EMConfig updateEmdProps
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\config\emd.properties .
..
Jan 11, 2007 10:43:10 AM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: targets.xml file is updated successfully
Jan 11, 2007 10:43:10 AM oracle.sysman.emcp.EMConfig updateEmomsProps
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\config\emoms.properties
...
Jan 11, 2007 10:43:10 AM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: emoms.properties file is updated successfully
Jan 11, 2007 10:43:12 AM oracle.sysman.emcp.EMConfig startOMS
INFO: Starting the DBConsole ...
Jan 11, 2007 10:44:53 AM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
Jan 11, 2007 10:44:53 AM oracle.sysman.emcp.EMConfig perform
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://taj.domainname.lo
cal:5504/em <<<<<<<<<<<>Agent Unreachable
First check agent services is start or not.
e:\>set oracle_sid=db01
e:\>emctl status agent
if stop or not running then start
e:\>emctl start agent
or check log file for more info.
OracleHome\Db_1\localhost_sid\sysman\log
------------------------------------------------------------

Io exception: SO Exception was generated
INFO: Block size for SYSAUX is greater than or equal to 8K
INFO: Job queue size is greater than or equal to 10
or check log file for more info.
Oracle_Home\Db_1\cfgtoollogs\emca_repos_xxxx.log
For above error because of your not drop repository properly.

SQL> DECLARE
2 CURSOR c1 IS
3 SELECT owner, synonym_name name
4 FROM dba_synonyms
5 WHERE table_owner = 'SYSMAN';
6
7 BEGIN
8 FOR r1 IN c1
9 LOOP
10 IF r1.owner = 'PUBLIC' THEN
11 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM 'r1.name;
12 ELSE
13 EXECUTE IMMEDIATE 'DROP SYNONYM 'r1.owner'.'r1.name;
14 END IF;
15 END LOOP;
16 END;
17 /

PL/SQL procedure successfully completed.

SQL> DROP ROLE mgmt_user;

Role dropped.

SQL> drop user mgmt_view cascade;

User dropped.

SQL> drop user sysman cascade;

User dropped.

------------------------------------------------------------------
After drop complete repository you can create new repository through EMCA.
E:\>emca

STARTED EMCA at Thu Jan 11 18:13:11 GST 2007
Enter the following information about the database to be configured
Listener port number: 1521
Database SID: db01
Service name: db01
Email address for notification:
Email gateway for notification:
Password for dbsnmp:
Password for sysman:
Password for sys: :
Password for sys:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1
Enterprise Manager ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1

Database host name ................ taj.domainname.local
Listener port number ................ 1521
Database SID ................ db01
Service name ................ db01
Email address for notification ...............
Email gateway for notification ...............

-----------------------------------------------------------------
Do you wish to continue? [yes/no]: yes
DBConsole is already configured for the database db01
Would you like to re-configure DBConsole for the specified database? [yes/no]: y
es
Make sure that DBConsole has been shutdown
When you are ready to continue, press

Jan 11, 2007 6:13:27 PM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\emdrep\config\repositor
y.variables ...
Jan 11, 2007 6:19:07 PM oracle.sysman.emcp.EMConfig createRepository
INFO: Creating repository ...
Jan 11, 2007 6:19:07 PM oracle.sysman.emcp.EMConfig perform
INFO: Repository was created successfully
Jan 11, 2007 6:19:15 PM oracle.sysman.emcp.EMConfig stopOMS
INFO: Stopping the DBConsole ...
Jan 11, 2007 6:19:21 PM oracle.sysman.emcp.EMConfig addPortEntries
INFO: Updating file C:\oracle\product\10.1.0\Db_1\install\portlist.ini ...
Jan 11, 2007 6:19:21 PM oracle.sysman.emcp.EMConfig updateEmdProps
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\config\emd.properties .
..
Jan 11, 2007 6:19:23 PM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: targets.xml file is updated successfully
Jan 11, 2007 6:19:23 PM oracle.sysman.emcp.EMConfig updateEmomsProps
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\config\emoms.properties
...
Jan 11, 2007 6:19:23 PM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: emoms.properties file is updated successfully
Jan 11, 2007 6:19:34 PM oracle.sysman.emcp.EMConfig startOMS
INFO: Starting the DBConsole ...
Jan 11, 2007 6:21:13 PM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
Jan 11, 2007 6:21:13 PM oracle.sysman.emcp.EMConfig perform
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://taj.domainname.lo
cal:5504/em <<<<<<<<<<<>

Note : In 10.1.0.2.0 RepManager is not working properly so that we are not able to use below command.

emca -config dbcontrol db -repos recreate

If is working in 10.1.0.3.0
for you have to manully drop repository and create again.

Related Links
1. http://dbataj.blogspot.com/2007/07/error-during-dbconsole-shutting-down.html

2. http://dbataj.blogspot.com/2007/07/change-sysman-password.html

3. http://dbataj.blogspot.com/2007/07/e-mail-notification.html

4. http://dbataj.blogspot.com/2007/06/enabling-isqlplus-dba-access.html

5. http://dbataj.blogspot.com/2007/04/export-database-with-sysdba-role.html

Retention Guarantee/NoGuarantee

SQL> select retention ,tablespace_name
2 from dba_tablespaces;

RETENTION TABLESPACE_NAME
----------- ------------------------------
NOT APPLY SYSTEM
NOGUARANTEE UNDOTBS1
NOT APPLY SYSAUX
NOT APPLY TEMP
NOGUARANTEE UNDOTBS2
NOT APPLY TEST
NOT APPLY MAKE

7 rows selected.

SQL> alter tablespace UNDOTBS2 retention guarantee;

Tablespace altered.

When we set any undo tablespace in GUARANTEE then oracle not overwrite any UNEXPIRED data.

Switch Undo Tablespace

SQL> select name from v$tablespace where name like 'UNDO%';

NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace = 'UNDOTBS2';

System altered.

On another session which connect as scott user

SQL> insert into test select * from all_objects;

47327 rows created.

SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE

14 rows selected.


On another session which connect as scott user


SQL> commit;

Commit complete.
SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

11 rows selected.
SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2

Note: When you change Undo Tbs say undotbs1 to undotbs2 then all existing trasaction they using UNDOTBS1 they still using undotbs1 and status show "OFFLINE PENDING".and all new transaction after changed undotbs "alter system undo_tablespace" statements issued used New Undo Tablespace.

You cann't drop or offline if status show offline pending.
when all transaction which using old undotbs are COMMIT;
then you can drop or offline old undotbs.

SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE

14 rows selected.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace

Hope this helps
Taj

sysaux tablespace

SYSAUX Tablespace

SYSAUX tablespace was installed as an auxiliary tablespace to the system tablespace when we created our database.
Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.

If sysaux tbs becomes unavailable, core database functionality will remain operational but dbs features that use the SYSAUX tbs could fail.

Viewing Components using SYSAUX tbs.
SQL> column occupant_name format a15
SQL> column occupant_desc format a30
SQL> column schema_name format a10
SQL> select schema_name,occupant_name,occupant_desc,space_usage_kbytes
from v$sysaux_occupants;

You can move components tbs other that SYSAUX first you know which component move is applicable or not.

SQL> column move_procedure format a25
SQL> column move_procedure_desc format a40
SQL> select move_procedure,move_procedure_desc from v$sysaux_occupants;

Two major components consume lot of space.

1.AWR ( automatic workload repository)
2.OEM ( oracle enterprise manager repository)

Transport Tablespace

SQL>alter tablespace TEST read only;

SQL> conn sys@catdb as sysdba
Enter password:
Connected.
SQL> create directory DATA as 'e:\DATA';
Directory created.
SQL> grant EXP_FULL_DATABASE, IMP_FULL_DATABASE to scott;
Grant succeeded.
SQL> grant READ, WRITE on directory DATA to scott;
Grant succeeded.
SQL> conn scott/tiger@catdb
Connected.
SQL> host EXPDP scott/tiger@catdb TRANSPORT_TABLESPACES=test DIRECTORY=data -> DUMPFILE=test1 LOGFILE=test1
Export: Release 10.1.0.2.0 - Production on Thursday, 04 January, 2007 17:17
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01": scott/********@catdb TRANSPORT_TABLESPACES=test DIRECTORY=data DUMPFILE=test1 LOGFILE=test1
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKP
rocessing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TRANSPORTABLE_01 is: E:\DATA\TEST1.DMP
Job "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:18

----------------------------------------------------
SQL> conn sys@sh as sysdba
Enter password:
Connected.
SQL> create directory DATA as 'e:\DATA';
Directory created.
SQL> grant EXP_FULL_DATABASE, IMP_FULL_DATABASE to scott;
Grant succeeded.
SQL> grant READ, WRITE on directory DATA to scott;
Grant succeeded.
SQL> conn scott/tiger@sh
Connected.
SQL> host impdp scott/tiger@sh DIRECTORY=data DUMPFILE=test1 -
> TRANSPORT_DATAFILES='c:\oracle\product\10.1.0\oradata\SH\users01.dbf' -
> LOGFILE=test2
Import: Release 10.1.0.2.0 - Production on Thursday, 04 January, 2007 17:42
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01": scott/********@sh DIRECTORY=data DUMPFILE=test1 TRANSPORT_DATAFILES='c:\oracle\product\10.1.0\oradata\SH\users01.dbf' LOGFILE=test2
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Job "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:42

SQL> alter tablespace TEST read write;
Tablespace altered.

Transport Tablespace

SQL> alter tablespace TEST read only;
Tablespace altered.
SQL> host exp TRANSPORT_TABLESPACE=y TABLESPACES=test -

> FILE=e:\test.dmp LOG=e:\test.log
Export: Release 10.1.0.2.0 - Production on Thu Jan 4 16:38:31 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Username: sys@sh as sysdba

Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character setNote: table data (rows) will not be exported

About to export transportable tablespace metadata...For tablespace TEST .... exporting cluster definitions.

exporting table definitions. .

exporting table DEPT. .

exporting table EMP. .

exporting table BONUS. .

exporting table SALGRADE. .

exporting table SCOT.

exporting referential integrity constraints.

exporting triggers.

end transportable tablespace metadata export

Export terminated successfully without warnings.

SQL> host xcopy c:\oracle\product\10.1.0\oradata\SH\users01.dbf c:\oracle\product\10.1.0\oradata\CATDB\

C:\oracle\product\10.1.0\oradata\SH\USERS01.DBF

1 File(s) copied

SQL> conn sys@catdb as sysdba

Enter password:

Connected.

SQL> host imp TRANSPORT_TABLESPACE=Y FILE=e:\test.dmp LOG=t.log -

> DATAFILES=c:\oracle\product\10.1.0\oradata\CATDB\users01.dbf
Import: Release 10.1.0.2.0 - Production on Thu Jan 4 16:56:12 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Username: sys@catdb as sysdbaPassword:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path

About to import transportable tablespace(s) metadata...

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

importing SYS's objects into SYS.

importing SCOTT's objects into SCOTT. .

importing table "DEPT". .

importing table "EMP". .

importing table "BONUS". .

importing table "SALGRADE". .

importing table "SCOT".

importing SYS's objects into SYS

Import terminated successfully without warnings.

SQL> alter tablespace TEST read write;
Tablespace altered.

Control file

All Controlfile or one of them are corrupted then we have to create new controlfile.
through control file statement.



STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "taj"
NORESETLOGS [archivelog/noarchivelog]
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO01.LOG' SIZE 10M,
GROUP 2 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO02.LOG' SIZE 10M,
GROUP 3 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO03.LOG' SIZE 10M
DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\SYSTEM01.DBF' SIZE xxx,
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\USERS01.DBF' SIZE
xxx,
.
.
.
.
CHARACTER SET [chracater_set]
================================================================
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 7271
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG' SIZE 10M,
GROUP 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG' SIZE 10M,
GROUP 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS02.DBF'
CHARACTER SET WE8MSWIN1252
;

ORA-12520

Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
--------------------------------------------------
SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 50


SQL> conn scott/tiger@test
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server



Clause : Process parameter value is LOW. we have to increase.
on Listner.log file
04-MAR-2007 10:23:38 * service_update * test * 0
04-MAR-2007 10:23:39 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=C:\oracle\product\10.1.0\Db_1\bin\sqlplus.exe)(HOST=0A49914)(USER=m.taj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1199)) * establish * test * 12520
TNS-12520: TNS:listener could not find available handler for requested type of server


Lsnrctl services
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:29 refused:0 state:blocked
LOCAL SERVER

Suggestion:

1.You should wait for some minutes and try to connect.
( but it is temporary solution)
2.You should increase PROCESSES parameter values.
( increase PROCESSES parameter and SHUTDOWN + STARTUP DATABASE)








Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
------------------------------------
Database configure in Shared Server Mode.
--------------------------------------------
SQL> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)
max_dispatchers integer

SQL> show parameter shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------max_shared_servers integer
shared_servers integer 1
------------------------------------------------------------------------------------------------
after change shared server = 0 then i got error "ora-12520".
SQL> alter system set shared_servers = 0;
System altered.

SQL> conn scott/tiger@sh
ERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserver
C:\>lsnrctl services
Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:3 refused:0 current:2 max:1002 state:blocked
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))

-------------------------------------------------------------------
For above error you have to check configure two parameters.
1.Shared_servers
2.Dispatchers

SQL> alter system set shared_servers = 1;
System altered.
SQL> alter system set dispatchers = "(PROTOCOL=TCP)" ;
System altered.

Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:4 refused:0 current:2 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))
The command completed successfully

Change Server Mode from Dedicated to Shared.

How can find in which mode database is running ?


1. If "SHARED_SERVERS" parameter value is "0". then database running on dedicated server mode.
2. select username,status,server from v$session;
Here SERVER column give server mode 1. dedicated or 2. Shared
3. LISTNER.LOG file
04-SEP-2007 18:46:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)


We have to configure two parameters for change server mode from Dedicated to Shared.
SHARED_SERVERS & DISPATCHERS parameters.

------------------------------------------------------------
SQL>show parameter shared_servers
NAME VALUE TYPE
----------------------------------------------
shared_servers integer 0

SQL> alter system set shared_servers = 1;
System altered.

SQL>show parameter shared_servers
NAME VALUE TYPE
----------------------------------------------
shared_servers integer 1


SQL>show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)
max_dispatchers integer

SQL> alter system set dispatchers = "(PROTOCOL=TCP) (DISPATCHERS=1) (SERVICE=ORCL1)";
System altered.

or you can edit tnsnames.ora file like
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521)) (CONNECT_DATA =
(SERVICE_NAME = orcl1)
(SERVER=SHARED)
)
)
Note : For Backward compatibility, if shared_server is not included in init parameter file or dispatchers is included and it specify aleast 1 dispatcher. then shared_server mode is enable.
but if not shared_server or nor dispatcher specified in init parameter file . then you have to set shared_server parameter aleast greater than 0 for enable shared server mode.

If We set only shared_server parameter but dispatchers parameters is not set then oracle server automatically create one dispatcher for shared server.

dispatchers string (PROTOCOL=TCP)

Database_Properties

SQL> col property_name format a25
SQL> col property_value format a40
SQL> select property_name, property_value from database_properties
2 order by 1;
PROPERTY_NAME PROPERTY_VALUE
------------------------- ----------------------------------------
DBTIMEZONE 00:00
DEFAULT_PERMANENT_TABLESP USERS
DEFAULT_TBS_TYPE SMALLFILE
DEFAULT_TEMP_TABLESPACE TEMP
DICT.BASE 2
EXPORT_VIEWS_VERSION 8
GLOBAL_DB_NAME ORCL1
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET WE8MSWIN1252
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 10.1.0.2.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFFAM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AMTZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
27 rows selected.

About Parameter

1.Parameters are specified in currently used by Instance SPFILE.

SQL> select name, value
2 from v$spparameter
3 where isspecified = 'TRUE';

2.Parameters are modifiable through "ALTER SESSION".

SQL> select name, value
2 from v$parameter
3 where isses_modifiable = 'TRUE' ;

3.Parameters are not modifiable through "ALTER SYSTEM"

SQL> select name, value
2 from v$parameter
3 where issys_modifiable = 'FALSE';

4.Parameters are modifiable through "ALTER SYSTEM" and Take Immediate Effect.( if instance startup through spfile).

SQL> select name, value
2 from v$parameter
3 where issys_modifiable = 'IMMEDIATE';

5.Parameters are modifiable through "ALTER SYSTEM + DEFERRED" and take effect in subsequent session.

SQL> col name format a20
SQL> col value format a40
SQL> select name, value
2 from v$parameter
3 where issys_modifiable = 'DEFERRED';

NAME VALUE
-------------------- ----------------------------------------
backup_tape_io_slaves TRUE
object_cache_optimal_size 102400
object_cache_max_size_percent 10
sort_area_size 65365
sort_area_retained_size 0
olap_page_pool_size 0
6 rows selected.
SQL> alter system set sort_area_size = 65600 deferred;
System altered.
SQL> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------sort_area_size integer 65365
SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> conn sys/oracle@orcl1 as sysdba
Connected.
SQL> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------sort_area_size integer 65600

Oracle Release.10.1.0.2.0

SQL> col product format a40
SQL> col version format a15
SQL> col status format a15
SQL> select * from product_component_version;
PRODUCT VERSION STATUS
---------------------------------------- --------------- ---------------
NLSRTL 10.1.0.2.0 Production
Oracle Database 10g Enterprise Edition 10.1.0.2.0 Prod
PL/SQL 10.1.0.2.0 Production
TNS for 32-bit Windows: 10.1.0.2.0 Production