DBA Hub

📋Steps in this guide1/4

Refresh Standby Database using RMAN Incremental SCN Backup

Action Plan

oracle configurationintermediate
by OracleDba
14 views
1

Overview

Action Plan

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
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
    425

[oracle@node1 DELL]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration;
Configuration - DELL
Protection Mode: MaxPerformance
Databases:
DELL    - Primary database
Error: ORA-16724: cannot resolve gap for one or more standby databases
DELL_DG - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
367

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

SQL>

SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER <----

SQL>
2

Section 2

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
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   1430085

[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 24 04:17:33 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DELL (DBID=3965088591)
RMAN>
RMAN> run
2> {
3> allocate channel t1 type disk;
4> allocate channel t2 type disk;
5> allocate channel t3 type disk;
6> backup incremental from SCN 1430085 database tag='STANDBY_TODAY_REFRESH' format '/u02/oracle/backup/DELL_BACKUP/database_%d_%u_%s';
7> release channel t1;
8> release channel t2;
9> release channel t3;
10> }

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=52 device type=DISK

allocated channel: t2
channel t2: SID=46 device type=DISK

allocated channel: t3
channel t3: SID=42 device type=DISK

Starting backup at 24-JAN-19
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/DELL/system01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/DELL/users01.dbf
channel t1: starting piece 1 at 24-JAN-19
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u02/oracle/oradata/DELL/sysaux01.dbf
input datafile file number=00003 name=/u02/oracle/oradata/DELL/undotbs01.dbf
channel t2: starting piece 1 at 24-JAN-19
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current control file in backup set
channel t3: starting piece 1 at 24-JAN-19
channel t3: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0vto3o79_31 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0tto3o79_29 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t1: backup set complete, elapsed time: 00:00:08
channel t2: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0uto3o79_30 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t2: backup set complete, elapsed time: 00:00:08
Finished backup at 24-JAN-19

released channel: t1

released channel: t2

released channel: t3

RMAN>

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY <-------

SQL>

SQL> alter database create standby controlfile as '/u02/oracle/backup/DELL_BACKUP/std_control.ctl';

Database alter

[oracle@node1 DELL_BACKUP]$ scp * oracle@node2:/u02/oracle/backup/DELL_DG
oracle@node2's password:
database_DELL_0tto3o79_29                        100%  232KB 232.0KB/s   00:00
database_DELL_0uto3o79_30                        100%  736KB 736.0KB/s   00:00
database_DELL_0vto3o79_31                        100%   10MB  10.0MB/s   00:00
std_control.ctl                                  100%   10MB   9.9MB/s   00:00
[oracle@node1 DELL_BACKUP]$

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u02/oracle/oradata/DELL_DG/co
                                                 ntrol01.ctl, /u02/oracle/orada
                                                 ta/fast_recovery_area/DELL_DG/
                                                 control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>
3

Section 3

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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

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


Database dismounted.
ORACLE instance shut down.
SQL>

oracle@node2 DELL_DG]$ cp std_control.ctl /u02/oracle/oradata/DELL_DG/control01.ctl
[oracle@nide2 DELL_DG]$ cp std_control.ctl /u02/oracle/oradata/fast_recovery_area/DELL_DG/control02.ctl

[oracle@node2 DELL_DG]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 04:28:58 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
Database mounted

[oracle@node2 DELL_DG]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 24 04:29:48 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DELL (DBID=3965088591, not open)

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29';

Starting implicit crosscheck backup at 24-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 24-JAN-19

Starting implicit crosscheck copy at 24-JAN-19
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 24-JAN-19

searching for all files in the recovery area
cataloging files...
no files cataloged

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29 RECID=16 STAMP=998368237

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30';

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30 RECID=17 STAMP=998368262

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31';

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31 RECID=18 STAMP=998368289

RMAN> list backup tag STANDBY_TODAY_REFRESH;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16      Incr    224.00K    DISK        00:00:00     24-JAN-19
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Incr 1431069    24-JAN-19 /u02/oracle/oradata/DELL_DG/system01.dbf
  4       Incr 1431069    24-JAN-19 /u02/oracle/oradata/DELL_DG/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Incr    728.00K    DISK        00:00:00     24-JAN-19
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Incr 1431070    24-JAN-19 /u02/oracle/oradata/DELL_DG/sysaux01.dbf
  3       Incr 1431070    24-JAN-19 /u02/oracle/oradata/DELL_DG/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Incr    9.95M      DISK        00:00:00     24-JAN-19
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31
  Control File Included: Ckp SCN: 1431069      Ckp time: 24-JAN-19

RMAN> exit


Recovery Manager complete.
[oracle@node2 DELL_DG]$

12. Cancel MRP

[oracle@node2 DELL_DG]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 04:32:49 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>  recover managed standby database cancel;
Media recovery complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

[oracle@node2 DELL_DG]$

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY <-------

SQL>

SQL> alter database create standby controlfile as '/u02/oracle/backup/DELL_BACKUP/std_control.ctl';

Database alter

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;

System altered.

SQL>

[oracle@node1 DELL_BACKUP]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> EDIT DATABASE "DELL" SET STATE='TRANSPORT-ON';
Succeeded.
DGMGRL>
4

Section 4

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
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/DELL
Oldest online log sequence     429
Next log sequence to archive   431
Current log sequence           431
SQL>

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
           430 <------

SQL>

DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database "DELL_DG"

Database - DELL_DG

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    DELL_DG

Database Status:
SUCCESS

DGMGRL>

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/DELL_DG
Oldest online log sequence     429
Next log sequence to archive   0
Current log sequence           431
SQL> 

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
       430

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!