How to clone an Oracle database from a cold backup

Purpose: clone a database using a cold copy of the source database files

Environment:

• Solaris 10
• Oracle 10.2.0.3se

1. Make a cold backup for the source database

1.1 Identify the database files

The following query will display all datafiles, tempfiles and redo logs in the source database:

select name, bytes from (select name, bytes from v$datafile union all select name, bytes from v$tempfile union all select lf.member "name", l.bytes from v$logfile lf, v$log l where lf.group# = l.group#) order by name;

1.2 Copy the cold backup files of the source database

Create all necessary directories in the clone database’s file-system and make sure the space is large enough.

Stop the source database by sqlplus / as sysdba: shutdown immediate

Copy the cold backup files to the target database server using Copy/scp/ftp. If you copy over the controlfiles, remove them or rename them.

2. Create a pfile for the clone database

If spfile is used in the source database, then follow the step. Otherwise just copy the existing pfile.

Start the source database up again: startup

From sqlplus: create pfile='…/…/initMYDB.ora' from spfile;

The new pfile need to be edited: directories (audit_file_dest, background_dump_dest, control_files, …), values of memory parameters, …

Pfile example:

MYDB.__db_cache_size=100663296

MYDB.__java_pool_size=16777216

MYDB.__large_pool_size=4194304

MYDB.__shared_pool_size=184549376

MYDB.__streams_pool_size=0

*._kgl_large_heap_warning_threshold=16388608

*.aq_tm_processes=1

*.audit_file_dest='/oracle/admin/MYDB/adump'

*.audit_trail='TRUE'

*.background_dump_dest='/oracle/admin/MYDB/bdump'

*.backup_tape_io_slaves=TRUE

*.compatible='10.2.0.3'

*.control_files='/u01/MYDB/control01.ctl','/u01/MYDB/control02.ctl'

*.core_dump_dest='/oracle/admin/MYDB/cdump'

*.db_block_size=16384

*.db_domain='mydomain.com'

*.db_file_multiblock_read_count=16

*.db_name='MYDB'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBXDB)'

*.fast_start_mttr_target=300

*.job_queue_processes=10

*.log_archive_dest_1='location=/oraback/archive/MYDB'

*.log_archive_format='MYDB_%s_%t_%r.dbf'

*.log_buffer=1048576

*.open_cursors=100

*.optimizer_features_enable='10.1.0'

*.optimizer_mode='CHOOSE'

*.os_authent_prefix=''

*.pga_aggregate_target=50M

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_limit=TRUE

*.session_max_open_files=20

*.sga_max_size=400M

*.sga_target=300M

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDO'

*.user_dump_dest='/oracle/admin/MYDB/udump'

3. Create the controlfiles for the clone database

To create controlfiles for the clone database, connect to the source database and request a dump of the current control file.

From sqlplus: alter database backup controlfile to trace as '…/…/create_mydb.sql'

Edit the file as follows:

· Remove all lines from the top of the file up to but not including the second 'STARTUP NOMOUNT' line.

· Remove any lines that start with --

· Remove any lines that start with a #

· Remove any blank lines in the 'CREATE CONTROLFILE' section.

· Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

· Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the clone database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

· If the file paths are being changed, alter the file to reflect the changes.

The following is an example:

STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "MYDB" RESETLOGS NOARCHIVELOG MAXLOGFILES 5 MAXLOGMEMBERS 4 MAXDATAFILES 200 MAXINSTANCES 2 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/u01/MYDB/redo_g1m1.log', '/u01/MYDB/redo_g1m2.log' ) SIZE 100M, GROUP 2 ( '/u01/MYDB/redo_g2m1.log', '/u01/MYDB/redo_g2m2.log' ) SIZE 100M, GROUP 3 ( '/u01/MYDB/redo_g3m1.log', '/u01/MYDB/redo_g3m2.log' ) SIZE 100M DATAFILE '/u01/MYDB/system01.dbf', '/u01/MYDB/undo01.dbf', '/u01/MYDB/sysaux01.dbf', '/u01/MYDB/users01.dbf', '/u01/MYDB/sde01.dbf', CHARACTER SET AL32UTF8 ; ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/MYDB/temp01.dbf' SIZE 2000M REUSE AUTOEXTEND OFF;

4. Add a new entry to oratab for the clone database

Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the clone database.

5. Create the a password file for the clone database

Use the following command to create a password file:

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=

