Shulou(Shulou.com)06/01 Report--

Oracle 19C Data Guard basic operation and maintenance-0 5Failovers (GAP)

Original main library

Original library

Failovers

New main library

Independent library

192.168.31.90

192.168.31.100

192.168.31.100

192.168.31.90

Cjcdb

Chendb

Chendb

Cjcdb

Failover:

Https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/data-guard-concepts-and-administration.pdf

Figure 9-4 Failover to a Standby Database

Performing a Failover to a Physical Standby Database

About archive gap?

Previous blog "04 Failovers question?" "I wrote the question about archive gap. In the experiment, I shut down the slave in advance, inserted a large amount of data in the main database to generate three archive files, and manually renamed the last three archive files, in order to prevent the slave from getting these three archive files. When starting the slave, I tried to simulate the archive gap scenario of the slave, but it was empty in the v$archive_gap of the slave. Did the slave not detect the existence of archive gap?

In fact, I have some misunderstandings about the concept of archive gap. For example, there are 1 to 100 archives in the main database. I think as long as any archive file fails to get on the slave side, there will be archive gap and will be recorded in v$archive_gap. Through the previous experiment, I found that this theory is obviously wrong. I forcibly renamed the three archived files of the main database 98 ~ 99100, and there is no archive gap on the standby side, that is, there will be no data in v$archive_gap.

So what exactly is the scene where archive gap will appear? The real scenario is that when the slave database receives the archived files of the master database, some of the archived files are not received successfully, but the subsequent archived files are received successfully, such as 1 to 100 archived files of the main database. For some reason, the slave database did not receive the two archives of 975098, but the subsequent 99100 archives can be received normally, then archive gap will be generated, and the archived information of 9798 will be found in v$archive_gap. (thank you for answering questions on Mo Tianlun platform "Hello I am Li Bai")

The experimental process is as follows:

Scenario 2: failover under archive gap

The main library simulates faults, simulates and archives gap:

Stop the standby database first: do not receive redo or archived data generated by the main library

SQL > shutdown immediate

Main library: generate test data, generate redo and archive data

-session 1

SQL >

Declare

Begin

For i in 1.. 1000 000 loop

Insert into test1 values (I)

Commit

End loop

End

During the insertion of the data, 3 archive files were generated

[oracle@cjcos01 arch] $pwd

/ arch

.

Cjcpdb_arch_1_74_1030641846.arc

Cjcpdb_arch_1_75_1030641846.arc

Cjcpdb_arch_1_76_1030641846.arc

The main library renames the first two newly generated archive files to simulate the archiving gap

[oracle@cjcos01 arch] $mv cjcpdb_arch_1_74_1030641846.arc cjcpdb_arch_1_74_1030641846.arc.bak

[oracle@cjcos01 arch] $mv cjcpdb_arch_1_75_1030641846.arc cjcpdb_arch_1_75_1030641846.arc.bak

Insert part of the data again

SQL >

Declare

Begin

For i in 1.. 1000 0 loop

Insert into test1 values (I)

Commit

End loop

End

Start the standby library:

SQL > startup

-- when the slave database starts, check the corresponding master database log and indicate that the two archive files of 74pj75 cannot be found and cannot be sent to the slave side.

2020-04-19T18:37:53.170879+08:00

Errors in file / u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_tt00_2349.trc:

ORA-00308: cannot open archived log'/ arch/cjcpdb_arch_1_75_1030641846.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2020-04-19T18:37:53.171203+08:00

Errors in file / u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_tt00_2349.trc:

ORA-00308: cannot open archived log'/ arch/cjcpdb_arch_1_74_1030641846.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

Standby library: check archive log, it should actually be 74 to 75, it is not clear why 73 will be displayed

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

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

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

1 73 75

Standby library: no 745075 two archived files were received

The main library renamed system01.dbf simulated database failure

[oracle@cjcos01 arch] $cd / u01/app/oracle/oradata/CJCDB/

