Sunday, September 3, 2023

Oracle Standby Database creation using rman active database duplicate

Oracle Standby Database Creation Procedure:

Primary DB UNIQNM: GGSRC04T
Standby DB UNIQNM: GGSRC04TSB1

Step 1) Ensure Primary db is using spfile & create password file if it doesnt exist already

cd $ORACLE_HOME/dbs
orapwd file=orapw${ORACLE_SID}

Actual output:

[oracle@vcentos79-oracle-ggsrc DGSetup]$ env|grep ORACLE
ORACLE_SID=GGSRC04T
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1


SQL> sho parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.2.0
                                                 .1/db_1/dbs/spfileGGSRC04T.ora
SQL> exit

[oracle@vcentos79-oracle-ggsrc DGSetup]$ ls -altr $ORACLE_HOME/dbs/orapw${ORACLE_SID}
-rw-r-----. 1 oracle oinstall 3584 Aug 24 19:53 /u01/app/oracle/product/12.2.0.1/db_1/dbs/orapwGGSRC04T
[oracle@vcentos79-oracle-ggsrc DGSetup]$

Step 2) Enable Force Logging in primary & archivelog mode

ALTER DATABASE FORCE LOGGING;
select name,open_mode,force_logging from v$database;

Actual output:

SQL>  select name,open_mode,force_logging,log_mode  from v$database;
NAME      OPEN_MODE            FORCE_LOGGING
--------- -------------------- ---------------------------------------
LOG_MODE
------------
GGSRC04T  READ WRITE           YES
ARCHIVELOG


Step 3) Add standby redologfiles to primary using the formula (g+1)*t , where g is numer of groups and t is threads

ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo06.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo07.log') SIZE 200M;

Actual output:

SQL> set lines 300
SQL> set pages 30000

SQL> select group# from v$log;
    GROUP#
----------
         1
         2
         3

SQL> select group# from v$standby_log;
no rows selected

SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/GGSRC04T/redo01.log
/oradata/GGSRC04T/redo02.log
/oradata/GGSRC04T/redo03.log

SQL> select bytes from v$log;
     BYTES
----------
 209715200
 209715200
 209715200


SQL> select count(1) from v$thread;
  COUNT(1)
----------
         1

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo04.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo05.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo06.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/GGSRC04T/sbyredo07.log') SIZE 200M;
Database altered.

SQL> select group# from v$standby_log;
    GROUP#
----------
         4
         5
         6
         7

SQL> select * from v$standby_log;
    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME     CON_ID
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ --------- ----------
         4 UNASSIGNED                                        0          0  209715200        512          0 YES UNASSIGNED 0
         5 UNASSIGNED                                        0          0  209715200        512          0 YES UNASSIGNED 0
         6 UNASSIGNED                                        0          0  209715200        512          0 YES UNASSIGNED 0
         7 UNASSIGNED                                        0          0  209715200        512          0 YES UNASSIGNED 0
SQL>

Step 4) Setup init params for the standby config in primary site

create pfile='/home/oracle/dba/DGSetup/pfileGGSRC04T_premod.ora' from spfile;
alter system set DB_UNIQUE_NAME=GGSRC04T scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(GGSRC04T,GGSRC04TSB1)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=GGSRC04T' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=GGSRC04TSB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=GGSRC04TSB1' scope=spfile;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=spfile;
alter system set FAL_SERVER=GGSRC04TSB1 scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
create pfile='/home/oracle/dba/DGSetup/pfileGGSRC04T_postmod.ora' from spfile;

Actual output:

SQL> create pfile='/home/oracle/dba/DGSetup/pfileGGSRC04T_premod.ora' from spfile;
File created.

SQL> sho parameter unique
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      GGSRC04T

SQL> alter system set DB_UNIQUE_NAME=GGSRC04T scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(GGSRC04T,GGSRC04TSB1)' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=GGSRC04T' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=GGSRC04TSB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=GGSRC04TSB1' scope=spfile;
System altered.
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=spfile;
System altered.
SQL> alter system set FAL_SERVER=GGSRC04TSB1 scope=spfile;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
System altered.
SQL> create pfile='/home/oracle/dba/DGSetup/pfileGGSRC04T_postmod.ora' from spfile;
File created.

