Showing posts with label dba tutorial. Show all posts
Showing posts with label dba tutorial. Show all posts

Monday, September 4, 2023

Procedure to convert a snapshot standby back to physical standby in oracle

Procedure to convert a snapshot standby back to physical standby in oracle

Step 1) Verify the status of the db

set lines 300
set pages 3000
select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;

Actual output:

SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------------------------ --------- --------------------
GGSRC04T  READ WRITE           SNAPSHOT STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;

PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CLOSING      ARCH             23
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
LNS       CONNECTED    LNS               0
DGRD      ALLOCATED    N/A               0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             25
RFS       IDLE         UNKNOWN           0
RFS       IDLE         UNKNOWN           0
12 rows selected.

SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;

       SCN GUA NAME
---------- --- --------------------------------------------------------------------------------------------------------------------------------
    600556 YES SNAPSHOT_STANDBY_REQUIRED_09/04/2023 11:05:49

SQL> col name for a50

SQL> 1
  1* select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT
SQL> /
       SCN GUA NAME
---------- --- --------------------------------------------------
    600556 YES SNAPSHOT_STANDBY_REQUIRED_09/04/2023 11:05:49
SQL>

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                          1.46                      1.06               6          0
BACKUP PIECE                           .99                         0               2          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                        19.53                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0
8 rows selected.
SQL>

Step 2) Ensure the DB is in mount state

shu immediate;
startup mount;
set lines 300
set pages 3000
select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
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.

SQL>
SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

NAME       OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME       SWITCHOVER_STATUS
---------- -------------------- ---------------- ------------------------------ ---------- --------------------
GGSRC04T   MOUNTED              SNAPSHOT STANDBY GGSRC04TSB1                    GGSRC04T   NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;

PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CLOSING      ARCH             25
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             26

8 rows selected.
SQL>

Step 3) Convert the db back to physical standby using the below command

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Actual output:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

NAME       OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME       SWITCHOVER_STATUS
---------- -------------------- ---------------- ------------------------------ ---------- --------------------
GGSRC04T   MOUNTED              PHYSICAL STANDBY GGSRC04TSB1                    GGSRC04T   RECOVERY NEEDED
SQL>

Step 4) Start the DB back again to mount/read only based on your active dataguard license availability

shu immediate;
startup mount;
set lines 300
set pages 3000
select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

SQL> shu immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup mount
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.

SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

NAME       OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME       SWITCHOVER_STATUS
---------- -------------------- ---------------- ------------------------------ ---------- --------------------
GGSRC04T   MOUNTED              PHYSICAL STANDBY GGSRC04TSB1                    GGSRC04T   RECOVERY NEEDED

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      CLOSING      ARCH             27
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             28

8 rows selected.
SQL>

Step 5) Verify recovery state

set lines 300
set pages 3000
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

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      CLOSING      ARCH             27
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             28

8 rows selected.
SQL>

Step 6) If the db isnt in recovery mode, put the db in recovery mode

alter database recover automatic managed standby database disconnect from session;
set lines 300
set pages 3000
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

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      CLOSING      ARCH             27
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             28
MRP0      APPLYING_LOG N/A              28

9 rows selected.

SQL> select status,applied,max(sequence#) from v$archived_log group by status,applied;

S APPLIED   MAX(SEQUENCE#)
- --------- --------------
A YES                   26
A IN-MEMORY             27

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      CLOSING      ARCH             27
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             28
MRP0      APPLYING_LOG N/A              28

9 rows selected.

SQL> /

PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
DGRD      ALLOCATED    N/A               0
ARCH      CLOSING      ARCH             28
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CLOSING      ARCH             27
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             29
MRP0      APPLYING_LOG N/A              29

9 rows selected.
SQL>

Step 7) Perform a log switch in primary and check in standby if the recovery is all good. [only step u need primary]

--primary:
alter system switch logfile;

--standby:
set lines 300
set pages 3000
select process,status,client_process,sequence# from gv$managed_standby;
Actual output:

--primary:

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         28  209715200        512          1 NO  CURRENT                 673153 04-SEP-23   1.8447E+19                      0
         2          1         26  209715200        512          1 YES INACTIVE                672879 04-SEP-23       672965 04-SEP-23            0
         3          1         27  209715200        512          1 YES ACTIVE                  672965 04-SEP-23       673153 04-SEP-23            0

SQL> alter system switch logfile;

System altered.

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         28  209715200        512          1 YES ACTIVE                  673153 04-SEP-23       673449 04-SEP-23            0
         2          1         29  209715200        512          1 NO  CURRENT                 673449 04-SEP-23   1.8447E+19                      0
         3          1         27  209715200        512          1 YES ACTIVE                  672965 04-SEP-23       673153 04-SEP-23            0

SQL>

-- standby

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      CLOSING      ARCH             27
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             28
MRP0      APPLYING_LOG N/A              28

9 rows selected.

SQL> /

PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
DGRD      ALLOCATED    N/A               0
ARCH      CLOSING      ARCH             28
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CLOSING      ARCH             27
ARCH      CONNECTED    ARCH              0
RFS       IDLE         Archival          0
RFS       IDLE         LGWR             29
MRP0      APPLYING_LOG N/A              29

9 rows selected.

SQL>

Step 8) Check the status of the restore point that snapshot standby command created.

