Cloning Database through RMAN

A wonderful feature in RMAN to clone or duplicate the database on the same server or remote server. This feature is useful when someone truncated or dropped the table and they wanted to recover the table. In that case, we can clone the database in different location and recover the database until point of time. This feature is very useful to recover the selected object from the RMAN backup rather than roll back a whole database. It is possible to create a duplicate database on a remote server with the same file structure or remote server with different file structure or the local server with a different file structure.

This article is tested in windows environment. Now I am going to clone the ORCL database and restore in different location on the same server. The new database name is called CLONEDB. Please remember, the directory, file names, Database names may varry between environment to environment. So you might need to change the scripts based on your database name, file structure when you follow the below steps.

Prerequisite: You should have target database and RMAN should be implemented to backup the target database. How do we implement RMAN backup? Please see this link.

Here is the Target DB and catalog DB info in my server.Target DB = ORCL
CatalogDB = CATDB

Data file, redo log, control file directory location for target and catalog DB:
C:\oracle\product\10.2.0\oradata\orcl
C:\oracle\product\10.2.0\oradata\catdb

Tnsentry file location
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

Password file location
C:\oracle\product\10.2.0\db_1\database

SPFILE location:
C:\oracle\product\10.2.0\db_1\dbs

Here are the nine easy steps to clone the database. I have followed this steps in Oracle10gR2 in windows environment. So please test the below steps in your dev/test environment before proceed to production. The steps are pretty much same for unix environment except directory names and step5

Step1. First create the directory structures for the CLONEDB.

Create the folder clonedb in the following locations.
c:\oracle\product\10.2.0\oradata
C:\oracle\product\10.2.0\admin

Create folders(adump,bdump,cdump,dpdump,udump) in the following location.
C:\oracle\product\10.2.0\admin\clonedb

Step2. Create the password file for clonedb database. Orapwd is oracle utility and we can run this utility in command prompt for windows server. For unix environment, we need to run this in $ prompt.

orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDclonedb.ora password=password entries=10

Step3. Add the appropriate entries into the tnsnames.ora and listener.ora files.

Add this entry in listener.ora file.

(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = CLONEDB))

After adding the above entry, reload the listener(lsnrctl reload)

Add the below entry in tnsnames.ora file..

CLONEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = EW_Training)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)

Step 4. Build init.ora file for the duplicate database. Since we are duplicating the database on the same server as the original, we must convert the file names, so there is no conflict. These entries needs to be modified in pfile for cloning database.

db_name='clonedb'
dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
audit_file_dest='C:\oracle\product\10.2.0\admin\clonedb\adump'
background_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\bdump'
control_files='C:\oracle\product\10.2.0\oradata\clonedb\control01.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control02.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control03.ctl'
core_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\cdump'
user_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\udump'
db_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')
log_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')

The modified PFILE is located in C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA

Step 5. Create the service for CLONEDB instance. Use ORDIM utility for windows environment. This command should run on OS Level.

ORADIM -NEW -SID clonedb

Creating service is only applicable for windows environment. We do not need to create any service in unix environment. In unix, starting the database spawns a services. We must ignore this step for unix environment.

Step 6. Create the spfile by using pfile which is build in step 4. Login as sys user in ORCL database and run this script.

create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\SPFILECLONEDB.ORA'
from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA'

Step 7. Create file name called initclonedb.ORA in C:\oracle\product\10.2.0\db_1\database location. The file should have the below content.
SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileclonedb.ora'
Step 8. Login as sys user in clonedb database. Start the clonedb instance without nomount.

C:\>set oracle_sid=clonedb

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 28 15:22:37 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
SQL>

Step 9. Connect as RMAN and clone the database. If you use any reserve word(like CLONE, TEST etc) for cloning database name, then you should use in double quotes. This case, i am using database name as clonedb. But in case, if we use name CLONE, then it should be quoted like this... (RMAN> duplicate target database to "CLONE";)

C:\>rman target=sys/password@orcl catalog=rman/rman@catdb AUXILIARY /

RMAN> duplicate target database to clonedb;

Now the ORCL database is successfully cloned and cloned database name is CLONEDB. In case if we want to clone the database to specific point of time, then we can use the below command to clone.

DUPLICATE TARGET DATABASE TO CLONEDB UNTIL TIME 'SYSDATE-4';

Questions and Answers....

Question1
I wanted to change the file names while cloning the database. How do we do that? Let us say, the file names in the target database are...

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF

While cloning the database, i wanted to change the file name as below. How can i achieve this?

C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSAUXCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\UNDOTBSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE02.DBF

The directory structures are taken care by db_file_name_convert and log_file_name_covert parameters. The file name changes are not taken care by this parameters. Here is the below sample script to take care of this....

RMAN>run
{
SET NEWNAME FOR DATAFILE 1 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF';
duplicate target database to clonedb;
}

Question2 One of my programmer truncated the table accidently. I wanted to clone that database and recover that table. But the database size is 2TB. I do not want to clone the entire 2 TB data. Since i wanted to clone only the tablespace which blongs to my table. How do we exclude unwanted tablespaces? Please remember, we can not skip SYSTEM, SYSAUX, UNDO, TEMP and default permanent tablespace. The below script ingore the specific tablespaces.

Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP TABLESPACE USERS, INVENTORY;
}

Question3 How do i skip the read only tablespace while cloning the database? The below script skip the read only tablespaces.

Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP READONLY ;
}

Question4 Let us say, my target database is running in Host1. My catalog database is running in Host2. I wanted to clone the target database in different server which is called Host3. Where should i run the RMAN?