6. Create the new controlfiles

Create the new controlfiles and open the database.

In sqlplus: run …/…/create_mydb.sql

Fix any errors during the process.

First try: SQL> @create_mydb.sql ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/oracle/product/10.2.0.3se/dbs/initmydb.ora' CREATE CONTROLFILE SET DATABASE "MYDB" RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-01034: ORACLE not available ALTER DATABASE OPEN RESETLOGS * ERROR at line 1: ORA-01034: ORACLE not available ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/ MYDB/temp01.dbf' * ERROR at line 1: ORA-01034: ORACLE not available	Solution: Create a symbolic link to the pfile ln -s /oracle/admin/mydb/pfile/initmydb.ora initmydb.ora
Second try: SQL> @create_mydb.sql ORACLE instance started. Total System Global Area 419430400 bytes Fixed Size 2030616 bytes Variable Size 318768104 bytes Database Buffers 96468992 bytes Redo Buffers 2162688 bytes CREATE CONTROLFILE SET DATABASE "MYDB" RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-00200: control file could not be created ORA-00202: control file: '/u01/ MYDB/control02.ctl' ORA-27038: created file already exists Additional information: 1 ALTER DATABASE OPEN RESETLOGS * ERROR at line 1: ORA-01507: database not mounted ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/MYDB/temp01.dbf' * ERROR at line 1: ORA-01109: database not open	Solution: The controlfile of the source database was copied over. Rename or remove it.
Third try: SQL> @create_mydb.sql ORACLE instance started. Total System Global Area 419430400 bytes Fixed Size 2030616 bytes Variable Size 318768104 bytes Database Buffers 96468992 bytes Redo Buffers 2162688 bytes Control file created. Database altered. Tablespace altered.	Yahoo! It worked!

7. Check the clone database is good

The clone database should be open now. Perform the following checks:

Check that the database is open	SQL> select status from v$instance; STATUS ------------ OPEN
Make sure that the datafiles are all ok	SQL> select distinct status from v$datafile; STATUS ------- ONLINE SYSTEM
Take a quick look at the alert log.	No errors.

8. Create spfile for the clone database

In sqlplus: create spfile from pfile='……';

9. Change DBID of the clone database

The clone database shares the same DBID with the source database. If RMAN will be used to backup the database, the database ID must be changed. See below for detail.

In sqlplus:

SQL> select dbid, name from v$database; DBID NAME ---------- --------- 2271328529 MYDB

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

SQL> startup mount ORACLE instance started. Total System Global Area 419430400 bytes Fixed Size 2030616 bytes Variable Size 318768104 bytes Database Buffers 96468992 bytes Redo Buffers 2162688 bytes Database mounted.

SQL> exit Disconnected from Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

Use nid to change DBID:

% echo $ORACLE_SID MYDB % nid target=/ DBNEWID: Release 10.2.0.3.0 - Production on Thu Sep 11 14:31:09 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to database MYDB (DBID=2271328529) Connected to server version 10.2.0 Control Files in database: /u01/MYDB/control01.ctl /u01/MYDB/control02.ctl Change database ID of database MYDB? (Y/[N]) => y Proceeding with operation Changing database ID from 2271328529 to 6868511435 Control File /u01/MYDB/control01.ctl - modified Control File /u01/MYDB/control02.ctl - modified Datafile /u01/MYDB/system01.dbf - dbid changed Datafile /u01/MYDB/undo01.dbf - dbid changed Datafile /u01/MYDB/sysaux01.dbf - dbid changed Datafile /u01/MYDB/users01.dbf - dbid changed Datafile /u01/MYDB/sde01.dbf - dbid changed Datafile /u01/MYDB/temp01.dbf - dbid changed Control File /u01/MYDB/control01.ctl - dbid changed Control File /u01/MYDB/control02.ctl - dbid changed Instance shut down Database ID for database MYDB changed to 6868511435. All previous backups and archived redo logs for this database are unusable. Database has been shutdown, open database with RESETLOGS option. Succesfully changed database ID. DBNEWID - Completed succesfully.

        Startup the clone database in sqlplus:

SQL> conn / as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 419430400 bytes Fixed Size 2030616 bytes Variable Size 318768104 bytes Database Buffers 96468992 bytes Redo Buffers 2162688 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs; Database altered. SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ MYDB OPEN

SQL> select dbid, name from v$database; DBID NAME ---------- --------- 6868511435 MYDB