set lines 300
set pages 3000
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;

Actual output:

SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
no rows selected
SQL>
--------

Foot Notes:

select parameter,value from v$option where upper(parameter) like '%VAULT%';

select * from v$encryption_wallet;



Actual output:

-- primary:

SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault                                            TRUE
SQL>

-- standby:
SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault                                            FALSE

SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
------------------------------ -------------------- --------- --------- ----------
FILE
/u01/app/oracle/admin/GGSRC04TSB1/wallet/
OPEN                           AUTOLOGIN            SINGLE    NO                 0

----------------------looks like the vault needs to be enabled back using snapshot standby :)

YouTube Video:

Thanks


at No comments:
Labels: , , , , , , , , , ,

Procedure to take a cold backup of snapshot standby database in oracle

Procedure to take a cold backup of snapshot standby database in oracle

 

Caution: Ensure you have TDE taken care.

 Step 1) Verify if the db is in snapshot standby mode

 

set lines 300
set pages 3000
select name,open_mode,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,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------------------------ --------- --------------------
GGSRC04T  MOUNTED              SNAPSHOT STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0

6 rows selected.
 

 

Step 2) Verify the db is in intended state (DV - OFF, TDE - ON)

 

select parameter,value from v$option where upper(parameter) like '%VAULT%';
select * from v$encryption_wallet;
 
Actual output:

SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';


PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault                                            FALSE

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
------------------------------ -------------------- --------- --------- ----------
FILE
/u01/app/oracle/admin/GGSRC04TSB1/wallet/
OPEN                           AUTOLOGIN            SINGLE    NO                 0

 

Step 3) Ensure the db is in mount state

 

Actual output:

Already verified


Step 4) Perform the rman cold backup

 

mkdir -p /oraarch/GGSRC04TSB1/SnapshotBackup/

ls -ld /oraarch/GGSRC04TSB1/SnapshotBackup/

 

run

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

allocate channel c4 device type disk;

backup as compressed backupset database format '/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_%d_%U';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

 

Actual Output:

 

[oracle@vcentos79-oracle-ggsrc wallet]$ rman target /
 
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Sep 4 11:46:27 2023
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: GGSRC04T (DBID=4198404018, not open)
 
RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
2> 3> 4> 5> 6> 7> backup as compressed backupset database format '/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_%d_%U';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}8> 9> 10> 11> 12>
 
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=44 device type=DISK
 
allocated channel: c2
channel c2: SID=46 device type=DISK
 
allocated channel: c3
channel c3: SID=43 device type=DISK
 
allocated channel: c4
channel c4: SID=37 device type=DISK
 
Starting backup at 04-SEP-23
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/GGSRC04TSB1/system01.dbf
input datafile file number=00005 name=/oradata/GGSRC04TSB1/encrypt_ts1_01.dbf
channel c1: starting piece 1 at 04-SEP-23
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/oradata/GGSRC04TSB1/sysaux01.dbf
input datafile file number=00004 name=/oradata/GGSRC04TSB1/users01.dbf
channel c2: starting piece 1 at 04-SEP-23
channel c3: starting compressed full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/GGSRC04TSB1/undotbs01.dbf
input datafile file number=00006 name=/oradata/GGSRC04TSB1/gg_data_01.dbf
channel c3: starting piece 1 at 04-SEP-23
channel c3: finished piece 1 at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3m25h60r_1_1 tag=TAG20230904T114634 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3k25h60r_1_1 tag=TAG20230904T114634 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
channel c2: finished piece 1 at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3l25h60r_1_1 tag=TAG20230904T114634 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:25
Finished backup at 04-SEP-23
 
Starting Control File and SPFILE Autobackup at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/autobackup/2023_09_04/o1_mf_s_1146655442_lhcff4yp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-SEP-23
 
released channel: c1
 
released channel: c2
 
released channel: c3
 
released channel: c4
 
RMAN> exit
 
 
Recovery Manager complete.

