Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the characteristics of Oracle snapshot standby". The explanation in the article is simple and clear, easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the characteristics of Oracle snapshot standby?"

As we all know, there are three modes of Oracle's standby database: physical standby database, logical standby database, and snapshot standby database. Here is mainly about the characteristics of the snapshot standby database, the database version is 11gR2.

1. Characteristics of snapshot standby database

The snapshot standby database has the following characteristics:

1. It can be opened in read-write mode and is a fully updatable standby database.

2. Receive related logs from the master database, but do not apply

3. When converted to a physical standby database, all previous updates are discarded first, and then the received redo data is applied

4. Cannot be the target of switchver or failover. Before performing a role conversion, you must first convert to a physical standby database

5. In a maximum protection Data Guard configuration, the snapshot standby database cannot be the only standby database.

Note: any operation using Flashback Database technology is irreversible, and Flashback Database will prevent a snapshot standby from being converted to a physical standby

Second, the construction of snapshot standby database

1. Build physical standby database

The snapshot standby database is transformed from the physical standby database. Therefore, first of all, build a physical standby database, the process is brief.

2. Convert a physical standby database to a snapshot standby database

1) View the database mode, recovery model and protection mode of the destination on the main database

SQL > select dest_id,database_mode,recovery_mode,protection_mode from v$archive_dest_status where dest_id = 2

DEST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE

-

2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE

You can see that the standby database is opened as read-only, applying redo in real time at the highest availability level

2) stop the Redo Apply of the physical standby database

SQL > alter database recover managed standby database cancel

Database altered.

3) if the physical standby database is RAC, only one instance is retained and the others are closed. The physical standby database in this environment is a single instance.

4) make sure the flashback recovery area is configured

SQL > show parameter db_recover

NAME TYPE VALUE

-

Db_recovery_file_dest string / opt/app/oracle/fast_recovery_area

Db_recovery_file_dest_size big integer 4182M

Otherwise, an error will be reported during the conversion:

ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_10/24/2018 10, 41, 21.

ORA-38786: Recovery area is not enabled.

5) convert a physical standby database to a snapshot standby database

Execute on the repository:

SQL > alter database convert to snapshot standby

Database altered.

You can see that the database creates a restore point:

SQL > select name,storage_size from v$restore_point

NAME STORAGE_SIZE

SNAPSHOT_STANDBY_REQUIRED_10/24/2018 10:43:28 1073741824

At this point, the database is in mounted state:

SQL > select name

2 database_role

3 switchover_status

4 open_mode

5 protection_mode

6 from v$database

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE

-

BDDEV2 SNAPSHOT STANDBY NOT ALLOWED MOUNTED MAXIMUM PERFORMANCE

6) Open the repository

SQL > select name

2 database_role

3 switchover_status

4 open_mode

5 protection_mode

6 from v$database

At this point, the slave database has been converted:

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE

-

BDDEV2 SNAPSHOT STANDBY NOT ALLOWED READ WRITE MAXIMUM PERFORMANCE

3. Convert snapshot standby to physical standby

1. Restart the standby library to mount mode

SQL > startup force mount

ORACLE instance started.

two。 Prepare the database to execute statements, which are converted to physical standby

SQL > alter database convert to physical standby

Database altered.

After conversion, the database is uninstalled and needs to be restarted.

3. Restart the database

SQL > startup force

ORACLE instance started.

4. Restore redo application

SQL > alter database recover managed standby database using current logfile disconnect

Database altered.

SQL > select name

2 database_role

3 switchover_status

4 open_mode

5 protection_mode

6 from v$database

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE

-

BDDEV2 PHYSICAL STANDBY RECOVERY NEEDED READ ONLY MAXIMUM PERFORMANCE

4. Snapshot standby database test

1. Test whether it can read and write

1) Update the table scott.tb_test:

SQL > delete from scott.tb_test where object_id = 20

1 row deleted.

2) Delete table scott.tb_test:

SQL > drop table scott.tb_test purge

Table dropped.

At this point, the table scott.tb_test no longer exists in the slave library, but still exists in the main library.

3) create a new table

SQL > create table scott.tb_test2 (id int)

Table created.

two。 Test whether logs can be received and not applied

Main library:

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / opt/app/oracle/archivelog/

Oldest online log sequence 77

Next log sequence to archive 79

Current log sequence 79

You can see that the current log sequence number of the main database is 79.

Prepare the library:

SQL > SELECT PROCESS

2 PID

3 STATUS

4 SEQUENCE#

5 DELAY_MINS

6 FROM V$MANAGED_STANDBY

PROCESS PID STATUS SEQUENCE# DELAY_MINS

ARCH 25422 CLOSING 78 0

ARCH 25425 CONNECTED 0 0

ARCH 25427 CLOSING 76 0

ARCH 25429 CLOSING 77 0

RFS 26020 IDLE 0 0

RFS 26023 IDLE 0 0

RFS 26026 IDLE 79 0

You can see that the redo sequence number being received by the slave RFS process is also 79, so the slave database can receive the log at this time. The MRP process in the slave library is not started, so it can be concluded that the log is not applied at this time.

3. Convert snapshot standby to physical standby to see if scott.tb_test can be restored

Follow the steps in step 3 to convert snapshot standby to physical standby, and check whether there is a scott.tb_test in the slave repository:

SQL > select count (1) from scott.tb_test

COUNT (1)

-

86415

Check to see if the table scott.tb_test2 built in the slave database still exists:

SQL > desc scott.tb_test2

ERROR:

ORA-04043: object scott.tb_test2 does not exist

In summary, it shows that when a snapshot standby is converted to a physical standby, the changes made in the snapshot standby state are discarded.

Thank you for your reading, the above is the content of "what are the characteristics of Oracle snapshot standby". After the study of this article, I believe you have a deeper understanding of what the characteristics of Oracle snapshot standby are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.