Rman Incremental Back up And Restore

Incremental backup and restore in oracle 10g

Here I am presenting the simple steps to demonstrate how to take incremental level backup,perform restore and recovery using RMAN.One can learn Rman incremental level backup easily by going through this post. I used oracle 10g express edition.

Difference between differential and cumulative incremental backup will be clear by going through this simple demo.

Simple scenarios has been taken.

Demonstrating DIFFERENTIAL INCREMENTAL BACKUP

1. Database is in NOARCHIVELOG mode.
2. Not using recovery catalog.
3. RMAN configuration setting that I used is following. I am posting the output of
RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default

4. Since database is in NOARCHIVELOG mode, it is necessary to shut down the database cleanly using immediate,normal or transactional options.

RMAN> shutdown

5. To take incremental 0 level backup,mount the database. This step is required even in non-incremental backups.

RMAN> startup mount

6. Issue
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
This takes the full backup of the database and also includes controlfile as well as spfile.

7. Issue
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11


SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1084366


8. RMAN> ALTER DATABASE OPEN;

9. Now, perform some DML operations. For example,

SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL> /
SQL> /
SQL> COMMIT;

10. Again, shutdown the database to take incremental level backup.
RMAN>SHUTDOWN
RMAN>STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
It backups only the changes made since the last incremental level n or lower backup.

11. Open the database again to perform some insertions.
RMAN> ALTER DATABASE OPEN;

12. SQL> INSERT INTO T1 VALUES(1);
SQL> /
SQL> /
SQL> /
SQL> COMMIT;

13. Shutdown the database again to take incremental level 2 backup.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE TAG 'INC_1';

14. Open database again to made some insertions.
RMAN> ALTER DATABASE OPEN;
SQL> INSERT INTO T1 VALUES(9);
SQL> /
SQL> /
SQL> COMMI;

15. select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11


SQL> select checkpoint_change# from v$database;


CHECKPOINT_CHANGE#
------------------
1084663

Here all the changes since the last backup has been stored in the redo logs.

16. Now, delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT;

18. RMAN> STARTUP
Database will not open. Database will only go upto the NOMOUNT state since controlfile has been lost.

19. RMAN> Now following steps are needed to recover the database.

20. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
If controlfile autobackup is located in default location, then we can skip this step.

21. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP

22. RMAN> ALTER DATABASE MOUNT;

23. RMAN> RESTORE DATABASE;
It will restore the database using the 0 level backup.

24. RMAN> RECOVER DATABASE
Watch the output carefully. You can recognise various backups that are being applied. Look for the tags that you have given to backupsets. It will applies all the incrementals one by one. First it will apply level 1 incremental, and then level 2. Then it will search for appropriate log sequence and applies the same if found. If log switching has not been taken place after the last incremental backup, then we get all the data without any data loss. And database will restore upto the current point in time. In our case no log switching has taken place and all the data since the last backup exist in the redologs. NOREDO option is needed when log switching has taken place. NOREDO option is not needed if log switch has not taken place.

25.RMAN> ALTER DATABASE OPEN RESETLOGS;

26. Now, view the table t1 and you will find the table restored upto the latest point in time if all the redos has been applied.

27. Always take full backup of the database after opening the database in resetlogs mode.

Demonstrating CUMULATIVE INCREMENTAL BACKUP

Here we will utilize same scenario as above,i.e. no recovery catalog mode,no archivelog mode.
We will start here by taking incremental level 0 backup.

1. RMAN> SHUTDOWN
2. RMAN> STARTUP MOUNT
3. RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
4. RMAN> ALTER DATABASE OPEN;

5. SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
6. Make some insertion.
SQL> INSERT INTO T1 VALUES(2);
SQL> /
SQL> /
SQL> COMMIT;

7. Again shutdown the database to take incremental level 1 database which will copies only the changed blocks since the last incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';

8.ALTER DATABASE OPEN;

9. SQL> INSERT INTO T1 VALUES(3);
SQL> /
SQL> /
SQL> COMMIT;

10. Again shutdown the database to take cumulative incremental level 1 backup, this time. This backups all the changes made after the last n-1 or lower backup,here it will backup all the changes since the incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'CUM_1';

11. RMAN> ALTER DATABASE OPEN;

12. Perform some DML again.
SQL> INSERT INTO T1 VALUES(9);
SQL>/
SQL> /
SQL> COMMIT;

13. Now, we will shutdown the database to take incremental level 1 backup this time.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1_1';

14. RMAN> ALTER DATABASE OPEN;