RMAN is client program that connects to the database. So it does not matter where you run the RMAN. We could actually run in Host4 if we want to... We should be good as long as we connect right database. Where the RMAN client runs is irrelevant.

Note : As per oracle10gR2, during the cloning, if any tablespaces are OFFLINE in target database, will not be cloned into new database.

Temp tablespace never be backed up through RMAN. Hence, it will not be cloned. At the end, cloning process add temp files for temporary tablespace automatically. This is pretty much same for restoring the database from RMAN backup. RMAN creates the temporary tablespace when we restore the database.

I have done more Restore scenarios in Oracle12c. Please refer this post if you want to..

1.  Duplicate the database using target database backup. Click  here  for details.
2.  Duplicate the database using Active database duplication. Click   here  for details.
3.  Regular RMAN restore approach. Click   here  for details.
4.  Setup the Standby Database. Click  here  for details.

New features in RMAN since Oracle9i

Oracle9i RMAN new features: Oracle9i introduced several new enhancements. Let us discuss some of the new enhancements in oracle9i.

1. Persistent Configuration:
Introduced new CONFIGURE command enable us to configure various backup, restore and recover parameters that are persistent across RMAN sessions.

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\BK\bk%d_%s_%p'
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
CONFIGURE EXCLUDE FOR TABLESPACE users;
CONFIGURE EXCLUDE FOR TABLESPACE users CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION ON;

2. Backup of Archivelogs:

We can use new PLUS ARCHIVELOG clause to specify that archive logs that have not been backed up are included with the data backup.

BACKUP DATAFILE 2 PLUS ARCHIVELOG;

3. Restartable Backups:

Oracle9i has added another new clause to the BACKUP statement, NOT BACKED UP [SINCE TIME….]. This clause specifies that oracle should only back up files that have not been backed up since a particular date and time.

BACKUP DATABASE NOT BACKED UP SINCE TIME ‘sysdate-7’;

We can use this clause to restart the backup process when it fails after completing a part of the backup. This clause only backs up the files that were missed by the first backup that failed. This clause enables you to reduce the time required for the backup. Otherwise, you would have to perform a full backup again. If we omit the SINCE TIME option, files that have never been backed up become candidates for backup.

Oracle9i also optimize the restoration of files from backup, files whose file header match the headers of the files in the backup sets are not restored. This keeps us from restoring files that have already been restored in the case of restore operation that fails midway.

4. Block Media Recovery – BMR:

Data blocks can be corrupted due to various reasons during normal database operations. Oracle writes information about the data block corruption into the alert log file and the user trace files. Prior to oracle9i, we had to restore and recover the entire data file by applying the redo required from the archived log files, even through just one single block was corrupted. It would be time consuming to restore and recover the entire data file just for one corrupted block.

Oracle9i introduced BMR to enable us to restore and recover just those blocks that are corrupted. We can find the corrupted block from V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views. We also do not have to bring the data file offline to recover the corrupted blocks. The DB availability is greatly enhanced with BMR feature.

BLOCKRECOVER DATAFILE d1 BLOCK b1,[b2,b3,b4…]
[DATAFILE d2 BLOCK b5,[b7,b8….]]…

--recover corrupt blocks in three datafiles
RMAN> BLOCKRECOVER DATAFILE 2 BLOCK 12, 13 DATAFILE 3 BLOCK 5, 98, 99 DATAFILE 4 BLOCK 19;

5. Trail Recovery:

Trail recovery tests the backup and identifies any problem in the backup before we really use this backup for any recovery. The difference between regular recovery and trail recovery is that, in trial recovery, the data is not written to the data files and changes are only recorded in the buffers. Trail recovery writes any errors encountered into the alert log file.

Once the trail recovery is completed, all the effects of trail recovery are nullified, and it writes all the errors in alert log. The database should be mounted stage while doing regular recovery as well as trail recovery. So users can not use the database during both trail and regular recovery.

Oracle9i introduced new feature to allow number of block corruptions during the recovery. If the corrupt blocks are few, you can perform a recovery that ignores the corrupt block but recovers the rest.

RECOVER DATABASE ALLOW 2 CORRUPTION;

Miscellaneous Enhancements:

1. Oracle9i has ability to report obsolete backups that are not required to keep the database unrecoverable with in a particular window of time. Here is the example.

REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS

We can also use DELETE OBSOLETE command to delete those obsolete backups. Also we can display files that must be backed up for number of days recovery window.

REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS

2. We can use CROSSCHECK command to check if backup sets or file copies exists.

3. List command has been enhanced. Now we can specify either BY BACKUP or BY FILE.
LIST BACKUP;
LIST BACKUP BY FILE;

4. Introduced SHOW command to display all persistent parameters.
SHOW ALL

Oracle10g RMAN new features: Oracle10g introduced several new enhancements. Let us discuss some of the new enhancements in oracle10g.

1. Incrementally updated backups:

RMAN incremental backup method back up only datafile blocks that have changed since a specified previous backup. You can make incremental backups of databases, individual tablespaces or datafiles. The goal of an incremental backup is to back up only those data blocks that have changed since a previous backup.

Each data block in a datafile contains a system change number (SCN), which is the SCN at which the most recent change was made to the block. During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN of the parent incremental backup. If the SCN in the input data block is greater than the checkpoint SCN of the parent, then RMAN copies the block.

Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. 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.

RMAN> backup incremental level 0 tablespace system;
RMAN> backup incremental level 1 database;

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

1. A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

2. A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

2. Fast Incremental backup

