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
