Oracle Data Guard 主备切换演练

denglj · 发表于 2021-11-24 09:14:17

1、主备库介绍

登录/注册后可看大图

51105619d905c53bbe.png (47.27 KB, 下载次数: 282)

下载附件

2021-11-24 09:07 上传

2、failover演练

注意：failover切换后原主库将不可用，必须重新搭建

2.1、主备库确认

主库查询：

SQL>select name,log_mode,open_mode,database_role,switchover_status,db_unique_namefrom v$database

NAME LOG_MODE OPEN_MODE DATABASE_R SWITCHOVER_STATUS DB_UNIQUE_

-------------------- ---------- ---------- -------------------- ----------

ORCL150 ARCHIVELOG READ WRITE PRIMARY TO STANDBY ORCL150

备库查询：

SQL>selectname,log_mode,open_mode,database_role,switchover_status,db_unique_name fromv$database

NAME LOG_MODE OPEN_MODE DATABASE_R SWITCHOVER_STATUS DB_UNIQUE_

-------------------- ------------------------ --------------- -------------------- ----------

ORCL150 ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ORCL151

2.2、主库模拟宕机

SQL> create table test as select * fromdba_objects;

Table created.

Elapsed:00:00:00.40

SQL> insert into test select * from test;

86965 rowscreated.

Elapsed:00:00:00.40

SQL> insert into test select * from test;

173930 rowscreated.

Elapsed:00:00:00.87

SQL> insert into test select * from test;

347860 rowscreated.

Elapsed:00:00:01.98

SQL> insert into test select * from test;

695720 rowscreated.

Elapsed:00:00:06.51

SQL> commit;

Commitcomplete.

Elapsed:00:00:00.01

SQL> shutdown abort;

ORACLE instanceshut down.

备库查询：

SQL> select count(*) from test;

COUNT(*)

----------

1391440

Elapsed:00:00:00.25

SQL> select thread#,low_sequence#,high_sequence#from v$archive_gap;

no rowsselected

Elapsed:00:00:00.04

SQL>

注：此时未发现gap，说明此次的failover不会有数据损失，在standby端，要进行关闭apply和结束应用动作.

2.3、备库failover切换

SQL> alter database recover managed standbydatabase cancel;

Databasealtered.

Elapsed:00:00:01.06

SQL> alter database recover managed standbydatabase finish;

Databasealtered.

Elapsed:00:00:00.37

SQL> selectname,log_mode,open_mode,database_role,switchover_status,db_unique_name fromv$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

------------------------- -------------------- -------------------- ----------------------------------------

ORCL150 ARCHIVELOG READ ONLY PHYSICAL STANDBY TO PRIMARY ORCL151

Elapsed:00:00:00.00

SQL> alter database commit to switchover toprimary with session shutdown;

Databasealtered.

Elapsed:00:00:00.06

SQL> select name,log_mode,open_mode,database_role,switchover_status,db_unique_namefrom v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

------------------------- -------------------- -------------------- ----------------------------------------

ORCL150 ARCHIVELOG MOUNTED PRIMARY NOT ALLOWED ORCL151

Elapsed:00:00:00.00

SQL> alter database open;

Databasealtered.

Elapsed:00:00:01.23

SQL>select name,log_mode,open_mode,database_role,switchover_status,db_unique_namefrom v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

------------------------- -------------------- -------------------- ----------------------------------------

ORCL150 ARCHIVELOG READ WRITE PRIMARY FAILED DESTINATION ORCL151

Elapsed:00:00:00.00

2.4、查看日志

切换成功后，观察整个日志情况

Sun Nov 2122:40:28 2021

alter databaserecover managed standby database cancel

Sun Nov 2122:40:28 2021

MRP0:Background Media Recovery cancelled with status 16037

Errors in file/u01/app/oracle/diag/rdbms/orcl151/orcl151/trace/orcl151_pr00_9340.trc:

ORA-16037: userrequested cancel of managed recovery operation

Managed StandbyRecovery not using Real Time Apply

Recoveryinterrupted!

Recovered datafiles to a consistent state at change 1311817

Sun Nov 2122:40:28 2021

MRP0:Background Media Recovery process shutdown (orcl151)

Managed StandbyRecovery Canceled (orcl151)

Completed:alter database recover managed standby database cancel

Sun Nov 2122:40:49 2021

alter databaserecover managed standby database finish

Attempt to do aTerminal Recovery (orcl151)

Media RecoveryStart: Managed Standby Recovery (orcl151)

