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;