15. SQL> INSERT INTO T1 VALUES(0);
SQL> /
SQL> COMMIT;

16. Delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT

18. RMAN> STARTUP
Database will not open.It will go upto only NOMOUNT state.

19. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/XE/backup/cf_%F';

20. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

21. RMAN> ALTER DATABASE MOUNT;

22. RMAN> RESTORE DATABASE;,
It will restore the database from the last incremental level 0 database.

23. RMAN> RECOVER DATABASE;
Here NOREDO option is not necessary as it is assumed that redo logs has not been switched and complete recovery is possible. Check the same by looking at the contents of v$log and comparing with the checkpoint_change# that have noted in step 5. If all the changes since that value exist in the redo logs then NOREDO option is not needed. Else it is required.

Here first of all, cumulative incremental level 1 backup would restore as there is no need for the backup having tag 'INC_1' as cumulative incremental level 1 backup take backup of all the changes since the last level 0 backup. Thus our first incremental level 1 backup is not applied. Then it applies backup having tag 'INC_1_1' which we take after cumulative incremental level backup. After that it will apply all the changes recorded in the online redologs.
Thus , only two incremental level backups needed to apply here. But in the first scenario all the incremental backups had been applied. Thus we can say that using cumulative incremental backup in your incremental strategy provides faster recovery as number of incremental backups to be applied is less.

24. RMAN> ALTER DATBASE OPEN RESETLOGS;

25. Take whole database backup after opening database in resetlogs mode. It is a good practice to perform th same.

26. Now check your table. It must have all the changes that we made, keeping all the scenario same.

Oracle DBA Day Today Activities

How to Increase the size of a tablespace

Answer: There are 2 ways to increase the size of tablespace.

1. Add an Extra Data file to the tablespace

alter tablespace users add datafile '/u01/oradata/orcl/users02.dbf' size 25m;

2. Resize the Datafile that is currently supporting the tablespace. Some versions of Oracle does not like this, and brings the instance down, so do not use this unless you are really sure.

alter database datafile '/u01/oradata/orcl/users01.dbf' resize 50M;

Hot Backup - User Managed

SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG

If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow other post on my blog.

2)Determine the files that you need to take backup.

Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.

In order to decide which files you need to backup issue the following query.

SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';

File Need Backup
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oradata2/data1/dbase/redo03.log
/oradata2/data1/dbase/redo02.log
/oradata2/data1/dbase/redo01.log
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
13 rows selected.

So after running the above query I can say I need to backup 13 files.

3)Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.

You can check the status, tablespace_name and it's associated data file name with the following query,

SELECT t.STATUS,t.TABLESPACE_NAME "Tablespace", f.FILE_NAME "Datafile"
FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;

Take the tablespace in backup mode rather than offline and read-only tablespace.

You can easily make a script of taking the online tablespace in backup mode by following query.

SQL>SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME ||' BEGIN BACKUP;' "Script" FROM DBA_TABLESPACES WHERE STATUS NOT IN ('READ ONLY','OFFLINE');

Script
-------------------------------------------------------------
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE TEMP BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;
ALTER TABLESPACE TEMP_T BEGIN BACKUP;

6 rows selected.

Alternatively, you can issue

SQL>ALTER DATABASE BEGIN BACKUP;

4)Copy the datafile to backup location.

After making a tablespace in backup mode take backup/copy of the associated datafiles. Here you can also make a script in order to copy datafiles to another location.

For online tablespace you must at first take it backup mode. You can check whether backup mode now active or not by issuing following query,

SQL>SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

SQL> SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES;
Enter value for backup_location: /backup
old 1: SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES
new 1: SELECT 'host scp '|| FILE_NAME || ' /backup ' "Backup Command" FROM DBA_DATA_FILES

Backup Command
------------------------------------------------------------------------------------------
host scp /oradata2/data1/dbase/system01.dbf /backup
host scp /oradata2/data1/dbase/undotbs01.dbf /backup
host scp /oradata2/data1/dbase/sysaux01.dbf /backup
host scp /oradata2/data1/dbase/users01.dbf /backup
host scp /oradata2/data.dbf /backup
host scp /oradata2/data1/data02.dbf /backup
host scp /oradata2/6.dbf /backup
host scp /oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf /backup
host scp /oradata2/data_test.dbf /backup

9 rows selected.

Also you can backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.

In order to make script for to copy data files for those tablespace which are only in backup mode then issue,

SQL>SELECT 'host scp '|| d.name ||' &backup_location' FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

Run the script that you genereted.

On windows or other operating system you can use graphical browser to copy or other associated copy command.