[oracle@cjcos01 CJCDB] $mv system01.dbf system01.dbf.bak

SQL > alter system checkpoint

SQL > shutdown abort

Failed to start the main library

SQL > startup

ORACLE instance started.

Total System Global Area 1375728192 bytes

Fixed Size 9134656 bytes

Variable Size 1107296256 bytes

Database Buffers 251658240 bytes

Redo Buffers 7639040 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: data file 1:'/ u01 *

SQL > select open_mode from v$database

OPEN_MODE

-

MOUNTED

Prepare the library:

1. Check the dg recovery model (maximum performance mode)

SQL > select database_role,protection_level,protection_mode from v$database

DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE

PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

2 check archive_gap, it should be 74 to 75, it is not clear why 73 will be displayed.

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

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

1 73 75

Main library:

SQL > select name from v$archived_log where thread#=1 and sequence# between 73 and 75

NAME

/ arch/cjcpdb_arch_1_73_1030641846.arc

/ arch/cjcpdb_arch_1_74_1030641846.arc

/ arch/cjcpdb_arch_1_75_1030641846.arc

Copy the 73 archive file to the repository side

[oracle@cjcos01 arch] $scp cjcpdb_arch_1_73_1030641846.arc cjcos02:/arch

The main library performs flush redo operations in mount

SQL > ALTER SYSTEM FLUSH REDO TO chendb

ALTER SYSTEM FLUSH REDO TO chendb

*

ERROR at line 1:

ORA-16416: No viable switchover targets available

Reserve library: manually register the 73 archive, which also shows that the archive has been registered

SQL > alter database register logfile'/ arch/cjcpdb_arch_1_73_1030641846.arc'

Alter database register logfile'/ arch/cjcpdb_arch_1_73_1030641846.arc'

*

ERROR at line 1:

ORA-16089: archive log has already been registered

But archive gap still shows that there are 73

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

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

1 73 75

Main library: change the name of archived file No. 74 back.

[oracle@cjcos01 arch] $mv cjcpdb_arch_1_74_1030641846.arc.bak cjcpdb_arch_1_74_1030641846.arc

Execute flush redo again

SQL > ALTER SYSTEM FLUSH REDO TO chendb

ALTER SYSTEM FLUSH REDO TO chendb

*

ERROR at line 1:

ORA-16416: No viable switchover targets available

Check the main database log, the main library has archived 74 to the standby side, and began to try to read the 75 archive file.

If the flush redo command does not take effect, you can also copy the archive file to the standby side and perform the registration manually

SQL > ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'

Reserve library: only one 75 file can not be found.

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

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

1 75 75

Standby library: canceling the application process

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Database altered.

Standby library: due to the existence of archive gap, regular failover is not allowed

SQL > ALTER DATABASE FAILOVER TO chendb

ALTER DATABASE FAILOVER TO chendb

*

ERROR at line 1:

ORA-00283: recovery session canceled due to errors

ORA-16171: RECOVER...FINISH not allowed due to gap for thr 1, seq 75-75

Standby library: adding force does not take effect

SQL > ALTER DATABASE FAILOVER TO chendb force

ALTER DATABASE FAILOVER TO chendb force

*

ERROR at line 1:

ORA-00283: recovery session canceled due to errors

ORA-16171: RECOVER...FINISH not allowed due to gap for thr 1, seq 75-75

Mandatory failover: if you enforce failover in the presence of archive gap, you will lose data and you will use it cautiously in the formal environment!

Perform a data loss failover.

If an error condition cannot be resolved, a failover can still be performed (with some data loss) by issuing the following SQL statement on the target standby database:

SQL > ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE

Open the database

SQL > ALTER DATABASE OPEN

View data

SQL > select count (*) from test1

COUNT (*)

-

252780

The test1 table lost 1000 000 million 1000-252780 = 748220 pieces of data.

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.