Oracle 11gR2 physical standby database construction and switching

Steps for building a physical standby database on the same machine: oracle 11.2.0.1 in linux

Master Database: orcl

Slave Database: stby

1. Check whether the listener is started.

2. Configure the initialization parameter file for the master-slave Database

Sqlplus "/as sysdba"

Create pfile = '/home/oracle/initprim. ora' from spfile;

Cp/home/oracle/initprim. ora/home/oracle/initstby. ora

Vi/home/oracle/initprim. ora

Orcl. _ db_cache_size = 104857600

Orcl. _ Java _pool_size = 4194304

Orcl. _ large_pool_size = 4194304

Orcl. _ oracle_base = '/oracle' # ORACLE_BASE set from environment

Orcl. _ pga_aggregate_target = 155189248

Orcl. _ sga_target = 268435456

Orcl. _ shared_io_pool_size = 0

Orcl. _ shared_pool_size = 142606336

Orcl. _ streams_pool_size = 4194304

*. Audit_file_dest = '/oracle/admin/orcl/adump'

*. Audit_trail = 'db'

*. Compatible = '11. 2.0.0.0'

*. Control_files = '/oradata/orcl/control01.ctl', '/oradata/flash_recovery_area/orcl/control02.ctl'

*. Db_block_size = 8192

*. Db_domain =''

*. Db_name = 'orcl'

*. Db_recovery_file_dest = '/oradata/flash_recovery_area'

*. Db_recovery_file_dest_size = 4039114752

*. Diagnostic_dest = '/oracle'

*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB )'

*. Memory_target = 422576128

*. Open_cursors = 300

*. Processses = 150

*. Remote_login_passwordfile = 'clusive'

*. Undo_tablespace = 'undotbs1'

*. Fal_client = 'prim'

*. Fal_server = 'stby'

*. Standby_file_management = auto

*. Log_archive_dest_1 = 'location =/oradata/arch/orcl valid_for = (all_logfiles, all_roles) db_unique_name = prim'

*. Log_archive_dest_2 = 'service = stby valid_for = (online_logfiles, primary_role) db_unique_name = stby'

*. DB_UNIQUE_NAME = prim

*. Log_archive_config = 'dg _ config = (prim, stby )'

Edit the parameter file of the slave Database

Vi/home/oracle/initstby. ora

Stby. _ db_cache_size = 104857600

Stby. _ java_pool_size = 4194304

Stby. _ large_pool_size = 4194304

Stby. _ oracle_base = '/oracle' # ORACLE_BASE set from environment

Stby. _ pga_aggregate_target = 155189248

Stby. _ sga_target = 268435456

Stby. _ shared_io_pool_size = 0

Stby. _ shared_pool_size = 142606336

Stby. _ streams_pool_size = 4194304

*. Audit_file_dest = '/oracle/admin/stby/adump'

*. Audit_trail = 'db'

*. Compatible = '11. 2.0.0.0'

*. Control_files = '/oradata/stby/control01.ctl', '/oradata/flash_recovery_area/stby/control02.ctl'

*. Db_block_size = 8192

*. Db_domain =''

*. Db_name = 'orcl '# <-- Build dg on the same machine to be the same as the master database; otherwise, the ora-01103

*. Db_recovery_file_dest = '/oradata/flash_recovery_area'

*. Db_recovery_file_dest_size = 4039114752

*. Diagnostic_dest = '/oracle'

*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = stbyXDB )'

*. Memory_target = 622576128

*. Open_cursors = 300

*. Processses = 150

*. Remote_login_passwordfile = 'clusive'

*. Undo_tablespace = 'undotbs1'

*. DB_FILE_NAME_CONVERT = '/oradata/orcl', '/oradata/stby'

*. LOG_FILE_NAME_CONVERT = '/oradata/orcl', '/oradata/stby'

*. Fal_client = 'stby'

*. Fal_server = 'prim'

*. Standby_file_management = auto

*. Log_archive_dest_1 = 'location =/oradata/arch/stby valid_for = (all_logfiles, all_roles) db_unique_name = stby'

*. Log_archive_dest_2 = 'service = prim valid_for = (online_logfiles, primary_role) db_unique_name = prim'

*. DB_UNIQUE_NAME = 'stby'

*. Log_archive_config = 'dg _ config = (prim, stby )'

Back up master database

Rman target/

Backup database format'/u01/oradata/dbfull % U ';

Create a slave Database Control File

Export ORACLE_SID = orcl

Sqlplus "/as sysdba"

Alter database create standby controlfile as '/oradata/stby/stbycontrol. ctl ';

Cp/oradata/stby/stbycontrol. ctl/oradata/stby/control01.ctl

Cp/oradata/stby/stbycontrol. ctl/oradata/flash_recovery_area/stby/control02.ctl

Process slave Database

Export ORACLE_SID = stby

Orapwd file =/oracle/product/11.2.0/db_1/dbs/orapwstby password = oracle entries = 5 ignorecase = y # Be sure to add ignorecase = y or archive will not be passed to the slave Database

Sqlplus "/as sysdba"

Startup nomount

Alter database mount;

Rman target/

Restore database;

Restart master database

Export ORACLE_SID = orcl

Sqlplus "/as sysdba"

Shutdown immediate

Startup pfile = '/home/oracle/initprim. ora'

Configure tnsnames. ora (this file is changed because it is on the same machine)

Orcl =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = orcl)

(SERVER = DEDICATED)

)

)

Stby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = stby)

(SERVER = DEDICATED)

)

)

Place the standby database in the status of receiving archived logs

Export ORACLE_SID = stby

Sqlplus "/as sysdba"

Alter database recover managed standby database disconnect from session;

Check whether logs are received later

Export ORACLE_SID = orcl

Sqlplus "/as sysdba"

Select max (sequence #) from v $ archived_log; -- view the serial number of the archived log

Alter system switch logfile;

Alter system switch logfile;

Export ORACLE_SID = stby

Sqlplus "/as sysdba"

Select sequence #, applied from v $ archived_log order by 1; -- view the archive log serial number

Master/Slave database Role Switching

Role Switching

Step 1: verify whether the master database can switch roles. to standby indicates that the master database can switch roles.

SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;

SWITCHOVER_STATUS

-----------------

TO STANDBY

Step 2: Execute the role on the master database to switch to the slave database role

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Step 3: Disable and restart the master database instance

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

Step 4: view the switchover status of the slave DATABASE in the V $ DATABASE view of the slave DATABASE

SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;

SWITCHOVER_STATUS

-----------------

TO_PRIMARY

Step 5: Switch the slave database to the master database role

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 6: complete the switchover from the slave database to the master database

1. If the standby database is not opened in read-only mode, run the following statement to open it to the new primary database.

SQL> ALTER DATABASE OPEN;

2. If the standby database is enabled in read-only mode, close the data and restart the database.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

Step 7: restart the redo log application service on the new slave database if necessary.

SQL> alter database recover managed standby database disconnect from session;

(Note: You can use select message from v $ reset uard_status to view the redo log status of the application in the current slave database)

Step 8: Start sending redo data to the slave Database

Issue the following statement on the new primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Note:

Alter database recover managed standby database using current logfile;

If there are missing archive log files, the manual examination is performed on the slave database:

Alter database register physical logfile 'filespec1 ';

FORCE keyword terminates the RFS process active on the target physical standby database, so that failover can be performed immediately without waiting for network connection timeout.

Alter database recover managed standby database finish force;