SQL> !diff /home/oracle/dba/DGSetup/pfileGGSRC04T_premod.ora /home/oracle/dba/DGSetup/pfileGGSRC04T_postmod.ora
22a23
> *.db_unique_name='GGSRC04T'
25a27
> *.fal_server='GGSRC04TSB1'
27c29,32
< *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
---
> *.log_archive_config='DG_CONFIG=(GGSRC04T,GGSRC04TSB1)'
> *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=GGSRC04T'
> *.log_archive_dest_2='SERVICE=GGSRC04TSB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=GGSRC04TSB1'
> *.log_archive_format='log%t_%s_%r.arc'
35a41
> *.standby_file_management='AUTO'

Step 5) Restart the primary database

shu immediate;
alter database mount;
-- verify if archive mode is enabled. If not enable it using "alter database archivelog;"
alter database open;

Actual output:
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size            1442840896 bytes
Database Buffers         1761607680 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,force_logging,log_mode from v$database;
NAME      OPEN_MODE            FORCE_LOGGING                           LOG_MODE
--------- -------------------- --------------------------------------- ------------
GGSRC04T  READ WRITE           YES                                     ARCHIVELOG
SQL>

Step 6) Make /etc/oratab entry for standby db

GGSRC04TSB1:/u01/app/oracle/product/12.2.0.1/db_1:N

Actual output:

[oracle@vcentos79-oracle-ggsrc DGSetup]$ grep GGSRC04 /etc/oratab
GGSRC04T:/u01/app/oracle/product/12.2.0.1/db_1:N
GGSRC04TSB1:/u01/app/oracle/product/12.2.0.1/db_1:N
[oracle@vcentos79-oracle-ggsrc DGSetup]$

Step 7) Copy the primary password file and create standby password file from it
cp ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ${ORACLE_HOME}/dbs/orapw${ORACLE_SID}SB1

Actual output:

[oracle@vcentos79-oracle-ggsrc admin]$ ls -ld ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
ls: cannot access /u01/app/oracle/admin/GGSRC04TSB1/adump: No such file or directory
[oracle@vcentos79-oracle-ggsrc admin]$ mkdir -p ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
[oracle@vcentos79-oracle-ggsrc admin]$ ls -ld ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
drwxr-xr-x. 2 oracle oinstall 6 Sep  3 21:09 /u01/app/oracle/admin/GGSRC04TSB1/adump
[oracle@vcentos79-oracle-ggsrc admin]$ ls -ld /oradata/${ORACLE_SID}
drwxr-xr-x. 2 oracle oinstall 6 Sep  3 21:08 /oradata/GGSRC04TSB1
[oracle@vcentos79-oracle-ggsrc admin]$ ls -altr /oradata/${ORACLE_SID}
total 0
drwxr-xr-x. 6 oracle dba      73 Sep  3 21:08 ..
drwxr-xr-x. 2 oracle oinstall  6 Sep  3 21:08 .
[oracle@vcentos79-oracle-ggsrc admin]$

Step 8) Startup nomount the standby db with only 2 parameters

*.db_name=GGSRC04T
*.db_unique_name=GGSRC04TSB1

Actual output:

[oracle@vcentos79-oracle-ggsrc DGSetup]$ vi $ORACLE_HOME/dbs/initGGSRC04TSB1.ora
[oracle@vcentos79-oracle-ggsrc DGSetup]$ cat $ORACLE_HOME/dbs/initGGSRC04TSB1.ora
*.db_name=GGSRC04T
*.db_unique_name=GGSRC04TSB1
[oracle@vcentos79-oracle-ggsrc DGSetup]$
[oracle@vcentos79-oracle-ggsrc ~]$ env|grep ORACLE
ORACLE_SID=GGSRC04TSB1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@vcentos79-oracle-ggsrc ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 3 21:01:38 2023
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  243269632 bytes
Fixed Size                  8619256 bytes
Variable Size             180357896 bytes
Database Buffers           50331648 bytes
Redo Buffers                3960832 bytes
SQL> select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
GGSRC04TSB1      STARTED
SQL>

Step 9) create audit file directory in standby site & create controlfile directory in asm in standby

ls -ld ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
controlfile path: /oradata/${ORACLE_SID}/controlfile/