[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr /oraarch/GGSRC04TSB1/*/
/oraarch/GGSRC04TSB1/archivelog/:
total 0
drwxr-x---. 2 oracle oinstall 134 Sep  3 21:29 2023_09_03
drwxr-x---. 2 oracle oinstall   6 Sep  4 08:00 2023_09_04
drwxr-x---. 4 oracle oinstall  42 Sep  4 08:00 .
drwxr-x---. 6 oracle oinstall  81 Sep  4 11:39 ..
 
/oraarch/GGSRC04TSB1/flashback/:
total 409616
drwxr-x---. 2 oracle oinstall        60 Sep  4 11:05 .
-rw-r-----. 1 oracle oinstall 209723392 Sep  4 11:05 o1_mf_lhcc019o_.flb
-rw-r-----. 1 oracle oinstall 209723392 Sep  4 11:26 o1_mf_lhcbzy0r_.flb
drwxr-x---. 6 oracle oinstall        81 Sep  4 11:39 ..
 
/oraarch/GGSRC04TSB1/autobackup/:
total 0
drwxr-x---. 3 oracle oinstall 24 Sep  4 11:14 .
drwxr-x---. 6 oracle oinstall 81 Sep  4 11:39 ..
drwxr-x---. 2 oracle oinstall 86 Sep  4 11:47 2023_09_04
 
/oraarch/GGSRC04TSB1/SnapshotBackup/:
total 186392
drwxr-x---. 6 oracle oinstall       81 Sep  4 11:39 ..
-rw-r-----. 1 oracle oinstall  4341760 Sep  4 11:39 rman_bkp_GGSRC04T_3j25h5j3_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep  4 11:39 rman_bkp_GGSRC04T_3i25h5j2_1_1
drwxr-xr-x. 2 oracle oinstall      196 Sep  4 11:46 .
-rw-r-----. 1 oracle oinstall  4341760 Sep  4 11:46 rman_bkp_GGSRC04T_3m25h60r_1_1
-rw-r-----. 1 oracle oinstall 62070784 Sep  4 11:46 rman_bkp_GGSRC04T_3k25h60r_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep  4 11:46 rman_bkp_GGSRC04T_3l25h60r_1_1
[oracle@vcentos79-oracle-ggsrc wallet]$

 

YouTube Video:

 


Thanks


at No comments:
Labels: , , , , , , , , , , , , , ,

Procedure to convert physical standby to snapshot standby in oracle

Procedure to convert physical standby db to snapshot standby

Reference: https://docs.oracle.com/cd/E11882_01/server.112/e41134/manage_ps.htm#SBYDB00708

Step 1) Verify if the DB recovery is active

set lines 300
set pages 3000
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 database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
---------------- ------------------------------ --------- --------------------
PHYSICAL STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
MRP0      APPLYING_LOG N/A              23
7 rows selected.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Step 2) Stop the redo apply

alter database recover managed standby database cancel;
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
6 rows selected.

Step 3) Restart the db in mount state

shu immediate;
startup mount;
Actual output:

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
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.

SQL> select database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
---------------- ------------------------------ --------- --------------------
PHYSICAL STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
6 rows selected.

Step 4) Verify if FRA is configured, it is mandatory for the guranteed restore point which the database will create as part of snapshot standby

sho parameter db_reco
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Actual output:

SQL> sho parameter db_reco
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oraarch
db_recovery_file_dest_size           big integer 1G

SQL> alter system set db_recovery_file_dest_size=2G;
System altered.

SQL> sho parameter db_reco
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oraarch
db_recovery_file_dest_size           big integer 2G

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.

SQL> select database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
---------------- ------------------------------ --------- --------------------
SNAPSHOT STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

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

SQL> alter database open;
Database altered.

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
GGSRC04T  READ WRITE <<< SNAPSHOT standby 
allowed db to open in RW mode vs Physical standby allows db to open in RO mode as per their nature

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
LNS       CONNECTED    LNS               0
DGRD      ALLOCATED    N/A               0
8 rows selected.

SQL> select * from v$restore_point;
       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                                                        RESTORE_POINT_TIME                                                    PRE
---------- --------------------- --- ------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---
NAME                                                                                                                      PDB CLE PDB_INCARNATION#     CON_ID
-------------------------------------------------------------------------------------------------------------------------------- --- --- ---------------- ----------
    600556                     1 YES    209715200 04-SEP-23 11.05.49.000000000 AM                                          YES
SNAPSHOT_STANDBY_REQUIRED_09/04/2023 11:05:49                                                                             NO  NO          0          0

SQL> select * from V$FLASHFILESTAT;
no rows selected

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                          1.06                         0               4          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                        19.53                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0
8 rows selected.
SQL>

YouTube link:



Thanks
at No comments:
Labels: , , , , , , , , , , , ,
Subscribe to: Posts (Atom)

OKV platform certificate rotation - pitfall , awareness!!!!

OKV version: 21.9 Setup: Multimaster R/W cluster Plan: https://docs.google.com/spreadsheets/d/e/2PACX-1vSaXXTjj9cE1fvYpNmsDNBOkTIw78yTwQ6a9o...