started logmerger process

Sun Nov 2122:40:49 2021

Managed StandbyRecovery not using Real Time Apply

Parallel MediaRecovery started with 4 slaves

Begin: StandbyRedo Logfile archival

End: StandbyRedo Logfile archival

TerminalRecovery timestamp is '11/21/2021 22:40:49'

TerminalRecovery: applying standby redo logs.

TerminalRecovery: thread 1 seq# 57 redo required

TerminalRecovery:

Recovery ofOnline Redo Log: Thread 1 Group 12 Seq 57 Reading mem 0

Mem# 0:/u01/app/oracle/oradata/orcl151/redo12_standby.log

IdentifiedEnd-Of-Redo (failover) for thread 1 sequence 57 at SCN 0xffff.ffffffff

IncompleteRecovery applied until change 1311818 time 11/21/2021 22:36:13

Media RecoveryComplete (orcl151)

TerminalRecovery: successful completion

Sun Nov 2122:40:49 2021

ARCH: Archivalstopped, error occurred. Will continue retrying

Forcing ARSCNto IRSCN for TR 0:1311818

ORACLE Instanceorcl151 - Archival Error

Attempt to setlimbo arscn 0:1311818 irscn 0:1311818

ORA-16014: log12 sequence# 57 not archived, no available destinations

ORA-00312:online log 12 thread 1: '/u01/app/oracle/oradata/orcl151/redo12_standby.log'

Resettingstandby activation ID 4034107751 (0xf0739967)

Completed:alter database recover managed standby database finish

Sun Nov 2122:42:29 2021

alter databasecommit to switchover to primary with session shutdown

ALTER DATABASESWITCHOVER TO PRIMARY (orcl151)

Maximum waitfor role transition is 15 minutes.

All dispatchersand shared servers shutdown

CLOSE: killingserver sessions.

CLOSE: allsessions shutdown successfully.

Sun Nov 2122:42:29 2021

SMON: disablingcache recovery

Backupcontrolfile written to trace file/u01/app/oracle/diag/rdbms/orcl151/orcl151/trace/orcl151_ora_5502.trc

Standbyterminal recovery start SCN: 1311817

RESETLOGS afterincomplete recovery UNTIL CHANGE 1311818

Online log/u01/app/oracle/oradata/orcl151/redo01.log: Thread 1 Group 1 was previouslycleared

Online log/u01/app/oracle/oradata/orcl151/redo02.log: Thread 1 Group 2 was previouslycleared

Online log/u01/app/oracle/oradata/orcl151/redo03.log: Thread 1 Group 3 was previouslycleared

Standby becameprimary SCN: 1311816

Sun Nov 2122:42:29 2021

Settingrecovery target incarnation to 3

AUDIT_TRAILinitialization parameter is changed back to its original value as specified inthe parameter file.

Switchover:Complete - Database mounted as primary

Completed:alter database commit to switchover to primary with session shutdown

Sun Nov 2122:43:14 2021

alter databaseopen

Sun Nov 2122:43:14 2021

Assigningactivation ID 4038081693 (0xf0b03c9d)

Thread 1advanced to log sequence 2 (thread open)

Sun Nov 2122:43:14 2021

ARC2: Becomingthe 'no SRL' ARCH

此时failover成功

2.5、处理原主库

原主库修复后，正常开启数据库，此时想将原主库切换为备库.

SQL> startup

ORACLE instancestarted.

Total SystemGlobal Area 3206836224 bytes

Fixed Size 2257520 bytes

VariableSize 1811942800 bytes

DatabaseBuffers 1375731712 bytes

RedoBuffers 16904192 bytes

Databasemounted.

Databaseopened.

SQL> alter database commit to switchover tophysical standby with session shutdown;

alter databasecommit to switchover to physical standby with session shutdown

*

ERROR at line1:

ORA-16416: Noviable Physical Standby switchover targets available

SQL> selectname,log_mode,open_mode,database_role,switchover_status,db_unique_name fromv$database;

NAME LOG_MODE OPEN_MODE DATABASE_R SWITCHOVER_STATUS DB_UNIQUE_

-------------------- ---------- ---------- -------------------- ----------

ORCL150 ARCHIVELOG READ WRITE PRIMARY FAILED DESTINATION ORCL150

Elapsed:00:00:00.01

结论：原主库修复后，整个dataguard架构已经被破坏，所以必须把原来的数据删除后构建新的备库，重新恢复dataguard环境.最后根据需要是否进行switchover切换来调整环境.