Actual output:

[oracle@vcentos79-oracle-ggsrc admin]$ ls -ld ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
ls: cannot access /u01/app/oracle/admin/GGSRC04TSB1/adump: No such file or directory
[oracle@vcentos79-oracle-ggsrc admin]$ mkdir -p ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
[oracle@vcentos79-oracle-ggsrc admin]$ ls -ld ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
drwxr-xr-x. 2 oracle oinstall 6 Sep  3 21:09 /u01/app/oracle/admin/GGSRC04TSB1/adump
[oracle@vcentos79-oracle-ggsrc admin]$
[oracle@vcentos79-oracle-ggsrc admin]$ ls -ld /oradata/${ORACLE_SID}
drwxr-xr-x. 2 oracle oinstall 6 Sep  3 21:08 /oradata/GGSRC04TSB1
[oracle@vcentos79-oracle-ggsrc admin]$ ls -altr /oradata/${ORACLE_SID}
total 0
drwxr-xr-x. 6 oracle dba      73 Sep  3 21:08 ..
drwxr-xr-x. 2 oracle oinstall  6 Sep  3 21:08 .
[oracle@vcentos79-oracle-ggsrc admin]$


Step 10) Add TNS entry for primary and standby (in my case primary and standby are in same server)

GGSRC04T =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vcentos79-oracle-ggsrc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = GGSRC04T)
    )
  )
GGSRC04TSB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vcentos79-oracle-ggsrc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = GGSRC04TSB1)
    )
  )

Actual output:

GGSRC04TSB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vcentos79-oracle-ggsrc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = GGSRC04TSB1)
    )
  )
LISTENER_GGSRC04TSB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = vcentos79-oracle-ggsrc)(PORT = 1521))

Step 11) Add static listener entry for primary and standby to facilitate active db duplication in both primary and standby site (in our case it is the same server)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = GGSRC04T)
     (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
     (SID_NAME = GGSRC04T)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = GGSRC04TSB1)
     (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
     (SID_NAME = GGSRC04TSB1)
    )
   )

Actual output:

[oracle@vcentos79-oracle-ggsrc admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vcentos79-oracle-ggsrc)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = GGSRC04T)
     (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
     (SID_NAME = GGSRC04T)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = GGSRC04TSB1)
     (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
     (SID_NAME = GGSRC04TSB1)
    )
   )
[oracle@vcentos79-oracle-ggsrc admin]$

Step 12)  Validate the db connection

sqlplus sys/pswd@<tnsentry> as sysdba-- primary
sqlplus sys/pswd@<tnsentry> as sysdba -- standby

Actual output:

[oracle@vcentos79-oracle-ggsrc DGSetup]$ sqlplus sys@GGSRC04T as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 3 21:06:26 2023
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> exit

[oracle@vcentos79-oracle-ggsrc DGSetup]$ sqlplus sys@GGSRC04TSB1 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 3 21:06:34 2023
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> exit
[oracle@vcentos79-oracle-ggsrc DGSetup]$

Step 13) Perform active database duplication to create standby. Frame the rman script

script: /home/oracle/dba/DGSetup/standbyGGSRC04TSB1.cmd
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
duplicate target database for standby from active database
USING COMPRESSED BACKUPSET
dorecover
spfile
  parameter_value_convert 'GGSRC04T','GGSRC04TSB1'
  set db_name='GGSRC04T'
  set db_unique_name='GGSRC04TSB1'
  set fal_server='GGSRC04TSB1'
  set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=GGSRC04TSB1'
  set log_archive_dest_2='service=GGSRC04T ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=GGSRC04T'
  set db_file_name_convert='/oradata/GGSRC04T','/oradata/GGSRC04TSB1'
  set log_file_name_convert='/oradata/GGSRC04T','/oradata/GGSRC04TSB1'
  reset local_listener
NOFILENAMECHECK;
}

Actual output:

[oracle@vcentos79-oracle-ggsrc DGSetup]$ ls -altr
total 24
drwxr-xr-x. 8 oracle oinstall 4096 Sep  3 20:33 ..
-rw-r--r--. 1 oracle oinstall  688 Sep  3 20:35 standbyGGSRC04TSB1.cmd


