Oracle 11GR2 Physical standby database setup and switching

Steps to build a physical standby database on the same machine, the Linux environment Oracle 11.2.0.1

Main Library: ORCL

Prepare library: Stby

1 Check if the listening is started

2 Configuring the initialization parameter file for the primary standby 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

*.processes=150

*.remote_login_passwordfile= ' EXCLUSIVE '

*.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 a parameter file for a standby

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 ' #<--on the same machine. DG to be the same as the main library otherwise 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

*.processes=150

*.remote_login_passwordfile= ' EXCLUSIVE '

*.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 the main library

Rman Target/

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

Create a Standby 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

Working with a standby library

Export Oracle_sid=stby

Orapwd file=/oracle/product/11.2.0/db_1/dbs/orapwstby password=oracle entries=5 ignorecase=y #一定要加ignorecase =y Or the archive won't be sent to the standby library.

Sqlplus "/as sysdba"

Startup Nomount

ALTER DATABASE Mount;

Rman Target/

Restore database;

Reboot the main library

Export ORACLE_SID=ORCL

Sqlplus "/as sysdba"

Shutdown immediate

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

Configure Tnsnames.ora (because it is on the same machine, so change this file)

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)

)

)

Placing a standby in the receiving archive log status

Export Oracle_sid=stby

Sqlplus "/as sysdba"

ALTER DATABASE recover managed standby database disconnect from session;

Check to see if you get the log in a minute.

Export ORACLE_SID=ORCL

Sqlplus "/as sysdba"

Select Max (sequence#) from V$archived_log; --View archived log serial number

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 archived log serial number

Primary repository role switching

Role switching

Step 1: Verify that the Master Library can perform role switching, to standby

Sql> SELECT switchover_status from V$database;

Switchover_status

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

To STANDBY

Step 2: Perform roles on the main library to switch from the library role

Sql> ALTER DATABASE COMMIT to switchover to physical STANDBY;

Step 3: Close and restart the main library instance before

Sql> SHUTDOWN IMMEDIATE

Sql> STARTUP MOUNT

Step 4: View the toggle state of the standby in the V$database view of the repository

Sql> SELECT switchover_status from V$database;

Switchover_status

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

To_primary

Step 5: Switch the standby to the main library role

Sql> ALTER DATABASE COMMIT to switchover to PRIMARY;

Step 6: Complete the switch from the standby to the main library

1. If the standby is not open in read-only mode, execute the following statement directly to open to the new Master library.

sql> ALTER DATABASE OPEN;

2. If the standby is open in read-only mode, close the data before restarting it.

Sql> SHUTDOWN IMMEDIATE;

Sql> STARTUP;

Step 7: Restart the Redo Log Application service on the new standby, if necessary

sql> ALTER DATABASE recover managed standby database disconnect from session;

(Note: You can view the status of the current standby application redo log by using the select Message from V$dataguard_status;

Step 8: Start sending the redo data to the repository

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 is a missing archive log file, manual test behind, on the standby:

ALTER DATABASE REGISTER physical LOGFILE ' filespec1 ';

The FORCE keyword terminates the active RFS process on the target physical standby database, enabling failover to take place immediately without waiting for the network connection to timeout.

ALTER database RECOVER MANAGED STANDBY database FINISH FORCE;