5)Whenever you copy is finished make the tablespace out of backup mode. You can issue BEGIN BACKUP .. SCP serially (Take one tablespace in begin backup mode and then copy the associated datafiles and make the tablespace out of backup mode) or you can do it parallely(Take all tablespaces in begin backup mode and then copy the associated datafiles of all tabelspaces and then make the tablespace out of backup mode).

You here also make a script like,

SQL>SELECT 'ALTER TABLESPACE ' ||t.name ||' END BACKUP;' "End Backup Script"
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
End Backup Script
----------------------------------------------------------
ALTER TABLESPACE SYSTEM END BACKUP;

You if you have taken Database in backup mode then issue

SQL>ALTER DATABASE END BACKUP;

set to database for Rman Back up

This example changes the default backup destination from disk to tape and then again backup to default.

RMAN> show default device type;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
RMAN configuration parameters are successfully reset to default value

RMAN> show default device type;
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

By default, the format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ,

where:IIIIIIIIII stands for the DBID.

YYYYMMDD is a time stamp of the day the backup is generated

QQ is the hex sequence that starts with 00 and has a maximum of

what happens in during hot back up?

I am just trying to explain my little knowledge on hot backup process…
During hot backup, a script or begin backup command puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode.
Many people are having a misconception regarding hot backup that, during hot backup DBWR process will stop writing into datafiles. Also, they say that while the datafiles are not writable, changes are stored somewhere in the SGA, the redologs, the rollback segments etc places and will be written back to datafiles when the tablespace is taken out of backup mode.
In fact, Oracle’s tablespace hot backup does not work this way at all. It absolutely does not stop writing the datafiles, and actually allows continued operation of the database almost exactly as during normal operation.
1. The tablespace is checkpointed 2. The checkpoint SCN in the datafile header freeze to increment with checkpoints 3. Full images of changed DB blocks are written to the redologs
The above three actions are all that is required to guarantee consistency once the file is restored and recovery is applied. By freezing the checkpoint SCN in the file headers, any subsequent recovery on that backup copy of the file will know that it must commence at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the redolog file containing that SCN, and apply recovery starting there. Note that checkpoints to datafiles in hot backup mode are not stopped during the backup, only the incrementing of the main checkpoint SCN flag will stop increasing.
By initially checkpointing the datafiles that comprise the tablespace and logging full block images to redo, Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be available in the redologs in case they are ever used for a recovery.
Also we will observe that huge redo will be generated during hot backup. This is the result of the logging of full images of changed blocks in these tablespaces to the redologs. Normally, Oracle logs an entry in the redologs for every change in the database, but it does not log the whole image of the database block. By logging full images of changed DB blocks to the redologs during backup mode, Oracle eliminates the possibility of the backup containing fractured blocks. To understand this reasoning, you must first understand what a fractured block is….
Typically, Oracle database blocks are a multiple of O/S blocks. For instance, most Unix filesystems have a default block size of 4k, while Oracle’s default block size is 8k. This means that the filesystem stores data in 4k chunks, while Oracle performs reads and writes in 8k chunks, or multiples. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd etc. As it is making this copy, it is reading in O/S-block sized increments. If the database writer happens to be writing a DB block into the datafile at the same time when backup is reading that block, your backup copy of the DB block could contain some O/S blocks before and after writing by DBWR. This kind of blocks which are having mismatched halves are called fractured blocks.
By logging the full block image of the changed block to the redologs, it guarantees that in the event of a recovery, any fractured that might be in the backup copy of the datafile will be resolved by replacing them with the full image of the block from the redologs.

Back Up History

In general, backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the data should that loss occur. The reconstructing of data is achieved through media recovery, which refers to the various operations involved in restoring, rolling forward, and rolling back a backup of database files.

Physical Backup
Hot backup
1. DB is in open stage
2. Check whether Archive is enabling or not. If not it must be enable.
3. Datafile and archive are to be backed up
4. Media recovery required.
5. Backup everything except binary files.Method Method
1. Get the listing of tablespace and Datafile.
a.Select tablespace_name, file_name from dba_data_files;
2. Put the tablespace in backup mode.
a. Alter tablespace tbsname begin backup;
3. Copy each Datafile in os level using CP command and move file to backup location
4. End the tablespace in backup mode
a.Alter tablespace tbsname end backup;
5. Repeat steps from 2 to 4 for each tablespace.
6. Backup the controlfile trace to bdump.
a. Alter database backup controlfile to trace;
7. Confirm all tablespace returned to normal mode.
a. Select file#, status, change#, time from v$backup where status != ‘NOT ACTIVE’;
8. Perform an archive log switch.
a. Alter system archive log current;
9. Backup all archive log files
ProcessWhen executing BEGIN BACKUP the Datafile header corresponding to the tablespace get freeze until executing END BACKUP. In between the END BACKUP time Datafile has to be copied from one location to another location (BL) using OS command CP. While moving the Datafile from one location to backup location data block will be split due to the OS block size. Still the transaction has been gone thought but the SCN number will not update in the Datafile header until trigger end backup.
Drawback1. Block corruption cannot be finding in HB.
2. Split block will occur.
3. Database inconsistence
4. Partial SCN will update