During the incremental backup, oracle scans the whole data file and compare the SCN between the blocks in the data file and backup set files. It will be a time consuming tasks. Oracle introduced change tracking file to track the physical location of all database changes. During an incremental backup, RMAN uses the change tracking file to quickly identify only the blocks that have changed, avoiding the time consuming task of reading the entire data file to determine which blocks have changed. This is called Fast incremental backup. The new background process CTWR(Change tracking writer) is also required when using fast incremental backups.

SQL> alter database enable block change tracking using file
2 'C:/changetracking/chg01.dbf';

Database altered.
SQL>

The dynamic performance view V$BLOCK_CHANGE_TRACKING shows where block change tacking file is stored, whether it is enabled, and how large it is.

Please click the below link to read more about incremental backup in oracle10g. Click here

3. Dropping database through RMAN

We can drop the database through RMAN, it has additional benefits of dropping all backup copies and archived log files for the database if you include the INCLUDING BACKUPS clause.

RMAN> drop database including backups;

4. Automatic Channel Failover

If we are using multiple channels during an RMAN backup to either tape or disk and one of the channels fails, the backup job continues on the remaining channels. Any errors including channel errors are reported in the dynamic performance view V$RMAN_OUTPUT after the backup job is completed.

5. Duration, Throttling and Partial Backup Options

BACKUP......DURATION is a maximum duration or time frame for a backup operation, we can minimize the impact of the backup operation in a 24/7 environment. The new DURATION option, which replace RATE and READRATE in previous version of RMAN. For example, to spread out the backup of the USERS tablespace over a maximum of two hours, use the following command.

RMAN> BACKUP DURATION 2:00 TABLESPACE users;

By default, the backup will complete as fast as possible within the two-hour time frame.

BACKUP.....DURATION.....PARTIAL adding PARTIAL to the BACKUP command prevents an error message from being issued by RMAN in case the backup does not finish the operation in the specified amount of time. Any complete backup sets are available for recovery operations, and any partial or incomplete backup operations will have to be restarted.

RMAN> backup duration 2:00 partial
2> tablespace users
3> filesperset 1;

BACKUP.....DURATION.....MINIMIZE LOAD The MINIMIZE LOAD clause, used with disk backups only, automatically adjusts the throughput of the backup operation to complete the backup in the estimated completion time specified by DUATION.

RMAN> BACKUP DURATION 4:00 PARTIAL
2> MINIMIZE LOAD
3> DATABASE FILESPERSET 1;

BACKUP....DURATION....MINIMIZE TIME The MINIMIZE TIME clause, will complete the backup in the shortest possible time.

RMAN> backup duration 2:00 minimize time
2> tablespace users;

6. Creating Image copies

Oracle10g introduced new feature to create image copy of the data files. It is equivalent to conventional copy of oracle data files. Image copy is a bit-for-bit duplicate of a database file, identical to a copy made with an operating system command. When we take conventional backup, we need to put the tablespace in BEGIN BACKUP mode. But when we create image copy, it is not required. Another thing, when we take conventional backup, it will not be registered in RMAN catalog database. But RMAN image copy backup will be registered in catalog database.

What is the advantage of image copies? To answer this question, it would be useful for migrating the database to ASM. We can also use them to recover the database in non RMAN environment.

We have BACKUP AS COPY command in RMAN, which can copy entire database, multiple tablespaces, datafiles, archived log files.

RMAN> backup as copy database;
RMAN> backup as copy tablespace system, sysaux;
RMAN> backup as copy datafile 1;

RMAN automatically determines which datafiles belong to each tablespace and performs the image copy for each. In addition, the image copies generated by RMAN can be used directly in a recovery operation without using RMAN to extract a data file from previous backup set.

7. Full databaes backup

We may want to back up a previous backup to tape, rather than create a direct backup to tape, so as not to impact a database that is up and running. In RMAN, we can use the following command to backup a previous copy of the entire database to the default tape device.

RMAN> backup copy of database;

8. Compressed backup

This feature allows RMAN to perform binary compression of backupsets. The resulting backupsets do not need to be uncompressed during recovery. It is most useful in the following circumstances.

1. You are performing disk-based backup with limited disk space
2. You are performing backups across a network where network bandwidth is limiting
3. You are performing backups to tape, CD or DVD where hardware compression is not available

RMAN>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
RMAN>BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5;

Compression requires additional CPU cycles which may affect the performance of the database. For this reason it should not be used for tape backups where hardware compression is available.

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we enable archivelog in oracle? Click here

How do we set up RMAN in oracle? Click here

What is difference between traditional backup and RMAN backup? Click here

Different type of recovery scenario in RMAN? Click here

Instance Recovery

Oracle performs instance recovery when the database is restarted due to instance failure or shutdown the database with ABORT option(shutdown abort) or startup the database with FORCE option(startup force). Instance recovery is taken care by SMON oracle background process automatically. Instance recovery consists of two steps. One is Roll forward, next is Roll backward.

Roll forward:

Changes being made to the database are recorded in the database buffer cache and buffer cache will be written into data files. At the same time, the changes are recorded in redo log buffer and redo log buffer will be written to redo log file. Oracle writes the data from database buffer cache to data file only when there is enough data in data buffer cache. It is not necessary that, every commit, oracle writes the data buffer cache into data file. When instance fails before committed data are written into data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started. This process is called Roll forward or cache recovery.

Roll backward:

When we make any changes in the database, the old image will be written into undo segment. Later undo segment will be used to rollback the data when we rollback the transaction. DBWR writes the buffer cache content into data file under different circumstances. It might be possible to write the uncommitted data from database buffer cache into data file. When instance fails and associated database is restarted, it rollback the uncommitted transaction in data file by using undo segment to maintain the read consistency. This is called Roll backward or transaction recovery.

