Thursday, September 11, 2008

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
 

No comments:

Post a Comment