Cold Backup
1. Database is in closed stage
2. Not available to the users
3. Get the down time from client
4. Schedule the time and plan it
5. Copy each datafiles and control file.
Method
1. Shutdown immediate
2. Copy each Datafiles and control file in os level----------------------------------------------------------------------------------------------------------------
Logical BackupExport and ImportExport and import is an Oracle utility in operating system level. It will be installed automatically while installing the oracle software itself. It is a tool allows you to extract data from the database and stored in a file called dump file in binary format. We can easily carry the binary file from one operating system to another operating system and also we move the data from one oracle database to another oracle database. So this tool is absolutely platform independent. The sizes of the dump (binary) file are compress into 1/3 of our Datafile size.

Export modes1. Full database
2. Tablespace level
3. Schema level (user)
4. Table level
5. Record level (using query clause)Role
1. Exp_full_database
2. Imp_full_database

Export clauseFull=no
File=’path’
Filesize=0
Help=none
Log=’path’
Feedback=0
Owner=user
userid=un/pws
Buffer=0 - Os dependent. As a result, the max no. of rows in an array fetch by export.
Buffer_size = rows_in_array * maximum_row_size
xRecordlength=0
Direct=n
Indexes=y
Grants=y
Consistent=n
Compress=y
Constraints=y
Statistics=none/compute/estimate (d)
Rows=y
Tables=schema.tblname
Tablespace=tblname
Transport_tablespace=y
Triggers=y
Query= where condition
Parfile=path

Database Cloning
Hot Clone1. Create required Directories in Clone db location.
2. Put the tablespace in backup mode.
a. Alter tablespace begin backup;
3. Copy the snapshot of each data files in this tablespaces.
4. End the tablespace in backup mode.
a. Alter tablespace end backup;
5. Repeat steps 2, 3, and 4 for each Tablespaces.
6. Backup parameter file.
a. Create pfile from spfile to clone db location;
b. edit the path and db name
7. Trace the control file.
a. Alter database backup control file to trace;
b. edit this file: removing the head, strike out "reuse", change "noresetlogs" to "resetlogs”
8. Copy each redo log file to clone db location.
9. Backup all archive files.
10. create new service id and password file
a. oradim –new –sid xx
b. orapwd file=xx.ora password=xx entries=5
11. Set the environment
a. Set oracle_sid=xx
b. Sqlplus “/as sysdba”
12. Startup the clone db with new pfile and put it in mount state
a. Startup pfile=/initxx.ora nomount
13. Run the control file script to re-create control file and other Datafile.
a. @create.sql
14. Recover the database using controlfile if required.
a. Recover database using backup controlfile until cancel
b. Apply the archive files
15. Open the database with resetlogs mode.
a. Alter database open resetlog

Cold Clone1. Trace the control file to udump.
Alter database backup control file to trace;
2. Create pfile from spfile for clone db.
3. Shutdown the database
4. Create required Directories in Clone destination
5. Copy and paste all the Datafile from target database.
6. Edit the parameter file and replace db name and path.
7. Edit the control file also
8. Startup the clone db with new pfile and place it in mount state
9. Run the control file script to re-create control file and other datafiles.
10. Alter database open resetlogs

Why should we perform consistent export?

As per my knowledge....

it makes all of the queries run by exp be "as of the same point in time-- consistent with
regards to eachother"

imagine if you started an export at 9am of the EMP and DEPT tables.

EMP started exporting at 9am and DEPT at 9:15am.

Now, the EMP data would be "as of 9am", but the DEPT data would be as of 9:15am.  What if
you import that data now -- will it work?  maybe, maybe not -- perhaps at 9:10am, someone
fired the last employee in department 50 and deleted department 50.  Your EMP export
would have them in this deptno, your DEPT export would not have this deptno.  The import
would fail.

If you use consistent=y, Oracle will export all of the data "as of 9am", so deptno=50
will be included in all tables and the import will succeed.


Thank's & Regards
balasubramanianWhy should we perform consistent export