RMAN Recovery Scenarios

Olden days, we use traditional backup(putting tablespace in hot backup mode) to backup the database. When we recover the database, we use traditional backup as a base and apply archive log files to recover the database. We have some issues when we dealt with traditional backup. Then oracle introduced RMAN backup to resolve some of the issues which we faced in traditional backup. Now you may ask, What are the issues in traditional backup and what are the convenience in RMAN backup? I discussed this in another thread.. Please see think link to get the answer.

In this thread, i am going to discuss about possible recovery scenario in RMAN. Before that, we need to setup the RMAN in oracle instance.. How do we set up RMAN in oracle instance? Here is the link to see how to setup RMAN in oracle.

Now we will discuss about different type of DB recovery scenarios.... The database name is ORCL. The catalog database name CATDB.... RMAN has setup and backup is running every day. The database is running in windows environment.

Scenario1

Kevin is DBA, he is working in Mission critical workshop. On Friday night, while taking RMAN backup, the machine is crashed and database is down. Kevin restarted the database and database is mounted successfully. But it is not opened. He realized that, some of the tablespaces are corrupted. Fortunately, redo log files and control files are intact. Kevin wanted to bring the DB as fast as possible.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL>
Here is the solution.... Since most of the tablespace datafiles are corrupted, he decided to do complete database recovery. First he mounted the database. He can not open the database until he completely recover the database.

Kevin connected the RMAN and recoverd the whole database as below.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 21:32:28 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database

RMAN> run{
2> restore database;
3> recover database;
4> alter database open;
5> }

Starting restore at 21-MAY-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P tag=TAG20090521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 21-MAY-09

Starting recover at 21-MAY-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:14

Finished recover at 21-MAY-09

database opened

RMAN>

Scenario2

Adita is DBA in banking firm. On Monday afternoon, due to media failure, one of the data file is corrupted. But all other data files are working fine. Anita restarted the database and one data file is complaining and DB is not restarted. Anita made the corrupted data file offine and opened the database. So users can use the DB while recoving the one data file.

SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select file_id from dba_data_files where file_name
2 ='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF';

FILE_ID
----------
5

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Here is the solution to recover the database..........

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 22:30:06 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run
2> {
3> restore datafile 5;
4> recover datafile 5;
5> }

Starting restore at 21-MAY-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 21-MAY-09

Starting recover at 21-MAY-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 21-MAY-09

RMAN> exit

Recovery Manager complete.

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 21 22:31:10 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
ONLINE;

Database altered.

SQL> select distinct status from dba_data_files;

STATUS
---------
AVAILABLE

SQL>

Scenario3

Scott is a DBA in trading firm. During the hard disk replacement in unix box, one of the application tablespace data files are corrupted. When Scott restart the database, it complained that, data file is corrupted and tablespace is not usable. But fortunately, other tablespaces datafiles are intact. Scott decided to make this tablespace offline and open the database. So that others can use the database while recovering the tablespace.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
OFFLINE;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.\ORADATA\ORCL\USERS02.DBF'
OFFLINE;

Database altered.

SQL> alter database open;

Database altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

Scott made data files offline and opened the database. While others are using the database, he connected in RMAN and recovered only corrupted tablespace

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 09:36:52 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run{
2> restore tablespace users;
3> recover tablespace users;
4> }

Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=128 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0CKFJA S2_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P tag=TAG20090521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 12 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_12_1_687344200.ARC
archive log thread 1 sequence 17 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_17_1_687344200.ARC
archive log thread 1 sequence 18 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_18_1_687344200.ARC
archive log thread 1 sequence 19 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_19_1_687344200.ARC
media recovery complete, elapsed time: 00:01:23
Finished recover at 22-MAY-09

SQL> connect sys/password as sysdba
Connected.
SQL> alter database datafile 'c:\oracle\product\10.2.0\oradata\orcl\users01.dbf'
2 online;

Database altered.

SQL> alter database datafile 'c:\oracle\product\10.2.0\oradata\orcl\users02.dbf'
2 online;

Database altered.

SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL>

Scenario4

Jane is a DBA in telecommunication company. Unix admin had done some maintenance work on the server. Once they completed their work, Jane restarted the database. When Jane restart the DB, Jane realized that, SYSTEM tablespace is corrupted.. But others are intact. He decided to mount the database and recover this tablespace. This leads downtime for the database, since jane can not open the database without recovering the system tablespace.

SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
' OFFLINE;

Database altered.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 10:21:04 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database

RMAN> run
2> {
3> restore tablespace system;
4> recover tablespace system;
5> }

Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE
9Q_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P tag=TAG20090522T094738
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 22-MAY-09
RMAN> exit

Recovery Manager complete.

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 22 10:22:20 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' online;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.
SQL> select distinct status from dba_tablespaces;

STATUS
---------
ONLINE

SQL> select distinct status from dba_data_files;

STATUS
---------
AVAILABLE

SQL>

Scenario5

David is DBA for Energy company. David ended up restarting the database due to hardware maintenance. When David restarts the database, realized that all the redo log files are corrupted... RMAN backup runs every day at 11.30PM. David found this issue on 12.20PM. Now David needs to do incomplete recovery. Here is the steps David taken... Mount the database and run the RMAN backup. Just to make sure all the archived redo log files are backed up before start the recovery. Once RMAN backup is completed, David run the incomplete recovery and recovered until the last archived redo log.

SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
SQL>exit
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 23 20:36:24 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database

RMAN> backup archivelog all;

