DBA Hub

📋Steps in this guide1/1

How to convert Snapshot Standby Database to Physical Standby Database-Oracle Dataguard

In this blog, We will see how to convert physical standby database to snapshot standby database.

oracle configurationintermediate
by OracleDba
14 views
1

Overview

How to convert Snapshot Standby Database to Physical Standby Database-Oracle Dataguard In this blog, We will see how to convert physical standby database to snapshot standby database. On that, we can do all types of testing or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

1 row selected.

SQL> select open_mode from v$database;

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

SQL> alter database recover managed standby database cancel;

Database altered.

1 row selected.

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

Total System Global Area  335540560 bytes
Fixed Size                  9134416 bytes
Variable Size             272629760 bytes
Database Buffers           50331648 bytes
Redo Buffers                3444736 bytes
Database mounted.
SQL>
SQL>

SQL> show parameter recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/app/oracle/fast_recovery
                                                 _area
db_recovery_file_dest_size           big integer 4800M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string


If recovery area is not set then , We can configure like this

SQL> alter system set db_recovery_file_dest_size=4g;

System altered.

SQL> alter system set db_recovery_file_dest='/data/testdb/recover';

System altered.

SYS@TESTER1 SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /data/testdb/recover
db_recovery_file_dest_size big integer 4G


SQL>  alter database flashback on;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL>
SQL>
SQL> alter database open;

Database altered.

SQL>
SQL>
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRIM      READ WRITE

SQL>
SQL>
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

NAME
--------------------------------------------------------------------------------
GUA
---
SNAPSHOT_STANDBY_REQUIRED_08/24/2021 09:53:14
YES

Shutdown database to enable flashback

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

Total System Global Area  335540560 bytes
Fixed Size                  9134416 bytes
Variable Size             272629760 bytes
Database Buffers           50331648 bytes
Redo Buffers                3444736 bytes
Database mounted.
SQL>
SQL>
SQL> alter database convert to physical standby;

Database altered.

SQL> alter database open;

Database altered.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!