2.6、原主库重新搭建物理dg

SQL> alter database close;

Databasealtered.

Elapsed:00:00:03.21

SQL> alter system enable restricted session;

System altered.

Elapsed:00:00:02.07

SQL> drop database;

Databasedropped.

Disconnectedfrom Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

With thePartitioning, OLAP, Data Mining and Real Application Testing options

Elapsed:00:00:06.77

SQL> startup nomount;

ORACLE instancestarted.

Total SystemGlobal Area 3206836224 bytes

Fixed Size 2257520 bytes

VariableSize 1811942800 bytes

DatabaseBuffers 1375731712 bytes

RedoBuffers 16904192 bytes

SQL> exit

Disconnectedfrom Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

With thePartitioning, OLAP, Data Mining and Real Application Testing options

[oracle@OEL791dbs]$ rman target sys/oracle_4U@orcl151auxiliary sys/oracle_4U@orcl150

RecoveryManager: Release 11.2.0.4.0 - Production on Sun Nov 21 23:57:05 2021

connected totarget database: ORCL150 (DBID=4034131047)

RMAN-00571:===========================================================

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-00554:initialization of internal recovery manager package failed

RMAN-04006:error from auxiliary database: ORA-12528: TNS:listener: all appropriateinstances are blocking new connections

# RMAN-04006报错，在原主库listener.ora中配置静态监听

[oracle@OEL791dbs]$ rman target sys/oracle_4U@orcl151auxiliary sys/oracle_4U@orcl150

RecoveryManager: Release 11.2.0.4.0 - Production on Mon Nov 22 00:04:09 2021

connected totarget database: ORCL150 (DBID=4034131047)

connected to auxiliarydatabase: ORCL150 (not mounted)

RMAN> duplicate target database

2> for standby nofilenamecheck

3> from active database

4> DORECOVER

5> spfile

6> set db_unique_name='ORCL150'

7>setlog_archive_dest_1='location=/u01/app/oracle/oradata/orcl150/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=orcl150'

8> set log_archive_dest_2='service=ORCL151lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl151'

9> set log_archive_dest_state_1='enable'

10> set log_archive_dest_state_2='enable'

11> set standby_file_management='AUTO'

12> set fal_client='ORCL150'

13> set fal_server='ORCL151'

14> setcontrol_files='/u01/app/oracle/oradata/orcl150/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl150/control02.ctl'

15> setdb_file_name_convert='/u01/app/oracle/oradata/orcl151','/u01/app/oracle/oradata/orcl150'

16> set log_file_name_convert='/u01/app/oracle/oradata/orcl151','/u01/app/oracle/oradata/orcl150'

17> setaudit_file_dest='/u01/app/oracle/admin/orcl150/adump'

18> setdb_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

19> ;

恢复过程：

StartingDuplicate Db at 22-NOV-21

using target databasecontrol file instead of recovery catalog

allocatedchannel: ORA_AUX_DISK_1

channelORA_AUX_DISK_1: SID=63 device type=DISK

contents ofMemory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl151' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl150' targetfile

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl151.ora'auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl150.ora' ;

sql clone "alter system set spfile=''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl150.ora''";

}

executingMemory Script

Starting backupat 22-NOV-21

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=196 device type=DISK

Finished backupat 22-NOV-21

sql statement:alter system set spfile=''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl150.ora''

contents ofMemory Script:

{

sql clone "alter system set db_unique_name =

''ORCL150'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_1 =

''location=/u01/app/oracle/oradata/orcl150/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=orcl150'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_2 =

''service=ORCL151 lgwr async valid_for=(online_logfiles,primary_role)db_unique_name=orcl151'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_state_1 =

''enable'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_state_2 =

''enable'' comment=

'''' scope=spfile";

sql clone "alter system set standby_file_management =

''AUTO'' comment=

'''' scope=spfile";

sql clone "alter system set fal_client =

''ORCL150'' comment=

'''' scope=spfile";

sql clone "alter system set fal_server =

''ORCL151'' comment=

'''' scope=spfile";

sql clone "alter system set control_files =

''/u01/app/oracle/oradata/orcl150/control01.ctl'',''/u01/app/oracle/fast_recovery_area/orcl150/control02.ctl'' comment=

'''' scope=spfile";

sqlclone "alter system set db_file_name_convert =

''/u01/app/oracle/oradata/orcl151'',''/u01/app/oracle/oradata/orcl150'' comment=

'''' scope=spfile";

sql clone "alter system set log_file_name_convert =

''/u01/app/oracle/oradata/orcl151'', ''/u01/app/oracle/oradata/orcl150''comment=

'''' scope=spfile";

sql clone "alter system set audit_file_dest =

''/u01/app/oracle/admin/orcl150/adump''comment=

'''' scope=spfile";

sql clone "alter system set db_recovery_file_dest =

''/u01/app/oracle/fast_recovery_area''comment=

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executingMemory Script

sqlstatement: alter system set db_unique_name = ''ORCL150''comment= '''' scope=spfile

sqlstatement: alter system set log_archive_dest_1 = ''location=/u01/app/oracle/oradata/orcl150/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=orcl150'' comment= ''''scope=spfile

sqlstatement: alter system set log_archive_dest_2 = ''service=ORCL151 lgwr async valid_for=(online_logfiles,primary_role)db_unique_name=orcl151'' comment= '''' scope=spfile

sqlstatement: alter system set log_archive_dest_state_1 = ''enable'' comment= '''' scope=spfile

sqlstatement: alter system set log_archive_dest_state_2 = ''enable''comment= '''' scope=spfile

sqlstatement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sqlstatement: alter system set fal_client= ''ORCL150'' comment= '''' scope=spfile

sqlstatement: alter system set fal_server= ''ORCL151'' comment= '''' scope=spfile

sqlstatement: alter system set control_files = ''/u01/app/oracle/oradata/orcl150/control01.ctl'',''/u01/app/oracle/fast_recovery_area/orcl150/control02.ctl'' comment= '''' scope=spfile

sqlstatement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/orcl151'', ''/u01/app/oracle/oradata/orcl150''comment= '''' scope=spfile

sqlstatement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/orcl151'', ''/u01/app/oracle/oradata/orcl150''comment= '''' scope=spfile

sqlstatement: alter system set audit_file_dest = ''/u01/app/oracle/admin/orcl150/adump'' comment= '''' scope=spfile

sqlstatement: alter system set db_recovery_file_dest = ''/u01/app/oracle/fast_recovery_area'' comment= '''' scope=spfile

Oracleinstance shut down

connectedto auxiliary database (not started)

Oracleinstance started

TotalSystem Global Area 3206836224 bytes

Fixed Size 2257520 bytes

VariableSize 1811942800 bytes

DatabaseBuffers 1375731712 bytes

RedoBuffers 16904192 bytes

contents ofMemory Script:

{

backup as copy current controlfile forstandby auxiliary format '/u01/app/oracle/oradata/orcl150/control01.ctl';

restore clone controlfile to '/u01/app/oracle/fast_recovery_area/orcl150/control02.ctl' from

'/u01/app/oracle/oradata/orcl150/control01.ctl';

}

executingMemory Script

Starting backupat 22-NOV-21

using channelORA_DISK_1

channelORA_DISK_1: starting datafile copy

copying standbycontrol file

output filename=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl151.ftag=TAG20211122T004212 RECID=11 STAMP=1089247332

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backupat 22-NOV-21

Startingrestore at 22-NOV-21

allocatedchannel: ORA_AUX_DISK_1

channelORA_AUX_DISK_1: SID=63 device type=DISK

channelORA_AUX_DISK_1: copied control file copy

Finishedrestore at 22-NOV-21

contents ofMemory Script:

{

sql clone 'alter database mount standbydatabase';

}

executingMemory Script

sql statement:alter database mount standby database

contents ofMemory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/orcl150/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/orcl150/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/orcl150/sysaux01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/orcl150/undotbs01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/orcl150/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/orcl150/example01.dbf";

set newname for datafile 6 to

"/u01/oracle/audit01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/orcl150/system01.dbf" datafile

2 auxiliary format

"/u01/app/oracle/oradata/orcl150/sysaux01.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/orcl150/undotbs01.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/orcl150/users01.dbf" datafile

5 auxiliary format

"/u01/app/oracle/oradata/orcl150/example01.dbf" datafile

6 auxiliary format

"/u01/oracle/audit01.dbf" ;

sql 'alter system archive log current';

}

executingMemory Script

executingcommand: SET NEWNAME

renamedtempfile 1 to /u01/app/oracle/oradata/orcl150/temp01.dbf in control file

executingcommand: SET NEWNAME

Starting backupat 22-NOV-21

using channelORA_DISK_1

channelORA_DISK_1: starting datafile copy

