10g duplicate and 11g dupliacte db for standby

###################10g

Creating a Physical Standby Database OASSTBY

Make sure database is in archivelog mode
(If it’s not then shutdown instance 2, shutdown then startup mount instance 1, enable archivelog mode, startup both instances)

node 1$ ssh node2 node 2$ sqlplus ‘/as sysdba’ SQL> shutdown immediate SQL> exit $ exit node 1$ sqlplus ‘/as sysdba’ SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> exit node 1$ ssh node2 node 2$ sqlplus ‘/as sysdba’ SQL> startup open

Backup primary database OASLIVE

(If using ASM then you must use RMAN to create the backup)

$ export ORACLE_SID=OASLIVE1 $ rman RMAN> connect target / connected to target database: OASLIVE (DBID=3404061247) RMAN> backup database plus archivelog format ‘/c04/oracle/oraback/OASLIVE/rman/OASLIVE_%U.rbak’

Copy control file from primary database for standby

RMAN> copy current controlfile for standby to '/home/oracle/OASSTBY.stby';
Copy backup file and control file to standby node (Node11)
$ cd /home/oracle/kerri $ scp OASSTBY.stby node11:/home/oracle/. $ cd /c04/oracle/oraback/OASLIVE/rman/ $ scp OASLIVE_*.rbak node11:/c04/oracle/oraback/OASLIVE/rman/.

Whilst waiting for the backup to copy across, create standby redo logs in the primary database OASLIVE

(Amount of standby logs per thread = no. of redo logs +1)

node 1$ sqlplus ‘/as sysdba’ SQL> select * from v$log; SQL> select group#, thread#, sequence#, status from v$standby_log; SQL> alter database add standby logfile thread 1 ('+LIVEDATA') size 256M; SQL> / SQL> / SQL> / SQL> / SQL> alter database add standby logfile thread 2 ('+LIVEDATA') size 250M; SQL> / SQL> / SQL> / SQL> / SQL> select group#, thread#, sequence#, status from v$standby_log;

Copy the server details of the standby node to the primary node tnsnames.ora

node 11$ cd $TNS_ADMIN Copy the following from OASSTBY tnsnames.ora to OASLIVE tnsnames.ora OASSTBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dgoh-data-11-vip)(PORT = 1511)) (CONNECT_DATA = (UR=A) (SERVER = DEDICATED) (SERVICE_NAME = OASSTBY.dudleygoh-tr.wmids.nhs.uk) ) )

Log into ASM and delete all pre-existing OASSTBY files

Check that both OASLIVE nodes have sysdba rman privileges if they don’t then run the following:

node 1$ sqlplus ‘/as sysdba’ SQLPLUS> grant sysdba to rman;

Create spfile for physical standby using modified pfile (see end of post for pfile details)

node 11$ sqlplus ‘/as sysdba’ SQLPLUS> startup nomount pfile='/home/oracle/kerri/OASSTBYpfile.ora.bkup'; SQL> create spfile from pfile='/home/oracle/kerri/OASSTBYpfile.ora.bkup'; SQL> shutdown immediate SQL> startup nomount

Log into RMAN and recover the standby database

node 11$ rman RMAN> connect target rman/o45rm4n@OASLIVE RMAN> connect auxiliary rman/o45rm4n@OASSTBY RMAN> duplicate target database for standby; <---duplicate

Copy across the password file

$ scp orapwOASLIVE node11:/u01/oracle/ora102/dbs/orapwOASSTBY Recover database

SQLPLUS> alter database recover managed standby database disconnect from session;

Appendix

Physical Standby spfile details for OASSTBY:

OASSTBY1.__db_cache_size=1191182336 OASSTBY.__java_pool_size=16777216 OASSTBY1.__java_pool_size=16777216 OASSTBY.__large_pool_size=16777216 OASSTBY1.__large_pool_size=16777216 OASSTBY.__shared_pool_size=352321536 OASSTBY1.__shared_pool_size=369098752 OASSTBY.__streams_pool_size=0 OASSTBY1.__streams_pool_size=0 *.audit_file_dest='/u01/oracle/admin/OASSTBY/adump' *.background_dump_dest='/u01/oracle/admin/OASSTBY/bdump' *.cluster_database=true *.cluster_database_instances=2 *.compatible='10.2.0.4.0' *.control_files='+LIVEDATA’ *.core_dump_dest='/u01/oracle/admin/OASSTBY/cdump' *.db_block_size=8192 *.db_create_file_dest='+LIVEDATA' *.db_domain='dudleygoh-tr.wmids.nhs.uk' *.db_file_multiblock_read_count=8 *.db_file_name_convert='OASLIVE','OASSTBY' *.db_name='OASLIVE' *.db_unique_name='OASSTBY' *.dispatchers='(PROTOCOL=TCP) (SERVICE=OASSTBYXDB)' *.fal_client='OASSTBY1' *.fal_server='OASLIVE1,OASLIVE2' OASSTBY1.instance_number=1 OASSTBY2.instance_number=2 *.job_queue_processes=1 OASSTBY2.local_listener='LISTENER_OASSTBY2' OASSTBY1.local_listener='LISTENER_OASSTBY1' *.log_archive_config='DG_CONFIG=(OASLIVE,OASSTBY,OASREP)' *.log_archive_dest_1='LOCATION=+LIVEARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OASSTBY' *.log_archive_dest_2='SERVICE=OASLIVE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=OASLIVE' *.log_archive_dest_3='SERVICE=OASREP LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASREP' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=10 *.open_cursors=300 *.pga_aggregate_target=2503999488 *.processes=600 *.remote_listener='LISTENERS_OASSTBY' *.remote_login_passwordfile='exclusive' *.sga_target=1610612736 *.standby_file_management='AUTO' OASSTBY2.thread=2 OASSTBY1.thread=1 *.undo_management='AUTO' OASSTBY1.undo_tablespace='UNDOTBS1' OASSTBY2.undo_tablespace='UNDOTBS2' *.user_dump_dest='/u01/oracle/admin/OASSTBY/udump' *.utl_file_dir='*'

###2

select 'copy datafile '||file_id||' /db/dba/'||regexp_substr(file_name,'[^/]+',1,4)||';' from dba_data_files

##10g standby db only mount and start mrp

#########################11g duplicate

#########standby
create pfile='/tmp/pfile.ora' from spfile;

orapwd file=/oracle/product/database/11.2.0.3/dbs/orapwdb

rman target sys/oracle@db auxiliary sys/oracle@rdb

##rman target sys/sys@db auxiliary sys/sys@rdb

duplicate target database for standby from active database nofilenamecheck; <- --

lsnrctl status db

sqlplus sys/oracle@rdb as sysdba

alter database open;

select database_role from v$database;

alter database recover managed standby database using current logfile disconnect from session;

######10.241.90.92 primary

tnsping rdb
tnsping db

rdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 25.10.0.197)(PORT = 1527))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db)
)
)

db=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.241.90.92)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db)))

##11g standby db can mount and start mrp also can open and start mrp

巴特西