Oracle dataguard recreate baseline

1. check info on both pr/dr

*** NOTE：pr: primary db,dr: standby db

1.1 os check use root user

su - root
df -hT
df -i
ifconfig -a
cat /etc/hosts
ps -ef|grep smon
复制

1.2 crs check use grid user

su - grid
crsctl stat res -t
asmcmd lsdg
cat $ORACLE_HOME/network/admin/sqlnet.ora
cat $ORACLE_HOME/network/admin/listener.ora
cat $ORACLE_HOME/network/admin/tnsnames.ora
sqlplus / as sysasm
show parameter spfile
show parameter shared_pool
show parameter memory_max_target
show parameter memory_target
show parameter disk
exit
复制

1.3 oracle check user oracle user

su - oracle
cat $ORACLE_HOME/network/admin/sqlnet.ora
cat $ORACLE_HOME/network/admin/listener.ora
cat $ORACLE_HOME/network/admin/tnsnames.ora

su - oracle
sqlplus / as sysdba
archive log list;
show parameter spfile;
set linesize 600 pagesize 2000
col ERROR for a20
col NAME for a60
col b_scn for a20
col s_scn for a20
col r_scn for a20
col d_scn for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select to_char(CHECKPOINT_CHANGE#) c_scn from v$database;
select FILE#,name,to_char(CHECKPOINT_CHANGE#) b_scn,CHECKPOINT_TIME,to_char(LAST_CHANGE#) s_scn,LAST_TIME from v$datafile;
select FILE#,name,bytes/1024/1024/1024 size_g, STATUS,TS#,RFILE#,to_char(RESETLOGS_CHANGE#) r_scn,RESETLOGS_TIME,
to_char(CHECKPOINT_CHANGE#) d_scn,CHECKPOINT_TIME,CHECKPOINT_COUNT,ERROR,RECOVER,FUZZY from v$datafile_header;

col member for a60
select * from v$log;
select * from v$standby_log;
select * from v$logfile;

col open_mode for a20
col database_role for a20
select name,open_mode,database_role,switchover_status from v$database;
select instance_number,instance_name,status,startup_time from gv$instance;
show parameter memory_max_target
show parameter memory_target
show parameter sga
select process,status,thread#,sequence#,block#,blocks from v$managed_standby where process in ('LNS','LGWR','RFS','MRP0');
show parameter db_name
show parameter db_unique_name;
show parameter log_archive_config;
show parameter log_archive_dest_1;
show parameter log_archive_dest_state_1;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter fal_server;
show parameter fal_client;
show parameter standby_file_management;
show parameter remote_login_passwordfile;
show parameter db_file_name_convert;
show parameter log_file_name_convert;
复制

1.4 listener check

ps -ef|grep tns|grep -v grep   <-- record listener <owner> and <listener_name>
su - <owner>
lsnrctl status <listener_name>
复制

1.5 check db connection

su - oracle
sqlplus sys/****@dr_orclbak as sysdba <-- change the *** to right password of sys on dr
exit
sqlplus sys/****@pr_orcl as sysdba <-- change the *** to right password of sys on pr
exit
复制

2. prepare before duplicates

2.2 stop other instance in rac cluster except the node1

su - oracle
sqlplus / as sysdba
shutdown immediate;
exits;
复制

3. delete all history file but spfile on dr side

3.1 stop db on node1

su - oracle
sqlplus / as sysdba
create pfile='/tmp/pfile_20221208.ora' from spfile;   <-- backup spfile before any change operation
shutdown immediate
exits;
复制

3.2 delete all history file but spfile on dr side

su - grid
asmcmd
lsdg
#### note: delete archivelog/controlfile/datafile/onlinelog/tempfile on each diskgroup_name attact to db_unique_name
rm -rf +<diskgroup_name>/<db_unique_name>/ARCHIVELOG
rm -rf +<diskgroup_name>/<db_unique_name>/CONTROLFILE
rm -rf +<diskgroup_name>/<db_unique_name>/DATAFILE
rm -rf +<diskgroup_name>/<db_unique_name>/ONLINELOG
rm -rf +<diskgroup_name>/<db_unique_name>/TEMPFILE
复制

3.3 startup nomount db of node1 on dr side

su - oracle
sqlplus / as sysdba
startup nomount
exit
复制

3.3 config duplicate script file on dr side

cd /tmp
vi gen_stb.rcv
run{
allocate channel prmy1 type disk;
allocate auxiliary channel stby1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby2 type disk;
allocate channel prmy3 type disk;
allocate auxiliary channel stby3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby5 type disk;
allocate channel prmy6 type disk;
allocate auxiliary channel stby6 type disk;
allocate channel prmy7 type disk;
allocate auxiliary channel stby7 type disk;
allocate channel prmy8 type disk;
allocate auxiliary channel stby8 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck;
}

chown -R oracle:oinstall gen_stb.rcv
chmod +x gen_stb.rcv

nohup rman target sys/****@pr_orcl auxiliary sys/****@dr_orclbak cmdfile=/tmp/gen_stb.rcv msglog=/tmp/gen_stb_20221208.log &

tail -100f gen_stb_*.log
复制

4. check db sync on pr

su - oracle
sqlplus / as sysdba
archive log list;
alter system archive log current;
alter system archive log current;
alter system archive log current;
archive log list;

set linesize 200 pagesize 9999
select process,status,thread#,sequence#,block#,blocks from v$managed_standby where process in ('LNS','LGWR','RFS','MRP0');
复制

5. check db sync on dr

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

#### wait 5 minutes: the archivelog sync from pr to dr.

#### note: if MRP0.SEQUENCE# on dr is greater than LSN.SEQUENCE# on pr,and MRP0.status: APPLYING_LOG,the db sync is ok 
set linesize 200 pagesize 9999
select process,status,thread#,sequence#,block#,blocks from v$managed_standby where process in ('LNS','LGWR','RFS','MRP0');
复制

6. start other process

6.1 startup other instance in rac cluster except the node1

su - oracle
sqlplus / as sysdba
startup
exit
复制

7. issue fix

7.1 controfile duplicate error

RMAN-03002: failure of Duplicate Db command at 12/09/2022 00:03:26
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on prmy1 channel at 12/09/2022 00:03:24
ORA-17628: Oracle error 19505 returned by remote Oracle server

solve plan：
*** note: do not set the path to DATAFILE/ONLINELOG,just the db_unique_name is ok
alter system set DB_FILE_NAME_CONVERT=’+DATA01/orcl/’,’+DATA01/orclbak/’ scope=spfile;
alter system set LOG_FILE_NAME_CONVERT=’+DATA01/orcl/’,’+DATA01/orclbak/’ scope=spfile;

7.2 check static listener

*** note: listener.ora in both oracle and grid
*** grid user: cat $ORACLE_HOME/network/admin/listener.ora
*** oracle user: cat $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclbak) <-- $ORACLE_SID of oracle user (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) <--$ORACLE_HOME of oracle user
(SID_NAME = orclbak) <-- $ORACLE_SID of oracle user
)
)

*** note: grid/oracle 's listener.ora always use orace to check need according to Oracle env parameter
su - oracle
echo $ORACLE_SID
echo $ORACLE_HOME

7.3 mrp wait_for_log on dr

*** pls,wait 5 minute,check again.
SQL> SQL> SQL> SQL> set linesize 200 pagesize 9999
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby where process in (‘LNS’,‘LGWR’,‘RFS’,‘MRP0’);

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

MRP0 WAIT_FOR_LOG 1 67 0 0

8. check list

8.1 standby log check on pr

standby_log and redo log need the same size and more than one;
复制

8.2 parameter check on both pr and dr

show parameter db_name
show parameter db_unique_name;
show parameter log_archive_config;
show parameter log_archive_dest_1;
show parameter log_archive_dest_state_1;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter fal_server;
show parameter fal_client;
show parameter standby_file_management;    <-- AUTO
show parameter remote_login_passwordfile;  <-- EXCLUSIVE
show parameter db_file_name_convert;       <-- just point to db_unique_name
show parameter log_file_name_convert;      <-- just point to db_unique_name
复制

8.3 check connect alias in tnsnames.ora on both pr/dr

8.4 check static listener config

according to <7.2 check static listener>
复制

8.5 check tnsnames.ora config

pr_orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.115)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

dr_orclbak =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclbak)
      (UR=A)                       <-- add this line on dr alias,otherwise cant connect to the dr
    )
  )
复制

8.6 check duplicate command

nohup rman target sys/****@pr_orcl auxiliary sys/****@dr_orclbak cmdfile=/tmp/gen_stb.rcv msglog=/tmp/gen_stb_20221208.log &

duplicate target database for standby from active database dorecover nofilenamecheck;


note:
pr_orcl: is alias point to pr db;
dr_orclbak: is alias point to dr db;
do recover: By default, the duplicate command does not use the archive to recover the created standby database data files. 
Unless the DORECOVER parameter is specified when running duplicate, then duplicate transmits and applies all available redologs. 
If the DORECOVER parameter is not added during duplicate, rman only restores control files and data files, and does not contain archive logs. 
The archive will not be transferred from the primary to the standby until the standby is created.
复制