Oracle 11g physical uard physical standby configuration

 

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.