Starting backup at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=31 stamp=687541158
input archive log thread=1 sequence=3 recid=32 stamp=687542042
input archive log thread=1 sequence=4 recid=33 stamp=687542062
40 comment=NONE
Starting Control File and SPFILE Autobackup at 23-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090523-0A comment=NONE
Finished Control File and SPFILE Autobackup at 23-MAY-09
RMAN> list backup of archivelog from time='sysdate-7';
List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10609 8.10M DISK 00:00:02 23-MAY-09
BP Key: 10611 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2235
40
Piece Name: C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\01KFPFLS_1_1

List of Archived Logs in backup set 10609
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 544522 23-MAY-09 550869 23-MAY-09
1 3 550869 23-MAY-09 550898 23-MAY-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10625 103.00K DISK 00:00:02 23-MAY-09
BP Key: 10630 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2236
29
Piece Name: C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\03KFPFND_1_1

List of Archived Logs in backup set 10625
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 550898 23-MAY-09 550988 23-MAY-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10672 8.29M DISK 00:00:02 23-MAY-09
BP Key: 10676 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2239
52
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_05KFPFTO_5_1

List of Archived Logs in backup set 10672
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 544522 23-MAY-09 550869 23-MAY-09
1 3 550869 23-MAY-09 550898 23-MAY-09
1 4 550898 23-MAY-09 550988 23-MAY-09
1 5 550988 23-MAY-09 551206 23-MAY-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10692 37.00K DISK 00:00:01 23-MAY-09
BP Key: 10697 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2240
42
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_07KFPFVA_7_1

List of Archived Logs in backup set 10692
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 551206 23-MAY-09 551263 23-MAY-09

RMAN> restore database until sequence=6 thread=1 force;

Starting restore at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATA
BASE\02KFPFLV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\02KFPFLV_1_1 tag=TAG20090523
T223542
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-MAY-09

RMAN> recover database until sequence=6 thread=1 ;

Starting recover at 23-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 4 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_687649381.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_687649381.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6876
49381.ARC thread=1 sequence=4
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6876
49381.ARC thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-MAY-09

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
RMAN>

The database is recovered successfully. But this is incomplete recovery. Prior to oracle10g, oracle strongly recommend to take the full database backup whenever there is incomplete recovery. But in oracle10g, it is optional. But still it is good to take the full database backup for safer side. Please read this link to know more about this...

Scenario6

Kathy is DBA for one of the finance company. On Monday, due to hardware issue, the server was crashed. Unfortunately, Kathy lost all data files, control files, redo log files. But luckily the current archived redo log files were intact. Here is the steps Kathy has taken to handle this crash recovery.

Kathy was not able to mount the database since she lost her control file. So she first recovered the control file from RMAN backup. Once control file is recovered, she mounted the database. After the database is mounted, she ran the RMAN backup to make sure, all the current archive log files are backed up. she recovered the data base until last archived log file. Finally she opened the the DB RESETLOGS option.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

C:\>set oracle_sid=orcl

