Create Read-Write copy from Standby database.

Step by step procedure to Create a Read-Write copy from the Standby database on the same box.

Steps are devided into three parts

Part 1: Create new database

Part 2 : Rename the database

Part 3 : Start the standby database

Standby database name : stbydb

Read-Write copy database name : rwdb


Part 1: Create new database

1. Identify all the datafiles, redo log file and control files to be copied.

2. Create a SQL script to rename the datafiles and redo log files.

3. Create pfile from stbydb's spfile

4. Create pfile for rwdb

[oracle@naikh] cp initstbydb.ora initrwdb.ora

5. Shutdown the stbydb database

[oracle@naikh] export ORACLE_SID=stbydb

[oracle@naikh] sqlplus "/ as sysdba'

sql> alter database recover managed standby database cancel;

sql> shutdown immediate

6. Copy all the datafiles, redo log file and control files to new location

7. Edit the initrwdb.ora file

a. Change the CONTROL_FILE parameter path to new controlfile path

b. Remove followig parameters

i. standby_file_management

ii. log_archive_dest_1

iii. log_archive_format

c. Change the value of the following parameters with new value

i. audit_file_dest

ii. background_dump_dest

iii. core_dump_dest

iv. user_dump_dest

8. Create dump directories

[oracle@naikh] mkdir -p admin/rwdb/{adump,bdump,cdump,udump}

9. Rename the datafiles and redo log files

[oracle@naikh] cd $ORAACLE_HOME/dbs
[oracle@naikh] sqlplus "/ as sysdba'

sql> startup nomount pfile='initstbydb.ora'

sql> alter database mount;

sql> alter database rename file ''/old_path/system01.dbf' to
'/new_path/system01.dbf'; ;

.

.

.

10. Activate the standby database

sql> alter database activate standby database;

11. Open the database

sql> alter database open;

12. Create new temp tablespace, make this default and drop the old one.

sql> create temporary tablespace temp1
tempfile '/new_path/temp1.dbf' size 2048m
autoextend off
extent menagement local uniform size 1m;

sql> alter database default temporary tablespace temp1;

sql> drop tablespace temp;

13. Disable archive log mode

sql> shutdown immediate;
sql> startup mount pfile='initrwdb.ora';
sql> alter database noarchivelog;
sql> alter database open;


Part 2 : Rename the database

1. Mount the database

[oracle@naikh] cd $ORAACLE_HOME/dbs
[oracle@naikh] export ORACLE_SID=stbydb
[oracle@naikh] sqlplus "/ as sysdba'
sql> startup mount pfile='initrwdb.ora'

2. Run the NID utolity

[oracle@naikh] nid target=sys/oracle dbname=rwdb logfile=nid_rwdb.log

3. Edit the initrwdb.ora file

i. Change the DB_NAME parameter value to rwdb

4. Create new password file

[oracle@naikh] orapwd file=orapwrwdb password=oracle entries=5

5. Start the rwdb in reserlog mode

sql> alter dabase open resetlogs;

6. Create spfile

sql> create pfile from pfile;

7. Setup Listener and TNS


Part 3 : Start the standby database

1. Start the stdby database

[oracle@naikh] cd $ORAACLE_HOME/dbs
[oracle@naikh] export ORACLE_SID=stbydb

[oracle@naikh] sqlplus "/ as sysdba'

sql> startup nomount

sql> alter database mount standby database;

sql> alter database recover managed standby database disconnect;


Part 4 : Useful SQL statements

1. To get the command to copy all the datafiles,redolog files and controlfiles to new location use the following sql statement:

select 'cp '||name||'/new_path/'||
substr(name,instr(name,'/',-1,1)+1))||' &'
from (
select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile
)

2. To get the sql statements to Rename all datafiles and redolog files use the following sql statement:

select 'alter database rename file '''||name|| ''' to
''/new_path/'||substr(name,instr(name,'/',-1,1)+1)||''';'
from (
select name from v$datafile
union all
select member from v$logfile
)