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

During the incremental production of a production business, the customer found that the database would switch the Snapshot standby mode. Take a learning test.

I. explanation of the principle

In Oracle 11g, data guard has the most attractive real-time query feature of active data guard (that is, the physical standby database can be opened read-only while the MRP process can continue to do recover), and the snapshot standby database is also good, which is more suitable for rapid deployment of a temporary test database with the same online environment. Through the principle of restore point (restore point) and flashback database (flashback database), it can open the physical standby database in read / write mode, modify the database, and then restore to the physical standby database according to the restore point.

Snapshot standby mode, that is, when you open this mode, in order to perform some test operations in the repository, but not to keep it in the database. When the slave database is switched back to the physical standby physical standby, the previous tests conducted in snapshot standby mode will be discarded.

Remarks:

Physical standby is the highest protected mode (maximum protection) and cannot be converted to snapshot standby.

Physical standby uses standby redo log. After create restore point, alter system switch logfile;, is required to ensure that the scn of the restore point is archived on the physical standby library, otherwise it may not be able to flash back to the restore point successfully.

After switching from physical standby to snapshot standby, a large number of redo logs are generated by primary database after a long interval, so that after conversion to physical standby, the restore speed of physical standby can be accelerated by incremental backup of primary database and recover to physical standby.

Note:

1. First of all, you need to make sure that the database has finished the log application! (some of the steps reported error due to lack of confirmation in this test)

In 2.snapshot standby mode, the flashback database function can be turned on or off. The default is off, but the size and path of the fast recovery area must be set.

Second, prepare the node2 test steps of the database node:

[oracle@node2 ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 19 21:46:34 2018

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, OLAP, Data Mining and Real Application Testing options

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

SQL > select flashback_on from v$database

FLASHBACK_ON

-

NO

SQL > select open_mode,database_role from v$database

OPEN_MODE DATABASE_ROLE

--

READ ONLY WITH APPLY PHYSICAL STANDBY

SQL > select status from v$instance

STATUS

-

OPEN

SQL > alter database convert to snapshot standby

Alter database convert to snapshot standby

*

ERROR at line 1:

ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_06/15/2018

02PUR 27VOG 25mm.

ORA-01153: an incompatible media recovery is active

To inquire about the network information:

ORA-01153: an incompatible media recovery is active

Cause: Attempted to start an incompatible media recovery or open resetlogs

During media recovery or RMAN backup. Media recovery sessions are

Incompatible if they attempt to recover the same data file. Incomplete media

Recovery or open resetlogs is incompatible with any media recovery. Backup or

Restore by RMAN is incompatible with open resetlogs

Action: Complete or cancel the other media recovery session or RMAN backup

Solution:

SQL > alter database recover managed standby database cancel

Database altered.

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

Database altered.

SQL > select open_mode,database_role from v$database

OPEN_MODE DATABASE_ROLE

--

MOUNTED PHYSICAL STANDBY

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in progress

Reason: after opening the backup application log, the application log is not cancelled, but the database is started directly and an error is reported.

SQL > alter database recover managed standby database cancel

Database altered.

SQL > alter database open

Database altered.

SQL > select open_mode,database_role from v$database

OPEN_MODE DATABASE_ROLE

--

READ ONLY PHYSICAL STANDBY

SQL > alter database convert to snapshot standby

Alter database convert to snapshot standby

*

ERROR at line 1:

ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_06/15/2018

02VOG 46VO2mm.

ORA-38786: Recovery area is not enabled.

Create a directory to store the quick recovery area

[root@node2 oradata] # su-oracle

[oracle@node2 ~] $cd / oradata/

[oracle@node2 oradata] $mkdir recovery

SQL > show parameter recovery

NAME TYPE VALUE

-

Db_recovery_file_dest string

Db_recovery_file_dest_size big integer 4182M

Recovery_parallelism integer 0

SQL > alter system set db_recovery_file_dest='/oradata/recovery'

System altered.

SQL > alter system set db_recovery_file_dest_size=2G

System altered.

After solving the error report, you can execute the snapshot.

SQL > alter database convert to snapshot standby

Database altered.

3. Check the snapshot information (you can check the alert log and you will find that a guaranteed restore point has been created in the database to ensure that we switch back to the master / slave and log can be applied)

[oracle@node2 trace] $tail-f alert_orcls.log

Alter database convert to snapshot standby

Starting background process RVWR

Fri Jun 15 02:51:28 2018

RVWR started with pid=19, OS id=7250

Allocated 3981120 bytes in shared pool for flashback generation buffer

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_06/15/2018 02:51:28

Killing 3 processes with pids 7193 in order to disallow current and future RFS connections 71979 (all RFS). Requested by OS process 7182

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

Fourth, check the status of the database and test it.

SQL > select open_mode,database_role from v$database

OPEN_MODE DATABASE_ROLE

--

MOUNTED SNAPSHOT STANDBY

SQL > select flashback_on from v$database

FLASHBACK_ON

-

RESTORE POINT ONLY

SQL > select open_mode,database_role from v$database

OPEN_MODE DATABASE_ROLE

--

MOUNTED SNAPSHOT STANDBY

SQL > alter database open

Database altered.

SQL > select open_mode,database_role from v$database

OPEN_MODE DATABASE_ROLE

--

READ WRITE SNAPSHOT STANDBY

Do a test

SQL > CREATE TABLE ZHU (An INT)

Table created.

SQL > INSERT INTO ZHU VALUES (1)

1 row created.

SQL > COMMIT

Commit complete.

SQL > SELECT * FROM ZHU

A

-

one

5. Restore the physical backup database. The database needs to be switched under mount and verified for testing.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 776646656 bytes

Fixed Size 2257272 bytes

Variable Size 507514504 bytes

Database Buffers 264241152 bytes

Redo Buffers 2633728 bytes

Database mounted.

SQL > alter database convert to physical standby

Database altered.

SQL > select status from v$instance

STATUS

-

STARTED

You need to restart to the mount application log after switching.

SQL > shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 776646656 bytes

Fixed Size 2257272 bytes

Variable Size 507514504 bytes

Database Buffers 264241152 bytes

Redo Buffers 2633728 bytes

Database mounted.

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

Database altered.

SQL > alter database recover managed standby database cancel

Database altered.

SQL > select database_role,open_mode from v$database

DATABASE_ROLE OPEN_MODE

--

PHYSICAL STANDBY MOUNTED

SQL > alter database open

Database altered.

SQL > select database_role,open_mode from v$database

DATABASE_ROLE OPEN_MODE

--

PHYSICAL STANDBY READ ONLY

Validate tables created in SNAPSHOT STANDBY mode

SQL > select * from ZHU

Select * from ZHU

*

ERROR at line 1:

ORA-00942: table or view does not exist

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.