input datafilefile number=00001 name=/u01/app/oracle/oradata/orcl151/system01.dbf

output filename=/u01/app/oracle/oradata/orcl150/system01.dbf tag=TAG20211122T004220

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channelORA_DISK_1: starting datafile copy

input datafilefile number=00002 name=/u01/app/oracle/oradata/orcl151/sysaux01.dbf

output filename=/u01/app/oracle/oradata/orcl150/sysaux01.dbf tag=TAG20211122T004220

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channelORA_DISK_1: starting datafile copy

input datafilefile number=00005 name=/u01/app/oracle/oradata/orcl151/example01.dbf

output filename=/u01/app/oracle/oradata/orcl150/example01.dbf tag=TAG20211122T004220

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channelORA_DISK_1: starting datafile copy

input datafilefile number=00003 name=/u01/app/oracle/oradata/orcl151/undotbs01.dbf

output filename=/u01/app/oracle/oradata/orcl150/undotbs01.dbf tag=TAG20211122T004220

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1:starting datafile copy

input datafilefile number=00006 name=/u01/oracle/audit01.dbf

output filename=/u01/oracle/audit01.dbf tag=TAG20211122T004220

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channelORA_DISK_1: starting datafile copy

input datafilefile number=00004 name=/u01/app/oracle/oradata/orcl151/users01.dbf

output filename=/u01/app/oracle/oradata/orcl150/users01.dbf tag=TAG20211122T004220

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backupat 22-NOV-21

sql statement:alter system archive log current

contents ofMemory Script:

{

backup as copy reuse

archivelog like "/u01/app/oracle/oradata/orcl151/archivelog/1_4_1089240149.dbf"auxiliary format

"/u01/app/oracle/oradata/orcl150/archivelog1_4_1089240149.dbf" archivelog like

"/u01/app/oracle/oradata/orcl151/archivelog/1_5_1089240149.dbf"auxiliary format

"/u01/app/oracle/oradata/orcl150/archivelog1_5_1089240149.dbf" ;

catalog clone archivelog "/u01/app/oracle/oradata/orcl150/archivelog1_4_1089240149.dbf";

catalog clone archivelog "/u01/app/oracle/oradata/orcl150/archivelog1_5_1089240149.dbf";

switch clone datafile all;

}

executingMemory Script

Starting backupat 22-NOV-21

using channelORA_DISK_1

channel ORA_DISK_1:starting archived log copy

input archivedlog thread=1 sequence=4 RECID=46 STAMP=1089247343

output filename=/u01/app/oracle/oradata/orcl150/archivelog1_4_1089240149.dbf RECID=0STAMP=0

channelORA_DISK_1: archived log copy complete, elapsed time: 00:00:02

channelORA_DISK_1: starting archived log copy

input archivedlog thread=1 sequence=5 RECID=47 STAMP=1089247392

output filename=/u01/app/oracle/oradata/orcl150/archivelog1_5_1089240149.dbf RECID=0STAMP=0

channelORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backupat 22-NOV-21

catalogedarchived log

archived logfile name=/u01/app/oracle/oradata/orcl150/archivelog1_4_1089240149.dbf RECID=1STAMP=1089247395

catalogedarchived log

archived logfile name=/u01/app/oracle/oradata/orcl150/archivelog1_5_1089240149.dbf RECID=2STAMP=1089247395

datafile 1switched to datafile copy

input datafilecopy RECID=11 STAMP=1089247395 filename=/u01/app/oracle/oradata/orcl150/system01.dbf

datafile 2switched to datafile copy

input datafilecopy RECID=12 STAMP=1089247395 filename=/u01/app/oracle/oradata/orcl150/sysaux01.dbf

datafile 3switched to datafile copy

input datafilecopy RECID=13 STAMP=1089247395 filename=/u01/app/oracle/oradata/orcl150/undotbs01.dbf

datafile 4switched to datafile copy

input datafilecopy RECID=14 STAMP=1089247395 filename=/u01/app/oracle/oradata/orcl150/users01.dbf

datafile 5switched to datafile copy

input datafilecopy RECID=15 STAMP=1089247395 file name=/u01/app/oracle/oradata/orcl150/example01.dbf

datafile 6switched to datafile copy

input datafilecopy RECID=16 STAMP=1089247395 file name=/u01/oracle/audit01.dbf

contents ofMemory Script:

{

set until scn 1320745;

recover

standby

clone database

delete archivelog

;

}

executingMemory Script

executingcommand: SET until clause

Startingrecover at 22-NOV-21

