****2 NODE RAC TO SINGLE INSTANCE DATA GUARD CONFIGURATION***
Switchover steps in below link :
1)*************Pre_Info**************
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- --------------------
1 Findb1 FINDB dm01db01.database.com PRIMARY ARCHIVELOG READ WRITE 12.1.0.1.0 ALLOWED 26-APR-2020 16:41:40
2 Findb2 FINDB dm01db02.database.com PRIMARY ARCHIVELOG READ WRITE 12.1.0.1.0 ALLOWED 26-APR-2020 16:41:22
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FINPDB READ WRITE NO
SQL>
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL> select GROUP#,THREAD#,BYTES from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------------
+DATA/FINDB/ONLINELOG/group_2.263.1038428391
+FRA/FINDB/ONLINELOG/group_2.258.1038428393
+DATA/FINDB/ONLINELOG/group_1.262.1038428389
+FRA/FINDB/ONLINELOG/group_1.257.1038428391
+DATA/FINDB/ONLINELOG/group_3.269.1038428699
+FRA/FINDB/ONLINELOG/group_3.259.1038428719
+DATA/FINDB/ONLINELOG/group_4.270.1038428721
+FRA/FINDB/ONLINELOG/group_4.260.1038428723
8 rows selected.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string Findb
SQL>
SQL>
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string Findb
2)***PARAMETERS TO BE SET AT PRIMARY DATABASE***
--alter system set log_archive_config='DG_CONFIG=(Findb,Findr)' SCOPE=both sid='*';
-- alter system set db_unique_name=Findb;
-- alter system set fal_server=Findr SCOPE=both sid='*'; (----unique name of second DR machine)
-- alter system set fal_client=Findb SCOPE=both sid='*'; (----unique name of Primary machine)
--alter system set log_archive_dest_1='LOCATION=+FRA' SCOPE=both sid='*';
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=Findr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Findr' SCOPE=both sid='*';
--alter system set log_archive_dest_state_1=enable SCOPE=both sid='*';
--alter system set log_archive_dest_state_2=enable SCOPE=both sid='*';
--alter system set standby_file_management=auto SCOPE=both sid='*';
3)*****Tnsfile entry for Primary and DR******
Tnsfile entry for Primary and DR :
Findr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm01dr01.database.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Findr.database.com)
(UR = A)
)
)
Findb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm01scan.database.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Findb.database.com)
(UR = A)
)
)
4)*****Listener file entry For Primary and DR*****
Primary Database example for Listener.ora
[grid@dm01db01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/app/grid/gr_home/network/admin/listener.ora
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR = SUBNET
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2 = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Findb.database.com)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findb)
)
(SID_DESC =
(GLOBAL_DBNAME = Findr.database.com)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findr)
)
)
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1 = OFF
VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET
MGMTLSNR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = MGMTLSNR))
)
ADR_BASE_MGMTLSNR = /oracle/app/grid/gr_base
SID_LIST_LISTENER_SCAN2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Findb)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findb)
)
(SID_DESC =
(GLOBAL_DBNAME = Findr)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findr)
)
)
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Findb)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findb)
)
(SID_DESC =
(GLOBAL_DBNAME = Findr)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findr)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR = ON
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
ADR_BASE_LISTENER = /oracle/app/grid/gr_base
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))
)
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN2 = /oracle/app/grid/gr_base
ADR_BASE_LISTENER_SCAN1 = /oracle/app/grid/gr_base
Standby Database example for listener.ora
[grid@dm01dr01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/app/grid/gr_home/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Findb.database.com)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findb)
)
(SID_DESC =
(GLOBAL_DBNAME = Findr.database.com)
(ORACLE_HOME = /oracle/app/orawork/product/12.1.0.2/db_1)
(SID_NAME = Findr)
)
)
VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm01dr01.database.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /oracle/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
4.1)*****Tnsping output after listener and tnsfile config********
Prod
[oracle@dm01db02 admin]$ tnsping findb
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-APR-2020 23:54:02
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dm01scan.database.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Findb.database.com) (UR = A)))
OK (0 msec)
[oracle@dm01db02 admin]$
[oracle@dm01db02 admin]$ tnsping findr
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-APR-2020 23:54:05
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dm01dr01.database.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Findr.database.com) (UR = A)))
OK (10 msec)
DR :
[oracle@dm01dr01 dbs]$ tnsping findb
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-APR-2020 23:53:17
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dm01scan.database.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Findb.database.com) (UR = A)))
OK (0 msec)
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$ tnsping findr
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-APR-2020 23:53:23
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dm01dr01.database.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Findr.database.com) (UR = A)))
OK (0 msec)
4.2)*******Primary Database Pfile********
Findb2.__data_transfer_cache_size=0
Findb1.__data_transfer_cache_size=0
Findb1.__db_cache_size=268435456
Findb2.__db_cache_size=285212672
Findb2.__java_pool_size=16777216
Findb1.__java_pool_size=16777216
Findb1.__large_pool_size=33554432
Findb2.__large_pool_size=33554432
Findb1.__oracle_base='/oracle/app/orawork'#ORACLE_BASE set from environment
Findb2.__oracle_base='/oracle/app/orawork'#ORACLE_BASE set from environment
Findb2.__pga_aggregate_target=385875968
Findb1.__pga_aggregate_target=385875968
Findb2.__sga_target=754974720
Findb1.__sga_target=754974720
Findb1.__shared_io_pool_size=33554432
Findb2.__shared_io_pool_size=33554432
Findb1.__shared_pool_size=385875968
Findb2.__shared_pool_size=369098752
Findb2.__streams_pool_size=0
Findb1.__streams_pool_size=0
*.audit_file_dest='/oracle/app/orawork/admin/Findr/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.0.0'
*.control_files='+DATA/FINDB/CONTROLFILE/current.261.1038428387','+FRA/FINDB/CONTROLFILE/current.256.1038428387'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='database.com'
*.db_name='Findb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=9000m
*.diagnostic_dest='/oracle/app/orawork'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=FindbXDB)'
*.enable_pluggable_database=true
*.fal_client='FINDB'
*.fal_server='FINDR'
Findb2.instance_number=2
Findb1.instance_number=1
*.log_archive_config='DG_CONFIG=(Findb,Findr)'
*.log_archive_dest_1='LOCATION=+FRA'
*.log_archive_dest_2='SERVICE=Findr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Findr'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1080m
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='exclusive'
*.sessions=555
*.standby_file_management='AUTO'
Findb2.thread=2
Findb1.thread=1
Findb2.undo_tablespace='UNDOTBS2'
Findb1.undo_tablespace='UNDOTBS1'
4.3)*********Standby Database pfile*********
*.audit_file_dest='/oracle/app/orawork/admin/Findr/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='database.com'
*.instance_name=Findr
*.db_name='Findb'
*.db_unique_name='Findr'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=9000m
*.diagnostic_dest='/oracle/app/orawork'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=FindrXDB)'
*.enable_pluggable_database=true
*.fal_client='FINDR'
*.fal_server='FINDB'
*.log_archive_config='DG_CONFIG=(Findr,Findb)'
*.log_archive_dest_1='LOCATION=+FRA'
*.log_archive_dest_2='SERVICE=Findb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Findb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1080m
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='exclusive'
*.sessions=555
*.standby_file_management='AUTO'
*.enable_pluggable_database=true
5)*******ON STANDBY DATABASE******
--FIRST MAKE A SERVICE
--export ORACLE_SID=Findr
--export ORACLE_HOME=/oracle/app/orawork/product/12.1.0.2/db_1
--alter system set fal_server=Findb
--alter system set fal_client=Findr
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=Findb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Findb' SCOPE=both sid='*';
--alter system set log_archive_dest_1='LOCATION=+FRA' scope=both;
--alter system set db_unique_name=Findr
6)*******Startup Standby database to nomount********
mount -t tmpfs shmfs -o size=2G /dev/shm
[oracle@dm01dr01 dbs]$ export ORACLE_SID=Findr
[oracle@dm01dr01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Apr 26 20:52:31 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 905971800 bytes
Database Buffers 218103808 bytes
Redo Buffers 9383936 bytes
7)*******Connect primary Rman with below command auxillary standby******
[oracle@dm01dr01 dbs]$ rman target sys/Password1@Findb auxiliary sys/Password1@Findr
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Apr 26 23:52:21 2020
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: FINDB (DBID=764828770)
connected to auxiliary database: FINDB (not mounted)
Exit
[oracle@dm01dr01 orawork]$ cat restore_RACDG.sh
export ORACLE_SID=Findr
export ORACLE_HOME=/oracle/app/orawork/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT='YYYY-MM-DD:hh24:mi:ss'
date
echo "Begin restore"
rman target sys/Password1@Findb auxiliary sys/Password1@Findr
cmdfile=/oracle/app/orawork/restore_Racdg.rcv
log=/oracle/app/orawork/restore_Racdg.log
date
echo "End restore"
[oracle@dm01dr01 orawork]$ cat restore_RACDG.rcv
run
{
ALLOCATE CHANNEL CHNL1 DEVICE TYPE disk;
ALLOCATE CHANNEL CHNL2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL CHNL3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL CHNL4 DEVICE TYPE disk;
duplicate target database for standby from active database;
RELEASE CHANNEL CHNL1;
RELEASE CHANNEL CHNL2;
RELEASE CHANNEL CHNL3;
RELEASE CHANNEL CHNL4;
}
8) ************LOG FILE*******************
[oracle@dm01dr01 app]$ rman target sys/Password1@Findb auxiliary sys/Password1@Findr
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Apr 27 23:21:33 2020
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: FINDB (DBID=764828770)
connected to auxiliary database: FINDB (not mounted)
RMAN> run
{
ALLOCATE CHANNEL CHNL1 DEVICE TYPE disk;
ALLOCATE CHANNEL CHNL2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL CHNL3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL CHNL4 DEVICE TYPE disk;
duplicate target database for standby from active database;
RELEASE CHANNEL CHNL1;
RELEASE CHANNEL CHNL2;
RELEASE CHANNEL CHNL3;
RELEASE CHANNEL CHNL4;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
using target database control file instead of recovery catalog
allocated channel: CHNL1
channel CHNL1: SID=68 instance=Findb2 device type=DISK
allocated channel: CHNL2
channel CHNL2: SID=80 instance=Findb1 device type=DISK
allocated channel: CHNL3
channel CHNL3: SID=7 device type=DISK
allocated channel: CHNL4
channel CHNL4: SID=25 device type=DISK
Starting Duplicate Db at 27-APR-20
contents of Memory Script:
{
backup as copy reuse
targetfile '+DATA/Findb/orapwfindb' auxiliary format
'/oracle/app/orawork/product/12.1.0.2/db_1/dbs/orapwFindr' ;
}
executing Memory Script
Starting backup at 27-APR-20
Finished backup at 27-APR-20
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
restore clone from service 'Findb' standby controlfile;
}
executing Memory Script
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 905971800 bytes
Database Buffers 218103808 bytes
Redo Buffers 9383936 bytes
allocated channel: CHNL3
channel CHNL3: SID=25 device type=DISK
allocated channel: CHNL4
channel CHNL4: SID=26 device type=DISK
Starting restore at 27-APR-20
channel CHNL3: starting datafile backup set restore
channel CHNL3: using network backup set from service Findb
channel CHNL3: restoring control file
channel CHNL3: restore complete, elapsed time: 00:00:03
output file name=+DATA/FINDR/CONTROLFILE/current.259.1038871347
output file name=+FRA/FINDR/CONTROLFILE/current.257.1038871347
Finished restore at 27-APR-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 clone tempfile 1 to new;
set newname for clone tempfile 2 to new;
set newname for clone tempfile 3 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
restore
from service 'Findb' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 27-APR-20
channel CHNL3: starting datafile backup set restore
channel CHNL3: using network backup set from service Findb
channel CHNL3: specifying datafile(s) to restore from backup set
channel CHNL3: restoring datafile 00001 to +DATA
channel CHNL4: starting datafile backup set restore
channel CHNL4: using network backup set from service Findb
channel CHNL4: specifying datafile(s) to restore from backup set
channel CHNL4: restoring datafile 00003 to +DATA
channel CHNL3: restore complete, elapsed time: 00:02:39
channel CHNL3: starting datafile backup set restore
channel CHNL3: using network backup set from service Findb
channel CHNL3: specifying datafile(s) to restore from backup set
channel CHNL3: restoring datafile 00004 to +DATA
channel CHNL4: restore complete, elapsed time: 00:02:41
channel CHNL4: starting datafile backup set restore
channel CHNL4: using network backup set from service Findb
channel CHNL4: specifying datafile(s) to restore from backup set
channel CHNL4: restoring datafile 00005 to +DATA
channel CHNL3: restore complete, elapsed time: 00:00:15
channel CHNL3: starting datafile backup set restore
channel CHNL3: using network backup set from service Findb
channel CHNL3: specifying datafile(s) to restore from backup set
channel CHNL3: restoring datafile 00006 to +DATA
channel CHNL3: restore complete, elapsed time: 00:00:15
channel CHNL3: starting datafile backup set restore
channel CHNL3: using network backup set from service Findb
channel CHNL3: specifying datafile(s) to restore from backup set
channel CHNL3: restoring datafile 00007 to +DATA
channel CHNL4: restore complete, elapsed time: 00:00:30
channel CHNL4: starting datafile backup set restore
channel CHNL4: using network backup set from service Findb
channel CHNL4: specifying datafile(s) to restore from backup set
channel CHNL4: restoring datafile 00008 to +DATA
channel CHNL4: restore complete, elapsed time: 00:00:07
channel CHNL4: starting datafile backup set restore
channel CHNL4: using network backup set from service Findb
channel CHNL4: specifying datafile(s) to restore from backup set
channel CHNL4: restoring datafile 00009 to +DATA
channel CHNL3: restore complete, elapsed time: 00:00:43
channel CHNL3: starting datafile backup set restore
channel CHNL3: using network backup set from service Findb
channel CHNL3: specifying datafile(s) to restore from backup set
channel CHNL3: restoring datafile 00010 to +DATA
channel CHNL4: restore complete, elapsed time: 00:00:36
channel CHNL4: starting datafile backup set restore
channel CHNL4: using network backup set from service Findb
channel CHNL4: specifying datafile(s) to restore from backup set
channel CHNL4: restoring datafile 00011 to +DATA
channel CHNL4: restore complete, elapsed time: 00:00:07
channel CHNL3: restore complete, elapsed time: 00:01:57
Finished restore at 27-APR-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=13 STAMP=1038871732 file name=+DATA/FINDR/DATAFILE/system.261.1038871359
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1038871732 file name=+DATA/FINDR/DATAFILE/sysaux.260.1038871359
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=1038871732 file name=+DATA/FINDR/DATAFILE/undotbs1.263.1038871527
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=1038871732 file name=+DATA/FINDR/A3E33EF1271C697EE0533C040A0A036A/DATAFILE/system.262.1038871525
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=1038871732 file name=+DATA/FINDR/DATAFILE/users.264.1038871541
datafile 7 switched to datafile copy
input datafile copy RECID=18 STAMP=1038871732 file name=+DATA/FINDR/A3E33EF1271C697EE0533C040A0A036A/DATAFILE/sysaux.265.1038871549
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=1038871732 file name=+DATA/FINDR/DATAFILE/undotbs2.266.1038871551
datafile 9 switched to datafile copy
input datafile copy RECID=20 STAMP=1038871732 file name=+DATA/FINDR/A3E36259DD3B7B62E0533C040A0A156B/DATAFILE/system.267.1038871559
datafile 10 switched to datafile copy
input datafile copy RECID=21 STAMP=1038871732 file name=+DATA/FINDR/A3E36259DD3B7B62E0533C040A0A156B/DATAFILE/sysaux.268.1038871595
datafile 11 switched to datafile copy
input datafile copy RECID=22 STAMP=1038871732 file name=+DATA/FINDR/A3E36259DD3B7B62E0533C040A0A156B/DATAFILE/users.269.1038871597
Finished Duplicate Db at 27-APR-20
released channel: CHNL1
released channel: CHNL2
released channel: CHNL3
released channel: CHNL4
*****************Logfile End********
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- --------------------
FINDB dm01dr01.database.com PHYSICAL STANDBY MOUNTED ARCHIVELOG 12.1.0.1.0 ALLOWED 27-APR-2020 23:22:18
Database will be in mount mode with pfile , now will swicth database to spfile with below steps :
[oracle@dm01dr01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Apr 27 23:42:24 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> create pfile from spfile;
File created.
-rw-r--r--. 1 oracle oinstall 8206 Apr 27 23:42 initFindr.ora
SQL> create spfile='+DATA' from pfile='initFindr.ora';
File created.
[oracle@dm01dr01 dbs]$ mv spfileFindr.ora spfileFindr.ora_old
[oracle@dm01dr01 dbs]$ mv initFindr.ora initFindr.ora_2704
Create pfile with entry of spfile
[oracle@dm01dr01 dbs]$ cat initFindr.ora
spfile='+DATA/FINDR/PARAMETERFILE/spfile.273.1041899893'
Now shutdown the Standby database
[oracle@dm01dr01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Apr 27 23:49:54 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@dm01dr01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Apr 27 23:51:44 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 905971800 bytes
Database Buffers 218103808 bytes
Redo Buffers 9383936 bytes
Database mounted.
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- --------------------
FINDB dm01dr01.database.com PHYSICAL STANDBY MOUNTED ARCHIVELOG 12.1.0.1.0 ALLOWED 27-APR-2020 23:51:51
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/FINDR/PARAMETERFILE/spfi
le.274.1038872679
SQL>
Add Database to Restart Configuration :
[oracle@dm01dr01 dbs]$ srvctl add database -d Findr -o /oracle/app/orawork/product/12.1.0.2/db_1 -m dm01dr01.database.com -n Findb -p +DATA/FINDR/PARAMETERFILE/spfile.274.1038872679 -s OPEN -r PHYSICAL_STANDBY -y automatic -a DATA,FRA
[oracle@dm01dr01 dbs]$ srvctl config database -d Findr
Database unique name: Findr
Database name: Findb
Oracle home: /oracle/app/orawork/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA/FINDR/PARAMETERFILE/spfile.274.1038872679
Password file:
Domain: dm01dr01.database.com
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: Findr
Disk Groups: DATA,FRA
Services:
Stop and restart the database with SRVCTL command
[oracle@dm01dr01 dbs]$ srvctl status database -d Findr
Database is not running.
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$ srvctl stop database -d Findr
PRCC-1016 : Findr was already stopped
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$ ps -ef|grep pmon
grid 2667 1 0 Apr27 ? 00:00:00 asm_pmon_+ASM
oracle 13445 1 0 Apr27 ? 00:00:00 ora_pmon_Findr
oracle 14295 13144 0 00:01 pts/0 00:00:00 grep --color=auto pmon
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 28 00:01:26 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- --------------------
FINDB dm01dr01.database.com PHYSICAL STANDBY MOUNTED ARCHIVELOG 12.1.0.1.0 ALLOWED 27-APR-2020 23:51:51
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$ srvctl start database -d Findr -o mount
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$ ps -ef |grep pmon
grid 3036 1 0 00:26 ? 00:00:00 asm_pmon_+ASM
oracle 6088 1 0 00:49 ? 00:00:00 ora_pmon_Findr
oracle 6200 3738 0 00:50 pts/0 00:00:00 grep --color=auto pmon
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$
[oracle@dm01dr01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon May 1 01:07:34 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- --------------------
FINDB dm01dr01.database.com PHYSICAL STANDBY MOUNTED ARCHIVELOG 12.1.0.1.0 ALLOWED 01-MAY-2020 01:08:59
Start the recovery :
Alter database recover managed standby database disconnect from session;
SQL> select process,status,sequence# from gv$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 73
RFS IDLE 58
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS IDLE 0
MRP0 WAIT_FOR_LOG 72
13 rows selected.
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 73 73 0
2 64 59 5
2 63 59 4
2 63 61 2
2 63 60 3
2 64 61 3
2 64 60 4
7 rows selected.
SQL> /
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 75 75 0
2 67 67 0
5 comments
Click here for commentsNice document sir
ReplyKindly post the switchover steps too.
ReplyFantastic post
ReplyNice post simple steps can be followed
ReplySuperb steps sir , really helped me a lot
ReplyConversionConversion EmoticonEmoticon