PRIMARY Configuration
Step: 1
PRIMARY Database should be in archive log mode, enable if not already done.
[code] [primary:oracle:primary]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 30 16:16:53 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch Oldest online log sequence 4 Current log sequence 6 [/code]
PRIMARY - Enable archive log mode using following commands
[code] SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount ORACLE instance started. Total System Global Area 1392508864 bytes Fixed Size 8896448 bytes Variable Size 872415232 bytes Database Buffers 503316480 bytes Redo Buffers 7880704 bytes Database mounted. SQL> SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 [/code]
Step: 2
PRIMARY - FORCE LOGGING should be enabled
[code] SQL> select FORCE_LOGGING,log_mode from v$database; FORCE_LOGGING LOG_MODE --------------------------------------- ------------ NO ARCHIVELOG [/code]
PRIMARY - Enable FORCE LOGGING using following commands
[code] SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> select FORCE_LOGGING,log_mode from v$database; FORCE_LOGGING LOG_MODE --------------------------------------- ------------ YES ARCHIVELOG [/code]
Step: 3
PRIMARY - Add standby logfiles
In standby setup, archives log from primary shipped to standby and applied there. But if the primary database crashes, then the online redo logs will stay in primary side. As these logs hasn’t been archives, means those won’t be applied to standby, Which results in data loss. So if we add standby logfile, Data guard writes the Primary’s current redo log to a "standby redo log" allowing complete recovery in case of Primary site is lost. [code] SQL> select GROUP#,BYTES/1024/1024,thread# from v$log; GROUP# BYTES/1024/1024 THREAD# ---------- --------------- ---------- 1 200 1 2 200 1 3 200 1 SQL> col member for a45 SQL> set pagesize 200 SQL> set lines 200 SQL> select GROUP#,MEMBER from v$logfile; GROUP# MEMBER ---------- --------------------------------------------- 3 /u01/app/oracle/oradata/PRIMARY/redo03.log 2 /u01/app/oracle/oradata/PRIMARY/redo02.log 1 /u01/app/oracle/oradata/PRIMARY/redo01.log [/code]
Use following dynamic query to generate add standby logfiles
[code] SQL> select 'alter database add standby logfile '''|| regexp_substr(MEMBER,'/.+/')||'stdby_'|| regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')|| ''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l where l.group# = lf.group# 2 3 4 5 6 ; Create Standby redo ----------------------------------------------------------------------------------------------------- alter database add standby logfile '/u01/app/oracle/oradata/PRIMARY/stdby_redo03.log' size 209715200; alter database add standby logfile '/u01/app/oracle/oradata/PRIMARY/stdby_redo02.log' size 209715200; alter database add standby logfile '/u01/app/oracle/oradata/PRIMARY/stdby_redo01.log' size 209715200; [/code]
Execute generated add standby logfile statements
[code] SQL> alter database add standby logfile '/u01/app/oracle/oradata/PRIMARY/stdby_redo03.log' size 209715200; alter database add standby logfile '/u01/app/oracle/oradata/PRIMARY/stdby_redo02.log' size 209715200; alter database add standby logfile '/u01/app/oracle/oradata/PRIMARY/stdby_redo01.log' size 209715200; Database altered. SQL> Database altered. SQL> Database altered. [/code]
Verify added standby logfiles
[code] SQL> select GROUP#,MEMBER from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------- 3 /u01/app/oracle/oradata/PRIMARY/redo03.log 2 /u01/app/oracle/oradata/PRIMARY/redo02.log 1 /u01/app/oracle/oradata/PRIMARY/redo01.log 4 /u01/app/oracle/oradata/PRIMARY/stdby_redo03.log 5 /u01/app/oracle/oradata/PRIMARY/stdby_redo02.log 6 /u01/app/oracle/oradata/PRIMARY/stdby_redo01.log 6 rows selected. [/code]
Step: 4
PRIMARY and STANDBY - Add network entry in both PRIMARY & STANDBY
PRIMARY
[code] [primary:oracle:primary]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRIMARY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary.oracledbashop.com) ) ) STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.115)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby.oracledbashop.com) ) ) [/code]
STANDBY
[code] [standby:oracle:standby]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRIMARY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary.oracledbashop.com) ) ) STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.115)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby.oracledbashop.com) ) ) [/code]
Step: 5
PRIMARY and STANDBY - Configure LISTENER in both PRIMARY & STANDBY
PRIMARY
[code] [primary:oracle:primary]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbashop.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = primary) ) ) [/code]
STANDBY
[code] [standby:oracle:standby]$ cat listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracledbashop.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = standby) (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = standby) ) ) [/code]
TNSPING should work from both PRIMARY and STANDBY
Step: 6
PRIMARY - Create db_recovery_file_dest if already not setup
[code] SQL> alter system set db_recovery_file_dest_size=2G scope=both; System altered. SQL> !mkdir -p /u01/app/oracle/FRA SQL> SQL> SQL> alter system set db_recovery_file_dest='/u01/app/oracle/FRA' scope=both; System altered. SQL> SQL> SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/FRA db_recovery_file_dest_size big integer 2G [/code]
PRIMARY - Change following parameters
[code] SQL> ALTER SYSTEM SET log_archive_config='dg_config=(primary,standby)' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/FRA valid_for=(all_logfiles,all_roles) db_unique_name=primary' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET log_archive_dest_2='service=standby async valid_for=(online_logfiles,primary_role) db_unique_name=standby' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET fal_server='STANDBY' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET fal_client='PRIMARY' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE; System altered. [/code]
STANDBY Configuration
[code] [standby:oracle:standby]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRIMARY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary.oracledbashop.com) ) ) STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.115)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby.oracledbashop.com) ) ) [/code]
Step: 5
PRIMARY and STANDBY - Configure LISTENER in both PRIMARY & STANDBY
PRIMARY
[code] [primary:oracle:primary]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbashop.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = primary) ) ) [/code]
STANDBY
[code] [standby:oracle:standby]$ cat listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracledbashop.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = standby) (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = standby) ) ) [/code]
TNSPING should work from both PRIMARY and STANDBY
Step: 6
PRIMARY - Create db_recovery_file_dest if already not setup
[code] SQL> alter system set db_recovery_file_dest_size=2G scope=both; System altered. SQL> !mkdir -p /u01/app/oracle/FRA SQL> SQL> SQL> alter system set db_recovery_file_dest='/u01/app/oracle/FRA' scope=both; System altered. SQL> SQL> SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/FRA db_recovery_file_dest_size big integer 2G [/code]
PRIMARY - Change following parameters
[code] SQL> ALTER SYSTEM SET log_archive_config='dg_config=(primary,standby)' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/FRA valid_for=(all_logfiles,all_roles) db_unique_name=primary' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET log_archive_dest_2='service=standby async valid_for=(online_logfiles,primary_role) db_unique_name=standby' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET fal_server='STANDBY' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET fal_client='PRIMARY' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE; System altered. [/code]
STANDBY Configuration
Step: 1
STANDBY - Copy password file from PRIMARY to STANDBY and rename.
[code] [primary:oracle:primary]$ scp orapwprimary oracle@192.168.56.115:/u01/app/oracle/product/19.0.0/dbhome_1/dbs The authenticity of host '192.168.56.115 (192.168.56.115)' can't be established. ECDSA key fingerprint is SHA256:AQtaEFRQKn4XYNLBKSA/qSNZXm8NJI5BFK4aX0S2xRU. ECDSA key fingerprint is MD5:d9:3b:d2:79:9b:c9:42:c8:04:e2:94:47:0d:3b:26:9e. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.56.115' (ECDSA) to the list of known hosts. oracle@192.168.56.115's password: orapwprimary 100% 2048 2.0MB/s 00:00 [/code]
Rename password file.
[code] [standby:oracle:standby]$ ls init.ora orapwprimary [standby:oracle:standby]$ mv orapwprimary orapwstandby [standby:oracle:standby]$ ls init.ora orapwstandby [/code]
Step: 2
STANDBY - Add only one parameter in init file (pfile)
DB_NAME=primary
[code] [standby:oracle:standby]$ cat initstandby.ora DB_NAME=primary [standby:oracle:standby]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/dbs [/code]
Step: 3
STANDBY - Create directory structure
[code] [standby:oracle:standby]$ mkdir -p /u01/app/oracle/oradata/STANDBY [standby:oracle:standby]$ mkdir -p /u01/app/oracle/oradata/STANDBY [standby:oracle:standby]$ mkdir -p /u01/app/oracle/admin/standby/adump [/code]
Step: 4
STANDBY - Start database in "nomount" using pfile.
[code] [standby:oracle:standby]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 30 17:21:49 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='initstandby.ora' nomount; ORACLE instance started. Total System Global Area 285211696 bytes Fixed Size 8895536 bytes Variable Size 218103808 bytes Database Buffers 50331648 bytes Redo Buffers 7880704 bytes [/code]
Step: 5
STANDBY and PRIMARY - Try connecting to the RMAN
PRIMARY
[code] [primary:oracle:primary]$ rman target sys/oracle@primary Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 30 18:25:30 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1879271900) RMAN> connect auxiliary sys/oracle@standby connected to auxiliary database: PRIMARY (not mounted) [/code]
STANDBY
[code] [standby:oracle:standby]$ rman target sys/oracle@primary auxiliary sys/oracle@standby Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 30 18:27:41 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1879271900) connected to auxiliary database: PRIMARY (not mounted) [/code]
STANDBY - Run RMAN duplicate command
[code] [standby:oracle:standby]$ rrman target sys/oracle@primary auxiliary sys/oracle@standby Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 30 19:38:27 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1879271900) connected to auxiliary database: PRIMARY (not mounted) RMAN> run 2> { allocate channel p1 type disk; allocate channel p2 type disk; allocate channel p3 type disk; allocate channel p4 type disk; allocate auxiliary channel s1 type disk; duplicate target database for standby from active database 3> 4> 5> spfile parameter_value_convert 'primary','standby' set db_name='primary' set db_unique_name='standby' set db_file_name_convert='/u01/app/oracle/oradata/PRIMARY/','/u01/app/oracle/oradata/STANDBY/' set log_file_name_convert='/u01/app/oracle/oradata/PRIMARY/','/u01/app/oracle/oradata/STANDBY/' set control_files='/u01/app/oracle/oradata/STANDBY/control01.ctl','/u01/app/oracle/oradata/STANDBY/control02.ctl' set log_archive_max_processes='5' set fal_client='standby' set fal_server='primary' set standby_file_management='AUTO' set log_archive_config='dg_config=(primary,standby)' nofilenamecheck; }6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> using target database control file instead of recovery catalog allocated channel: p1 channel p1: SID=340 device type=DISK allocated channel: p2 channel p2: SID=27 device type=DISK allocated channel: p3 channel p3: SID=185 device type=DISK allocated channel: p4 channel p4: SID=343 device type=DISK allocated channel: s1 channel s1: SID=14 device type=DISK Starting Duplicate Db at 30-MAY-20 contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwstandby' targetfile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileprimary.ora' auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestandby.ora' ; sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestandby.ora''"; } executing Memory Script Starting backup at 30-MAY-20 Finished backup at 30-MAY-20 sql statement: alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestandby.ora'' contents of Memory Script: { sql clone "alter system set audit_file_dest = ''/u01/app/oracle/admin/standby/adump'' comment= '''' scope=spfile"; sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=standbyXDB)'' comment= '''' scope=spfile"; sql clone "alter system set db_name = ''primary'' comment= '''' scope=spfile"; sql clone "alter system set db_unique_name = ''standby'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/u01/app/oracle/oradata/STANDBY/control01.ctl'', ''/u01/app/oracle/oradata/STANDBY/control02.ctl'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_max_processes = 5 comment= '''' scope=spfile"; sql clone "alter system set fal_client = ''standby'' comment= '''' scope=spfile"; sql clone "alter system set fal_server = ''primary'' comment= '''' scope=spfile"; sql clone "alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_config = ''dg_config=(primary,standby)'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/standby/adump'' comment= '''' scope=spfile sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=standbyXDB)'' comment= '''' scope=spfile sql statement: alter system set db_name = ''primary'' comment= '''' scope=spfile sql statement: alter system set db_unique_name = ''standby'' comment= '''' scope=spfile sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile sql statement: alter system set control_files = ''/u01/app/oracle/oradata/STANDBY/control01.ctl'', ''/u01/app/oracle/oradata/STANDBY/control02.ctl'' comment= '''' scope=spfile sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile sql statement: alter system set fal_client = ''standby'' comment= '''' scope=spfile sql statement: alter system set fal_server = ''primary'' comment= '''' scope=spfile sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile sql statement: alter system set log_archive_config = ''dg_config=(primary,standby)'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 1207958960 bytes Fixed Size 8895920 bytes Variable Size 721420288 bytes Database Buffers 469762048 bytes Redo Buffers 7880704 bytes allocated channel: s1 channel s1: SID=172 device type=DISK contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/STANDBY/control01.ctl'; restore clone primary controlfile to '/u01/app/oracle/oradata/STANDBY/control02.ctl' from '/u01/app/oracle/oradata/STANDBY/control01.ctl'; } executing Memory Script Starting backup at 30-MAY-20 channel p1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_primary.f tag=TAG20200530T193910 channel p1: datafile copy complete, elapsed time: 00:00:02 Finished backup at 30-MAY-20 Starting restore at 30-MAY-20 channel s1: copied control file copy Finished restore at 30-MAY-20 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/STANDBY/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/STANDBY/system01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/STANDBY/sysaux01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/STANDBY/undotbs01.dbf"; set newname for datafile 7 to "/u01/app/oracle/oradata/STANDBY/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/STANDBY/system01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/STANDBY/sysaux01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/STANDBY/undotbs01.dbf" datafile 7 auxiliary format "/u01/app/oracle/oradata/STANDBY/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/STANDBY/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 30-MAY-20 channel p1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMARY/system01.dbf channel p2: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMARY/sysaux01.dbf channel p3: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMARY/undotbs01.dbf channel p4: starting datafile copy input datafile file number=00007 name=/u01/app/oracle/oradata/PRIMARY/users01.dbf output file name=/u01/app/oracle/oradata/STANDBY/users01.dbf tag=TAG20200530T193917 channel p4: datafile copy complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/STANDBY/undotbs01.dbf tag=TAG20200530T193917 channel p3: datafile copy complete, elapsed time: 00:00:07 output file name=/u01/app/oracle/oradata/STANDBY/system01.dbf tag=TAG20200530T193917 channel p1: datafile copy complete, elapsed time: 00:00:15 output file name=/u01/app/oracle/oradata/STANDBY/sysaux01.dbf tag=TAG20200530T193917 channel p2: datafile copy complete, elapsed time: 00:00:15 Finished backup at 30-MAY-20 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/undotbs01.dbf datafile 7 switched to datafile copy input datafile copy RECID=4 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/users01.dbf Finished Duplicate Db at 30-MAY-20 released channel: p1 released channel: p2 released channel: p3 released channel: p4 released channel: s1 RMAN> quit Recovery Manager complete. [/code]
Step: 6
STANDBY - Connect to standby database and start the RECOVERY.
[code] [standby:oracle:standby]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 30 23:36:11 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database recover managed standby database using current logfile disconnect; Database altered. [/code]
Step: 7
STANDBY - Check physical standby status
[code] SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 6 30-MAY-20 30-MAY-20 NO SQL> / SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 6 30-MAY-20 30-MAY-20 YES 7 30-MAY-20 30-MAY-20 NO 8 30-MAY-20 30-MAY-20 NO 9 30-MAY-20 30-MAY-20 NO [/code]
Step: 8
PRIMARY - Give few "alter system switch logfile" at PRIMARY
[code]
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> !hostname
primary.oracledbashop.com
[/code]
Step: 9
STANDBY - Check the SYNC status using following query
STANDBY - Copy password file from PRIMARY to STANDBY and rename.
[code] [primary:oracle:primary]$ scp orapwprimary oracle@192.168.56.115:/u01/app/oracle/product/19.0.0/dbhome_1/dbs The authenticity of host '192.168.56.115 (192.168.56.115)' can't be established. ECDSA key fingerprint is SHA256:AQtaEFRQKn4XYNLBKSA/qSNZXm8NJI5BFK4aX0S2xRU. ECDSA key fingerprint is MD5:d9:3b:d2:79:9b:c9:42:c8:04:e2:94:47:0d:3b:26:9e. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.56.115' (ECDSA) to the list of known hosts. oracle@192.168.56.115's password: orapwprimary 100% 2048 2.0MB/s 00:00 [/code]
Rename password file.
[code] [standby:oracle:standby]$ ls init.ora orapwprimary [standby:oracle:standby]$ mv orapwprimary orapwstandby [standby:oracle:standby]$ ls init.ora orapwstandby [/code]
Step: 2
STANDBY - Add only one parameter in init file (pfile)
DB_NAME=primary
[code] [standby:oracle:standby]$ cat initstandby.ora DB_NAME=primary [standby:oracle:standby]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/dbs [/code]
Step: 3
STANDBY - Create directory structure
[code] [standby:oracle:standby]$ mkdir -p /u01/app/oracle/oradata/STANDBY [standby:oracle:standby]$ mkdir -p /u01/app/oracle/oradata/STANDBY [standby:oracle:standby]$ mkdir -p /u01/app/oracle/admin/standby/adump [/code]
Step: 4
STANDBY - Start database in "nomount" using pfile.
[code] [standby:oracle:standby]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 30 17:21:49 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='initstandby.ora' nomount; ORACLE instance started. Total System Global Area 285211696 bytes Fixed Size 8895536 bytes Variable Size 218103808 bytes Database Buffers 50331648 bytes Redo Buffers 7880704 bytes [/code]
Step: 5
STANDBY and PRIMARY - Try connecting to the RMAN
PRIMARY
[code] [primary:oracle:primary]$ rman target sys/oracle@primary Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 30 18:25:30 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1879271900) RMAN> connect auxiliary sys/oracle@standby connected to auxiliary database: PRIMARY (not mounted) [/code]
STANDBY
[code] [standby:oracle:standby]$ rman target sys/oracle@primary auxiliary sys/oracle@standby Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 30 18:27:41 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1879271900) connected to auxiliary database: PRIMARY (not mounted) [/code]
STANDBY - Run RMAN duplicate command
[code] [standby:oracle:standby]$ rrman target sys/oracle@primary auxiliary sys/oracle@standby Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 30 19:38:27 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1879271900) connected to auxiliary database: PRIMARY (not mounted) RMAN> run 2> { allocate channel p1 type disk; allocate channel p2 type disk; allocate channel p3 type disk; allocate channel p4 type disk; allocate auxiliary channel s1 type disk; duplicate target database for standby from active database 3> 4> 5> spfile parameter_value_convert 'primary','standby' set db_name='primary' set db_unique_name='standby' set db_file_name_convert='/u01/app/oracle/oradata/PRIMARY/','/u01/app/oracle/oradata/STANDBY/' set log_file_name_convert='/u01/app/oracle/oradata/PRIMARY/','/u01/app/oracle/oradata/STANDBY/' set control_files='/u01/app/oracle/oradata/STANDBY/control01.ctl','/u01/app/oracle/oradata/STANDBY/control02.ctl' set log_archive_max_processes='5' set fal_client='standby' set fal_server='primary' set standby_file_management='AUTO' set log_archive_config='dg_config=(primary,standby)' nofilenamecheck; }6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> using target database control file instead of recovery catalog allocated channel: p1 channel p1: SID=340 device type=DISK allocated channel: p2 channel p2: SID=27 device type=DISK allocated channel: p3 channel p3: SID=185 device type=DISK allocated channel: p4 channel p4: SID=343 device type=DISK allocated channel: s1 channel s1: SID=14 device type=DISK Starting Duplicate Db at 30-MAY-20 contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwstandby' targetfile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileprimary.ora' auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestandby.ora' ; sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestandby.ora''"; } executing Memory Script Starting backup at 30-MAY-20 Finished backup at 30-MAY-20 sql statement: alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestandby.ora'' contents of Memory Script: { sql clone "alter system set audit_file_dest = ''/u01/app/oracle/admin/standby/adump'' comment= '''' scope=spfile"; sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=standbyXDB)'' comment= '''' scope=spfile"; sql clone "alter system set db_name = ''primary'' comment= '''' scope=spfile"; sql clone "alter system set db_unique_name = ''standby'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/u01/app/oracle/oradata/STANDBY/control01.ctl'', ''/u01/app/oracle/oradata/STANDBY/control02.ctl'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_max_processes = 5 comment= '''' scope=spfile"; sql clone "alter system set fal_client = ''standby'' comment= '''' scope=spfile"; sql clone "alter system set fal_server = ''primary'' comment= '''' scope=spfile"; sql clone "alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_config = ''dg_config=(primary,standby)'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/standby/adump'' comment= '''' scope=spfile sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=standbyXDB)'' comment= '''' scope=spfile sql statement: alter system set db_name = ''primary'' comment= '''' scope=spfile sql statement: alter system set db_unique_name = ''standby'' comment= '''' scope=spfile sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/PRIMARY/'', ''/u01/app/oracle/oradata/STANDBY/'' comment= '''' scope=spfile sql statement: alter system set control_files = ''/u01/app/oracle/oradata/STANDBY/control01.ctl'', ''/u01/app/oracle/oradata/STANDBY/control02.ctl'' comment= '''' scope=spfile sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile sql statement: alter system set fal_client = ''standby'' comment= '''' scope=spfile sql statement: alter system set fal_server = ''primary'' comment= '''' scope=spfile sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile sql statement: alter system set log_archive_config = ''dg_config=(primary,standby)'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 1207958960 bytes Fixed Size 8895920 bytes Variable Size 721420288 bytes Database Buffers 469762048 bytes Redo Buffers 7880704 bytes allocated channel: s1 channel s1: SID=172 device type=DISK contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/STANDBY/control01.ctl'; restore clone primary controlfile to '/u01/app/oracle/oradata/STANDBY/control02.ctl' from '/u01/app/oracle/oradata/STANDBY/control01.ctl'; } executing Memory Script Starting backup at 30-MAY-20 channel p1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_primary.f tag=TAG20200530T193910 channel p1: datafile copy complete, elapsed time: 00:00:02 Finished backup at 30-MAY-20 Starting restore at 30-MAY-20 channel s1: copied control file copy Finished restore at 30-MAY-20 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/STANDBY/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/STANDBY/system01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/STANDBY/sysaux01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/STANDBY/undotbs01.dbf"; set newname for datafile 7 to "/u01/app/oracle/oradata/STANDBY/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/STANDBY/system01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/STANDBY/sysaux01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/STANDBY/undotbs01.dbf" datafile 7 auxiliary format "/u01/app/oracle/oradata/STANDBY/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/STANDBY/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 30-MAY-20 channel p1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMARY/system01.dbf channel p2: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMARY/sysaux01.dbf channel p3: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMARY/undotbs01.dbf channel p4: starting datafile copy input datafile file number=00007 name=/u01/app/oracle/oradata/PRIMARY/users01.dbf output file name=/u01/app/oracle/oradata/STANDBY/users01.dbf tag=TAG20200530T193917 channel p4: datafile copy complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/STANDBY/undotbs01.dbf tag=TAG20200530T193917 channel p3: datafile copy complete, elapsed time: 00:00:07 output file name=/u01/app/oracle/oradata/STANDBY/system01.dbf tag=TAG20200530T193917 channel p1: datafile copy complete, elapsed time: 00:00:15 output file name=/u01/app/oracle/oradata/STANDBY/sysaux01.dbf tag=TAG20200530T193917 channel p2: datafile copy complete, elapsed time: 00:00:15 Finished backup at 30-MAY-20 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/undotbs01.dbf datafile 7 switched to datafile copy input datafile copy RECID=4 STAMP=1041795573 file name=/u01/app/oracle/oradata/STANDBY/users01.dbf Finished Duplicate Db at 30-MAY-20 released channel: p1 released channel: p2 released channel: p3 released channel: p4 released channel: s1 RMAN> quit Recovery Manager complete. [/code]
Step: 6
STANDBY - Connect to standby database and start the RECOVERY.
[code] [standby:oracle:standby]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 30 23:36:11 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database recover managed standby database using current logfile disconnect; Database altered. [/code]
Step: 7
STANDBY - Check physical standby status
[code] SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 6 30-MAY-20 30-MAY-20 NO SQL> / SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 6 30-MAY-20 30-MAY-20 YES 7 30-MAY-20 30-MAY-20 NO 8 30-MAY-20 30-MAY-20 NO 9 30-MAY-20 30-MAY-20 NO [/code]
Step: 8
PRIMARY - Give few "alter system switch logfile" at PRIMARY
Step: 9
STANDBY - Check the SYNC status using following query
No comments:
Post a Comment