Step 14) Frame the shell script to invoke the rman script above & launch the script

export ORACLE_SID=GGSRC04TSB1;export ORAENV_ASK=NO;. oraenv >/dev/null 2>&1;
date
rman target sys/******@GGSRC04T auxiliary  sys/*******@GGSRC04TSB1 cmdfile=/home/oracle/dba/DGSetup/standbyGGSRC04TSB1.cmd log=/home/oracle/dba/DGSetup/standbyGGSRC04TSB1.log
date

Actual output:

[oracle@vcentos79-oracle-ggsrc DGSetup]$ nohup sh standbyGGSRC04TSB1.sh >nh_standbyGGSRC04TSB1.out &
[1] 26930

[oracle@vcentos79-oracle-ggsrc DGSetup]$ nohup: ignoring input and redirecting stderr to stdout

[oracle@vcentos79-oracle-ggsrc DGSetup]$ jobs -l
[1]+ 26930 Running                 nohup sh standbyGGSRC04TSB1.sh > nh_standbyGGSRC04TSB1.out &

[oracle@vcentos79-oracle-ggsrc DGSetup]$ cat nh_standbyGGSRC04TSB1.out
Sun Sep  3 21:19:19 BST 2023
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> [oracle@vcentos79-oracle-ggsrc DGSetup]$
[oracle@vcentos79-oracle-ggsrc DGSetup]$

[oracle@vcentos79-oracle-ggsrc DGSetup]$ jobs -l
[1]+ 26930 Running                 nohup sh standbyGGSRC04TSB1.sh > nh_standbyGGSRC04TSB1.out &
[oracle@vcentos79-oracle-ggsrc DGSetup]$

[oracle@vcentos79-oracle-ggsrc DGSetup]$ tail -10f standbyGGSRC04TSB1.log
Starting restore at 03-SEP-23
channel stby1: starting datafile backup set restore
channel stby1: using compressed network backup set from service GGSRC04T
channel stby1: specifying datafile(s) to restore from backup set
channel stby1: restoring datafile 00001 to /oradata/GGSRC04TSB1/system01.dbf
channel stby2: starting datafile backup set restore
channel stby2: using compressed network backup set from service GGSRC04T
channel stby2: specifying datafile(s) to restore from backup set
channel stby2: restoring datafile 00002 to /oradata/GGSRC04TSB1/sysaux01.dbf
^C
[oracle@vcentos79-oracle-ggsrc DGSetup]$

[oracle@vcentos79-oracle-ggsrc DGSetup]$ view standbyGGSRC04TSB1.log
[1]+  Done                    nohup sh standbyGGSRC04TSB1.sh > nh_standbyGGSRC04TSB1.out
[oracle@vcentos79-oracle-ggsrc DGSetup]$

Snippet of rman log:

Recovery Manager: Release 12.2.0.1.0 - Production on Sun Sep 3 21:19:19 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: GGSRC04T (DBID=4198404018)
connected to auxiliary database: GGSRC04T (not mounted)
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate auxiliary channel stby1 type disk;
5> allocate auxiliary channel stby2 type disk;
6> duplicate target database for standby from active database
7> USING COMPRESSED BACKUPSET
8> dorecover
9> spfile
10>   parameter_value_convert 'GGSRC04T','GGSRC04TSB1'
11>   set db_name='GGSRC04T'
12>   set db_unique_name='GGSRC04TSB1'
13>   set fal_server='GGSRC04TSB1'
14>   set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=GGSRC04TSB1'
...
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /oraarch/GGSRC04TSB1/archivelog/2023_09_03/o1_mf_1_19_lh9tohmh_.arc
archived log for thread 1 with sequence 20 is already on disk as file /oraarch/GGSRC04TSB1/archivelog/2023_09_03/o1_mf_1_20_lh9tohoc_.arc
archived log file name=/oraarch/GGSRC04TSB1/archivelog/2023_09_03/o1_mf_1_19_lh9tohmh_.arc thread=1 sequence=19
archived log file name=/oraarch/GGSRC04TSB1/archivelog/2023_09_03/o1_mf_1_20_lh9tohoc_.arc thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-SEP-23
Finished Duplicate Db at 03-SEP-23
released channel: prmy1
released channel: prmy2
released channel: stby1
released channel: stby2
Recovery Manager complete.

Step 15) Put the db in recovery mode

alter database recover automatic managed standby database disconnect from session;                                                                                                                 select database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
GGSRC04T  MOUNTED

SQL> select distinct(status) from v$datafile;
STATUS
-------
ONLINE
SYSTEM

SQL> select distinct(status) from v$datafile_header;
STATUS
-------
ONLINE
SQL>
SQL>
SQL> set lines 300
SQL> set pages 3000

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CLOSING      ARCH             21
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             22
8 rows selected.

SQL> alter database recover automatic managed standby database disconnect from session;
Database altered.

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CLOSING      ARCH             21
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             22
MRP0      APPLYING_LOG N/A              22
9 rows selected.

SQL> /
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CLOSING      ARCH             21
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             22
MRP0      APPLYING_LOG N/A              22
9 rows selected.

SQL> select name,open_mode,db_unique_name,log_mode,force_logging from v$Database;
NAME      OPEN_MODE            DB_UNIQUE_NAME                 LOG_MODE     FORCE_LOGGING
--------- -------------------- ------------------------------ ------------ ---------------------------------------
GGSRC04T  MOUNTED              GGSRC04TSB1                    ARCHIVELOG   YES

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME       CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0  209715200        512          1 YES UNUSED                  590545 03-SEP-23   1.8447E+19                      0
         3          1          0  209715200        512          1 YES UNUSED                  586943 03-SEP-23       590545 03-SEP-23            0
         2          1          0  209715200        512          1 YES UNUSED                  577599 03-SEP-23       586943 03-SEP-23            0

SQL> select status,max(sequence#) from v$archived_log group by status;
S MAX(SEQUENCE#)
- --------------
A             21

SQL> select status,applied,max(sequence#) from v$archived_log group by status,applied;
S APPLIED   MAX(SEQUENCE#)
- --------- --------------
A YES                   20
A IN-MEMORY             21 <<<< before arch switch in primary

SQL> /
S APPLIED   MAX(SEQUENCE#)
- --------- --------------
A YES                   21
A IN-MEMORY             22 <<<< after arch switch in primary

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
DGRD      ALLOCATED    N/A               0
ARCH      CLOSING      ARCH             22
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CLOSING      ARCH             21
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             23
MRP0      APPLYING_LOG N/A              23
9 rows selected.
SQL> 

Step 16) A quick validation of the pfile in standby

[oracle@vcentos79-oracle-ggsrc DGSetup]$ grep GGSRC /home/oracle/dba/DGSetup/pfileGGSRC04TSB1.ora
*.audit_file_dest='/u01/app/oracle/admin/GGSRC04TSB1/adump'
*.control_files='/oradata/GGSRC04TSB1/control01.ctl','/oradata/GGSRC04TSB1/control02.ctl'#Restore Controlfile
*.db_file_name_convert='/oradata/GGSRC04T','/oradata/GGSRC04TSB1'
*.db_name='GGSRC04T'
*.db_unique_name='GGSRC04TSB1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=GGSRC04TSB1XDB)'
*.fal_server='GGSRC04TSB1' <<< CONCERNING, since we used param conver GGSRC04T became GGSRC04TSB1, but the otherway around didnt happen
*.log_archive_config='DG_CONFIG=(GGSRC04T,GGSRC04TSB1)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=GGSRC04TSB1'
*.log_archive_dest_2='service=GGSRC04T ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=GGSRC04T'
*.log_file_name_convert='/oradata/GGSRC04T','/oradata/GGSRC04TSB1'
[oracle@vcentos79-oracle-ggsrc DGSetup]$

Step 17) Fix FAL_SERVER @ standby

SQL>
SQL> sho parameter fal_Server
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string      GGSRC04TSB1
SQL> alter system set fal_server='GGSRC04T' scope=spfile;
System altered.
SQL> alter system set fal_server='GGSRC04T' scope=both;
System altered.
SQL> sho parameter fal_se
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string      GGSRC04T

YouTube Video:

Thank you!

at
Labels: , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Flashback data archive steps

 Objective: Ways to track DML changes in a table Detailed objective: We should be able to track the changes in the table at any point in tim...