C:\>rman catalog=rman/rman@catdb target=sys/password

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:26:51 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: orcl (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATA
BASE\C-1215492928-20090524-00
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215492928-20090524-00 tag
=TAG20090524T152409
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 24-MAY-09

RMAN>

Once the control file is recovered, the database is mounted.....

SQL> alter database mount;

Database altered.

SQL> select archivelog_change#-1 from v$database;

ARCHIVELOG_CHANGE#-1
--------------------
547010

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:29:33 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database

RMAN> run{
2> set until scn 547010;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause

Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_02KFRA
N7_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFRAN7_1_1_%S_%P tag=TAG20090524T1523
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09

Starting recover at 24-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687712197.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6877
12197.ARC thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAY-09

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Scenario7

Scott is the DBA for database. One of the DB progammer truncated the critical table around 3.45PM and he called Scott and explained the situation. Scott has to recover the truncated table. Here is the steps Scott followed....

SQL> select count(*) from employee;

COUNT(*)
----------
14

SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
2 from dual
3 /

TO_CHAR(SYSDATE,'DD
-------------------
24-05-2009:15:45:42

SQL> truncate table employee;

Table truncated.

SQL> select count(*) from employee;

COUNT(*)
----------
0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL>


C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:58:56 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database

RMAN> run
2> {
3> set until time "to_date('24-05-2009:15:45:42','DD-MM-YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_09KFRB
BR_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P tag=TAG20090524T1534
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09

Starting recover at 24-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687713476.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_6877
13476.ARC thread=1 sequence=2
media recovery complete, elapsed time: 00:00:05
Finished recover at 24-MAY-09

RMAN> exit

Recovery Manager complete.

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 24 16:01:31 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open resetlogs;

Database altered.

SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from employee;

COUNT(*)
----------
14

SQL>

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we enable archivelog in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

Steps to Setup RMAN

This article is tested in oracle10gR2. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have seperate database to store catalog info. Here i am using seperate database to store backup catalog information.

You might ask a question yourself, why would we need RMAN backup? Why do we need to setup RMAN, since my traditional backups are already running fine.... I already discussed this in another thread.. Please see this link to answer your question. Click

I am using windows OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environmnet.

Here i am using ORCL as primary database and CATDB as catalog database.

Step1 Enable the archive log in ORCL database. I already discussed this in another thread. Please refer this link to enable the database to archive log mode.

Step2 Create the tablespace and user in catalog database to hold backup information.

SQL> CONNECT sys/password@catdb AS SYSDBA
Connected.

SQL> CREATE TABLESPACE RMAN
2 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\RMAN01.DBF' SIZE 6208K REUSE
3 AUTOEXTEND ON NEXT 64K MAXSIZE 32767M
4 EXTENT MANAGEMENT LOCAL
5 SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> CREATE USER rman IDENTIFIED BY rman
2 TEMPORARY TABLESPACE temp
3 DEFAULT TABLESPACE rman
4 QUOTA UNLIMITED ON rman;

User created.

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Grant succeeded.

SQL>

Step3 Create the recovery catalog in catalog database.

C:\>rman catalog=rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 09:59:26 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN";

recovery catalog created

RMAN> exit

Recovery Manager complete.

C:\>

Step4 Register the database with Catalog database. Each database should be registered to catalog database to run RMAN backup.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:02:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

C:\>

Step5 Configure the persistent parameters.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 19 18:46:40 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215054467)
connected to recovery catalog database

RMAN> configure retention policy to recovery window of 2 days;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure default device type to disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure channel device type disk format 'C:\rmanbackup\Backup%d_DB_%U_%S
_%P';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\rmanbackup\Backup%d_DB_%U_%S_%P'
;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN>

Step 6 Take database full backup. The full database backup should be taken first time. Afterwards, archivelog backup will be taken.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:16:09 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run{
2> backup database plus archivelog;
3> delete noprompt obsolete;
4> }

starting full resync of recovery catalog
full resync complete

Starting backup at 21-MAY-09
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=687435222
input archive log thread=1 sequence=3 recid=2 stamp=687435237
input archive log thread=1 sequence=4 recid=3 stamp=687435270
input archive log thread=1 sequence=5 recid=4 stamp=687435279
input archive log thread=1 sequence=6 recid=5 stamp=687435420
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_01KFIRKU_1_1_%S_%P tag=TAG20090521T1017
01 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 21-MAY-09

Starting backup at 21-MAY-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB
F
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFIRLF_1_1_%S_%P tag=TAG20090521T1017
19 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 21-MAY-09

Starting backup at 21-MAY-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=6 stamp=687435506
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_03KFIRNJ_1_1_%S_%P tag=TAG20090521T1018
27 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-MAY-09

Starting Control File and SPFILE Autobackup at 21-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090521-00 com
ment=NONE
Finished Control File and SPFILE Autobackup at 21-MAY-09

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
using channel ORA_DISK_1
no obsolete backups found

RMAN> exit

Recovery Manager complete.

C:\>

Now the RMAN setup is completed successfully. Here are the info about RMAN.

Primary DB = ORCL
Catalog DB = CATDB
RMAN Backup location = c:\rmanbackup.

Now the full backup is taken. Every day, the below script should run and backup the new archive log files.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:25:40 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run{
2> delete noprompt obsolete;
3> backup archivelog all;
4> }

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
no obsolete backups found

Starting backup at 21-MAY-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=687435222
input archive log thread=1 sequence=3 recid=2 stamp=687435237
input archive log thread=1 sequence=4 recid=3 stamp=687435270
input archive log thread=1 sequence=5 recid=4 stamp=687435279
input archive log thread=1 sequence=6 recid=5 stamp=687435420
input archive log thread=1 sequence=7 recid=6 stamp=687435506
input archive log thread=1 sequence=8 recid=7 stamp=687435975
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_05KFIS68_1_1_%S_%P tag=TAG20090521T1026
15 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 21-MAY-09

Starting Control File and SPFILE Autobackup at 21-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090521-01 com
ment=NONE
Finished Control File and SPFILE Autobackup at 21-MAY-09

RMAN> exit

Recovery Manager complete.

C:\>

How do we schedule the daily backup in Windows OS?

We need to write two script, one is batch file(named as daily_backup.bat) which should contain the following command.

rman catalog=rman/rman@catdb target=sys/password@orcl cmdfile daily_backup.sql

Another one is text file(named as daily_backup.sql) which should contain the following code.

run
{
backup archivelog all;
delete noprompt obsolete;
}

The batch file(daily_backup.bat) can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks). The above two windows scripts are tested in windows environment and works well.

How do we schedule the daily backup in Unix?

Here we need to write shell script and schedule the shell script in unix scheduler(crontab).

The shell script content should be like this....... Please remember, the below shell script is not tested in unix environment. Please do test yourself in unix environment.... This is sample shell script...

export ORACLE_HOME=/usr/app/oracle/product/10.2.0
export ORACLE_SID=orcl
export ALIAS=orcl
cd =/usr/app/oracle/product/10.2.0/bin
rman catalog=rman/rman@catdb target=sys/password@orcl <<
run
{
backup archivelog all;
delete noprompt obsolete;
}
exit
EOF

The above shell script can be scheduled in crontab. To learn more about crontab, see this link.

If you want to read more on RMAN, Please click this link....

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

What are the new features in RMAN since Oracle9i? Click here

Different type of recovery scenario in RMAN? Click here

Benefits of RMAN Backup vs. Scripted Backups

RMAN was introduced in oracle8i. Before oracle8i, we used traditional backup or scripted backup. Why did oracle move to RMAN backup? Why do we need to use RMAN to backup our database? We may already be doing online backup with some wonderfully crafted, home-grown scripts. We may ask this question ourself. Why should we start using RMAN when my scripted backups are working fine?

We are moving to RMAN backup for two reasons. One is, when backup scripts breaks, the database backup would fail. Another issue is, when script fails, some one has to fix the backup scripts.

There are number of other positive reason to move to RMAN backup. Here are these....

1. RMAN will detect corrupted blocks and report them to us. RMAN also can recover only corrupted blocks. This is called Block Media Recovery(BMR). In traditional recovery, we need to restore and recover the whole data file. But in RMAN, it is not required to restore and recover the entire data file for just few corrupted blocks.

2. RMAN can back up your database online without having to put the tablespace in hot backup mode. Thus, the additional redo generated during a hot backup is reduced.

3. RMAN will automatically track new datafiles and tablespaces for us. In scripted backup, we might need to change the backup script when new tablespace or data file is added in the database.

4. RMAN will only backup up used data blocks(up to the high water mark(HWM)). So RMAN backup images typically are smaller than those of traditional online back images.

5. RMAN provides easy, automated backup, restore and recovery operations. RMAN tracks all the backups needed to recover the database.

6. RMAN can work fairly seamlessly with third-party media management products.

7. RMAN supports incremental backup strategies.

8. With RMAN, we can actually test backups without restoring them. We can validate the backup and make sure backup will work at the time of restore.

9. If we use the repository, then RMAN provides a nice, centralized reporting facility.

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we enable archivelog in oracle? Click here

How do we set up RMAN in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

Different type of recovery scenario in RMAN? Click here

Enable Archive log in Oracle10g

How do we enable the archive log mode in oracle10g?

The below article is tested in oracle10g R2 version. Please remember, below steps directories and folders would change based on the environment and operating systems. Here the database name is ORCL.

Step 1 We need to build the pfile from spfile to add new entries. If you have already recent pfile, the you do not need to do this step.

Login as sys user and execute this to create pfile.

create pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE.ORA' from spfile;

Step 2 Once pfile is created, then edit the pfile and add the below two parameters.

log_archive_format=Log_%s_%t_%r.arc
log_archive_dest='C:\oracle\product\10.2.0\db_1\database\archive'

In case, if you already have the below two entries in the pfile, then we need to remove or comment this below two entires. Since we can not have this below two entry with above new two parameters.

db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
db_recovery_file_dest_size=2147483648

Till oracle9i, we use log_archive_start=true in parameter file. Since from oracle10g, this parameter is deprecated. We should not add this entry in pfile from oracle10g. If we have this entry in oracle10g, we get the below error.

ORA-32004:obsolete and/or deprecated parameter specified.

Step 3 Once pfile is edited, then we need to create the spfile with modified pfile. Login as sys user and execute the below command.

create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\XX.ORA' from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE.ORA'

Step 4 Rename the original SPFILEORCL.ORA to different name. Then rename the XX.ORA to SPFILEORCL.ORA.

Step 5 Login as sys user and shutdown the database and follow the steps.

Mount the database
alter database archivelog
alter database open;

Here is the screen output....

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\product\10.2.0\db_1\database\archive
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we set up RMAN in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

What is difference between traditional backup and RMAN backup? Click here

Different type of recovery scenario in RMAN? Click here

Dropping database in oracle10g

There are few ways, we can drop the database in oracle. This article is tested in oracle10gR2. Let us discuss these options here.

Option 1. We can use DBCA utility to drop the database. This approach will be a clean approach for dropping database. Since DBCA tool drop all the data files, control files, redolog files, spfile, password file and background dump trace files. This approach cleans up the tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. It also deletes the entire folders relevant to that database. But this approach will not delete RMAN backup files, archived redo log files. If the database is running in RMAN and it is registered with catalog database, then DBCA utility will not unregister from catalog database automatically. So before we drop the database by using DBCA utility, we need to unregister the database by using RMAN UNREGISTER DATABASE command. If the database is using only control file for RMAN metadata, then this command is not necessary.

Option 2 In oracle10g, we have a command to drop the database. All the data files, control files, redo log files will be deleted automatically when we use drop database command in oracle10g. But it is not deleting the folders. It is not deleting dump files, alert log files, archived redo log files, RMAN backup files and password files relevant to the database. Also it is not cleaning the tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. We need to clean these files and folders manually after we drop the database by using drop database command. If the database is running in RMAN with catalog database, we need to unregister the database manually from catalog before we drop the database. This option would not remove backup info in catalog DB.

Here are the steps to drop the database in oracle10g.

1. Connect as sys user
2. shutdown the database
3. Mount the database with restrict mode
4. Drop the database
5. Clean up the files manually(trace files, password file, folders, cleaning the tnsentry, archive redo log files, RMAN backup files)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

Option 3 When the database is running in RMAN, the database must be registered with catalog database. The above two options will not unregister the database from catalog DB. It will not remove the backup information from catalog database. If we use the above options, we need to unregister the database from catalog manually before we drop the database. Oracle10g introduced new feature that we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG. Dropping database from RMAN has a additional benefit of dropping all backup copies and archived log files for the database. For further information pls also see Metalink Note: 251412.1

But still it is not deleting the dump files, alert log files, password file. It is not removing tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. But it cleans data file, control file, redo log file, archive log file, RMAN backup files and removes backup info in CATALOG Database.

C:\>rman target / catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 20 15:11:25 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes

RMAN> sql 'alter system enable restricted session';

sql statement: alter system enable restricted session

RMAN> drop database including backups;

database name is "ORCL" and DBID is 1215054467

Do you really want to drop all backups and the database (enter YES or NO)? YES

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
36 34 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_01KF
EGUK_1_1_%S_%P
37 35 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_02KF
EGUN_1_1_%S_%P
54 49 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_03KF
EGVS_1_1_%S_%P
64 62 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-00
133 130 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-01
261 255 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-02
328 324 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_07KF
EQ6L_1_1_%S_%P
329 325 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_08KF
EQ7P_1_1_%S_%P
356 350 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-03
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_01KFEGUK_1_1_%S_%P recid=1 stamp
=687293397
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFEGUN_1_1_%S_%P recid=2 stamp
=687293400
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_03KFEGVS_1_1_%S_%P recid=3 stamp
=687293438
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-00 recid=4 stamp=687293441
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-01 recid=5 stamp=687297981
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-02 recid=6 stamp=687301176
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_07KFEQ6L_1_1_%S_%P recid=7 stamp
=687302870
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_08KFEQ7P_1_1_%S_%P recid=8 stamp
=687302906
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-03 recid=9 stamp=687302924
Deleted 9 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
26 1 3 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_3_1_687273734.ARC
33 1 4 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_4_1_687273734.ARC
103 1 5 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_5_1_687273734.ARC
104 1 6 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_6_1_687273734.ARC
128 1 7 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_7_1_687273734.ARC
129 1 8 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_8_1_687273734.ARC
164 1 9 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_9_1_687273734.ARC
165 1 10 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_10_1_687273734.ARC
166 1 11 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_11_1_687273734.ARC
167 1 12 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_12_1_687273734.ARC
249 1 13 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_13_1_687273734.ARC
287 1 1 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_1_1_687301150.ARC
288 1 2 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_2_1_687301150.ARC
289 1 3 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_3_1_687301150.ARC
290 1 4 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_4_1_687301150.ARC
319 1 5 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_5_1_687301150.ARC
384 1 6 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_6_1_687301150.ARC
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6872
73734.ARC recid=1 stamp=687293394
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6872
73734.ARC recid=2 stamp=687293436
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6872
73734.ARC recid=3 stamp=687297267
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_6_1_6872
73734.ARC recid=4 stamp=687297285
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_7_1_6872
73734.ARC recid=5 stamp=687298507
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_8_1_6872
73734.ARC recid=6 stamp=687298515
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_9_1_6872
73734.ARC recid=7 stamp=687300567
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_10_1_687
273734.ARC recid=8 stamp=687300576
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_11_1_687
273734.ARC recid=9 stamp=687300608
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_12_1_687
273734.ARC recid=10 stamp=687300618
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_13_1_687
273734.ARC recid=11 stamp=687301152
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_1_1_6873
01150.ARC recid=12 stamp=687301663
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_6873
01150.ARC recid=13 stamp=687301679
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6873
01150.ARC recid=14 stamp=687301699
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6873
01150.ARC recid=15 stamp=687301706
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6873
01150.ARC recid=16 stamp=687302866
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_6_1_6873
01150.ARC recid=17 stamp=687340879
Deleted 17 objects


database name is "ORCL" and DBID is 1215054467
database dropped

database name is "ORCL" and DBID is 1215054467
database unregistered from the recovery catalog

RMAN>

Conclusion: What is best choice to delete the database? Based on my experience, best choice would be, use DBCA to remove the Database. But before remove, unregister the database in CATALOG database if database is using RMAN with catalog.

In case, if you don't have DBCA, then we can go for removing DB in RMAN as below.

RMAN> drop database including backups;

After successfully dropped the database, we might need to remove dump files, password file manually.

Another option would be, using DROP DATABASE command. Again, you need to manually remove the files, backup info in CATALOG database.

Global Temporary tables

During the application development, we use some staging table or temporary tables to store intermediate result which are complicated to complete in one pass. Normally we use regular table or PLSQL table(array) to capture the intermediate result. But oracle8i introduced global temporary table. We can use global temporary table instead of regular table.

GLOBAL TEMPORARY TABLE indicates that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table

Please remember, the below codes are tested in oracle10g R2.

What are the advantages of Global temporary table compared to regular table?

Let us call Global temporary table as GTT....

1. GTT does not generate REDO info by itself. But regular table does generate redo infomation. So it will be faster when we use GTT.

2. When we truncate the GTT, it truncates only session specific data. So others can still see their data. But regular table truncate will truncate the whole table.

3. Data in GTT is deleted when the session ends or commit/rollback the transaction. So GTT data is session specific. But regular table is not session specific. It is visible of all the session once the data are committed.

4. Index can be created on GTT. But scope of the index data is at the session level.

5. Views can be created against GTT and combination GTT and regular table

6. Like regular table, database triggers can be created on GTT

Restriction on Global temporary tables :

1. GTT can not be partitioned.

2. We can not create any Foreign key constraint on GTT

3. Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored

4. GTT cannot contain columns of nested table.

5. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Creation of Global temporary tables

The session-specific rows in a global temporary table can be preserved for the whole session. The data will be deleted when we exit the session. The ON COMMIT PRESERVE ROWS clause indicates that the data should be deleted at the end of the session.

scott@orcl> CREATE GLOBAL TEMPORARY TABLE gtt(
2 column1 NUMBER,
3 column2 NUMBER
4 ) ON COMMIT PRESERVE ROWS;

Table created.

scott@orcl> insert into gtt values(1,1);

1 row created.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from gtt;

COLUMN1 COLUMN2
---------- ----------
1 1

scott@orcl> connect scott/tiger@orcl
Connected.
scott@orcl> select * from gtt;

no rows selected

scott@orcl>

The transaction-specific rows in a global temporary table can be preserved until we commit or rollback the transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted when we commit or rollback the transaction.

scott@orcl> CREATE GLOBAL TEMPORARY TABLE gtt(
2 column1 NUMBER,
3 column2 NUMBER
4 ) ON COMMIT DELETE ROWS;

Table created.

scott@orcl> INSERT INTO gtt values(1,1);

1 row created.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from gtt;

no rows selected

scott@orcl>