According to the official oracle documentation, the oracle 11g replicuard can also apply redo in the open state on the physical standby machine, so it was tested locally, in this way, both redo and Data Query and Database Backup can be applied. it not only solves data disaster tolerance but also solves the pressure on the primary database.
Introduction to Oracle elastic uard
Dataguard is divided into two categories:
Physical standby: This type is divided into redo application, read-only mode, read-write mode. When the redo application is in, the related query operations cannot be performed. When the read-only application is in, redo cannot be applied, But redo can be applied under read-only after oracle 11G. When read-write is in place, the redo data is accepted from primary.
Logical standby: In read-write mode, this type can accept redo data from primary and apply redo.
You can search for the differences and features of the two on the Internet.
Two types of related processes:
Physical standby Processes
_ Remote file server (RFS)
Receives archive files from the primary database.
_ Archiver (ARCn)
Archiving logs
Managed recovery process (MRP)
Apply the archive file to the backup machine
Logical standby Processes
_ Remote file server (RFS)
Receives archive files from the primary database.
_ Archiver (ARCn)
Archiving logs
Logical standby process (LSP)
Apply the archive file to the backup machine
Lab environment:
In this test, the vmware server 1.6 virtual machine is used. The IP address planning is as follows:
192.168.137.93 primary database
192.168.137.94 standby Database
Perform the following operations on the two servers:
# Vi/etc/hosts
192.168.137.93 node1
192.168.137.94 node2
Software Environment
Database: oracle 11G
Dataguard Configuration:
Perform the following operations on the primary server:
SQL> ALTER DATABASE FORCE LOGGING; // set the primary DATABASE
Force logging Mode
SQL> create pfile from spfile; // create a pfile to facilitate editing. Here I put the spfile in another path so that oracle can read pfile.
Add the following content to the pfile file:
*. DB_UNIQUE_NAME = uqn_node1 // customize
Unique_name name
*. LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (uqn_node1, uqn_node2) '// This is the master Backup Server unique_name
*. LOG_ARCHIVE_DEST_2 = 'service = node2 ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = uqn_node2'
*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE
*. LOG_ARCHIVE_DEST_STATE_2 = ENABLE
*. LOG_ARCHIVE_MAX_PROCESSES = 30
*. FAL_SERVER = node2 // here is net
Service name
*. FAL_CLIENT = node1
*. STANDBY_FILE_MANAGEMENT = AUTO
If the database is in non-archive mode, execute the following SQL statement (this step depends on whether the database is in archive mode)
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
Create a control file for STANDBY data
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oradata/wiliiwin. ctl ';
SQL> ALTER DATABASE OPEN;
Copy the files on the primary server (data files, password files, pfile files, and control files) to the STANDBY server.
# Cd/home/oracle/dbs
# Cp initora10.ora orapwora10/home/oradata/
# Cd/home/
$ Scp-r admin/oradata/oracle @ node2:/home/oradata
Perform the following operations on the STANDBY server:
$ Cd/home/oradata/ora10
$ Rm-rf control0 *
$ Mv ../wiliiwin. ctl control01.ctl
$ Cp control01.ctl control02.ctl
$ Cp control01.ctl control03.ctl
$ Cd/home/oracle/dbs
$ Mv/home/oradata/initora10.ora.
$ Mv/home/oradata/orapwora10.
$ Vi initora10.ora // you need
Slightly modify
*. DB_UNIQUE_NAME = uqn_node2 // customize
Unique_name name
*. LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (uqn_node1, uqn_node2) '// The unique_name of the master backup server.
*. LOG_ARCHIVE_DEST_2 = 'service = node1 ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = uqn_node1'
*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE
*. LOG_ARCHIVE_DEST_STATE_2 = ENABLE
*. LOG_ARCHIVE_MAX_PROCESSES = 30
*. FAL_SERVER = node1 // here is net
Service name
*. FAL_CLIENT = node2
*. STANDBY_FILE_MANAGEMENT = AUTO
$ Lsnrctl start // in primary and
STANDBY is executed on the server.
Start a database instance on the master server
SQL> startup;
$ Sqlplus system/oracle @ node1 // you can test the connection on both the primary and STANDBY servers.
Start a database instance on the STANDBY server
// It is best to create four stanby redo files of the same size as the master server, and add one more file. It is best to perform the following operations on the Primary server to facilitate later role switching.
SQL> startup mount;
SQL> alter database add standby logfile group 4'/home/oradata/ora10/stdREDO01.LOG 'size 50 m;
SQL> alter database add standby logfile group 5'/home/oradata/ora10/stdredo02log' size 50 m;
SQL> alter database add standby logfile group 6'/home/oradata/ora10/stdREDO03LOG 'size 50 m;
SQL> alter database add standby logfile group 7'/home/oradata/ora10/stdREDO04LOG 'size 50 m;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; // start real-time applications
Test whether the dataguard data is synchronized.
Method 1
On the STANDBY server:
SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;
SEQUENCE # FIRST_TIM NEXT_TIME
----------------------------
4 04-MAY-10 04-MAY-10
5 04-MAY-10 04-MAY-10
On the Primary server:
SQL> ALTER SYSTEM SWITCH LOGFILE; // triggers Archiving
Now return to the STANDBY server:
SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;
SEQUENCE # FIRST_TIM NEXT_TIME
----------------------------
4 04-MAY-10 04-MAY-10
5 04-MAY-10 04-MAY-10
6 04-MAY-10 04-MAY-10
SQL> SELECT SEQUENCE #, APPLIED FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;
SEQUENCE # APP
-------------
4 YES
5 YES
6 YES
If a new record is found, synchronization is normal.
Method 2
Create a table on the Primary server
SQL> create table wiliiwin (id integer, name char (10 ));
SQL> insert into wiliiwin values (1, 'wiliiwin ');
SQL> insert into wiliiwin values (2, 'wiliiwiner ');
SQL> insert into wiliiwin values (3, 'wiliiwinsi ');
SQL> ALTER SYSTEM SWITCH LOGFILE;
On the STANDBY server
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; // CANCEL executing redo
// Error ORA-16136: Managed Standby Recovery not active
Because the MRP0 service is not started, the following is an SQL statement to view the status of each service.
SQL> select process, status, sequence # from v $ managed_standby;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; // start the MRP0 Service
SQL> ALTER DATABASE OPEN; // OPEN the DATABASE.
So now
You can also apply redo.
SQL> select * from wiliiwin;
ID NAME
--------------------
1 wiliiwin
2 wiliiwiner
3 wiliiwinsi // you can see that the data has been synchronized from
Oracle 11 GB is also available in the open state
To execute redo, so now in primary do
For related operations, STANDBY can also be the same
Step by step
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; // restores STANDBY to the mount state.