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

What is the configuration of Snapshot Standby Database in Data Guard? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Data Guard-Snapshot Standby Database configuration

Overview

-

In general, the physical standby database is in the mount state to receive and apply the REDO logs of the main database, and the physical standby database cannot be accessed externally. If you need read-only access, you can temporarily open the physical repository in the way of read-only, or configure ACTIVE DATA GUARD, then the physical standby database can have read-only (read-only) access (such as report business query), but the physical standby database cannot perform read-write operations (read-write).

In some cases, in order to achieve stress testing or Real Application Testing (RAT) or other read-write testing of the system, you can temporarily convert the physical standby database to a snapshot standby database and then test it, because the snapshot standby database is independent of the main library and can be read-write (read-write). During the test, the snapshot standby database normally receives the archived logs of the main database to ensure the data security of the main database, but these logs will not be applied. When the stress test is over, it is very simple to convert snapshot standby into a physical standby database and continue to synchronize the master database logs.

Configuration

-

1. Physical standby configuration flashback log

SQL > Alter system set db_recovery_file_dest_size=500M

System altered.

SQL > Alter system set db_recovery_file_dest='/u01/app/oracle/snapshot_standby'

System altered.

two。 Physical standby stops applying logs

SQL > alter database recover managed standby database cancel

Database altered.

3. Physical standby is converted to snapshot standby, and open snapshot standby

SQL > alter database convert to snapshot standby

Database altered.

SQL > alter database open

Database altered.

Check that the snapshot standby database role is SNAPSHOT STANDBY,open and the schema is READ WRITE:

SQL > select DATABASE_ROLE,name,OPEN_MODE from v$database

DATABASE_ROLE NAME OPEN_MODE

SNAPSHOT STANDBY FSDB READ WRITE

4. Perform stress testing or Real Application Testing (RAT) or other read and write operations on the snapshot standby database.

5. At the end of the test, convert snapshot standby to physical standby and restart the application log

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Database mounted.

SQL > ALTER DATABASE CONVERT TO PHYSICAL STANDBY

Database altered.

SQL > shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Database mounted.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Database altered.

5. After the conversion to physical standby, check that the standby role is PHYSICAL STANDBY,open and the mode is MOUNTED.

SQL > select DATABASE_ROLE,name,OPEN_MODE from v$database

DATABASE_ROLE NAME OPEN_MODE

PHYSICAL STANDBY FSDB MOUNTED

6. Check that the main database and the physical standby log are synchronized

Master Library Log:

SQL > select ads.dest_id,max (sequence#) "Current Sequence"

Max (log_sequence) "Last Archived"

From v$archived_log al, v$archive_dest ad, v$archive_dest_status ads

Where ad.dest_id=al.dest_id

And al.dest_id=ads.dest_id

And al.resetlogs_change#= (select max (resetlogs_change#) from v$archived_log)

Group by ads.dest_id

DEST_ID Current Sequence Last Archived

1 361 361

2 361 362

-- prepare the library log

SQL > select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"

From (select thread# thrd, max (sequence#) almax

From v$archived_log

Where resetlogs_change#= (select resetlogs_change# from v$database)

Group by thread#) al

(select thread# thrd, max (sequence#) lhmax

From v$log_history

Where resetlogs_change#= (select resetlogs_change# from v$database)

Group by thread#) lh

Where al.thrd = lh.thrd

Thread Last Seq Received Last Seq Applied

1 361 361

After reading the above, have you mastered the method of Snapshot Standby Database configuration in Data Guard? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.