using channelORA_AUX_DISK_1

starting mediarecovery

archived logfor thread 1 with sequence 4 is already on disk as file/u01/app/oracle/oradata/orcl150/archivelog1_4_1089240149.dbf

archived logfor thread 1 with sequence 5 is already on disk as file/u01/app/oracle/oradata/orcl150/archivelog1_5_1089240149.dbf

archived logfile name=/u01/app/oracle/oradata/orcl150/archivelog1_4_1089240149.dbf thread=1sequence=4

archived logfile name=/u01/app/oracle/oradata/orcl150/archivelog1_5_1089240149.dbf thread=1sequence=5

media recoverycomplete, elapsed time: 00:00:00

Finishedrecover at 22-NOV-21

FinishedDuplicate Db at 22-NOV-21

[oracle@OEL791dbs]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Mon Nov 22 00:45:14 2021

Connected to:

Oracle Database11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

SQL>selectdbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_statusfrom v$database

DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_ OPEN_MODE SWITCHOVER_STATUS

-------------------- ----------- ------------------------------ -------------------------- -------------------- --------------------

4034131047ORCL150 1320744 MAXIMUMPERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED

SQL> alter database open;

Databasealtered.

SQL> alter database recover managed standbydatabase using current logfile disconnect;

Databasealtered.

2.7、检验

现主库(orcl151)执行

SQL> show parameter unique_name

NAME TYPE VALUE

---------------------------------------------------------- ------------------------------

db_unique_name string ORCL151

SQL> select count(*) from test;

COUNT(*)

----------

1381440

Elapsed:00:00:02.70

SQL> delete from test where rownum<=1000000;

1000000 rowsdeleted.

Elapsed:00:00:57.97

SQL> commit;

Commitcomplete.

Elapsed:00:00:00.01

SQL> select count(*) from test;

COUNT(*)

----------

381440

Elapsed:00:00:00.03

连接现备库(orcl150)进行查询：

SQL> conn sys/oracle_4U@orcl150 as sysdba

Connected.

SQL> select count(*) from test;

COUNT(*)

----------

381440

Elapsed:00:00:00.10

2.8、switchover切换

此处做switchover切换，将orcl150重新切换为主库，orcl151切换为备库

现主库(orcl151)切换为备库

SQL> show parameter unique_name

NAME TYPE VALUE

---------------------------------------------------------- ------------------------------

db_unique_name string ORCL151

SQL> selectname,log_mode,open_mode,database_role,switchover_status,db_unique_name fromv$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_

------------------------- -------------------- -------------------- ------------------------------

ORCL150 ARCHIVELOG READ WRITE PRIMARY TO STANDBY ORCL151

Elapsed:00:00:00.00

SQL> alter database commit to switchover tophysical standby with session shutdown;

Databasealtered.

Elapsed:00:00:03.32

SQL> shutdown abort;

ORACLE instanceshut down.

SQL> startup mount;

ORACLE instancestarted.

Total SystemGlobal Area 3206836224 bytes

Fixed Size 2257520 bytes

VariableSize 1811942800 bytes

DatabaseBuffers 1375731712 bytes

RedoBuffers 16904192 bytes

Databasemounted.

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

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_

------------------------- -------------------- -------------------- ------------------------------

ORCL150 ARCHIVELOG MOUNTED PHYSICAL STANDBY RECOVERY NEEDED ORCL151

Elapsed:00:00:00.01

现备库(orcl150)切换为主库

SQL> select name, LOG_MODE, OPEN_MODE,database_role, SWITCHOVER_STATUS, db_unique_name from v$database；

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

------------------------- -------------------- -------------------- ----------------------------------------

ORCL150 ARCHIVELOG READ ONLY WITH APPLY PHYSICALSTANDBY TO PRIMARY ORCL150

SQL> alter database commit to switchover toprimary with session shutdown;

Databasealtered.

SQL> alter database open;

Databasealtered.

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

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

------------------------- -------------------- -------------------- ----------------------------------------

ORCL150 ARCHIVELOG READ WRITE PRIMARY SESSIONS ACTIVE ORCL150

orcl151开启行实时日志应用

SQL> alter database recover managed standbydatabase using current logfile disconnect;

Databasealtered.

Elapsed:00:00:06.19

至此failover、switchover切换以及切换后的恢复测试完毕，数据库也已经还原到最初状态.

帐号		自动登录	找回密码
密码			注册

[Oracle] Oracle Data Guard 主备切换演练