DG--主库备库 切换

 

 

主库中执行

 

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

 

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2020192 bytes
Variable Size             100666528 bytes
Database Buffers          176160768 bytes
Redo Buffers                6365184 bytes
SQL> alter database mount standby database;

Database altered.

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

Database altered.

 

 

SQL> select  OPEN_MODE,SWITCHOVER_STATUS,PROTECTION_MODE   from v$database;

OPEN_MODE  SWITCHOVER_STATUS    PROTECTION_MODE
---------- -------------------- --------------------
MOUNTED    TO PRIMARY           MAXIMUM PERFORMANCE

 

 

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

 

 

SQL> select max(SEQUENCE#) from  v$archived_log;

 

--=========================================================

备库中执行

 

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> shutdown immdeiate
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2020192 bytes
Variable Size             176164000 bytes
Database Buffers          100663296 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL> select  OPEN_MODE from v$database;

OPEN_MODE
----------
READ WRITE

 

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

 

 

SQL> select  OPEN_MODE,SWITCHOVER_STATUS,PROTECTION_MODE   from v$database;

OPEN_MODE  SWITCHOVER_STATUS    PROTECTION_MODE
---------- -------------------- --------------------
READ WRITE TO STANDBY           MAXIMUM PERFORMANCE

 

 

SQL> select max(SEQUENCE#) from  v$archived_log;

MAX(SEQUENCE#)
--------------
            31

 

 

SQL> alter system switch logfile;

System altered.

SQL> select max(SEQUENCE#) from  v$archived_log;

MAX(SEQUENCE#)
--------------
            32

 

---=====================

检查切换后的情况

 

返回现在的备库 （曾今的主库）

 

SQL> select max(SEQUENCE#) from  v$archived_log;

MAX(SEQUENCE#)
--------------
            32

 

 

--===================================
附上主备库的initJZQ.ora

 

JZQ.__db_cache_size=113246208
JZQ.__java_pool_size=4194304
JZQ.__large_pool_size=4194304
JZQ.__shared_pool_size=155189248
JZQ.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/JZQ/adump'
*.background_dump_dest='/u01/app/oracle/admin/JZQ/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/JZQ/controlfile/control01.ctl','/u01/app/oracle/oradata/JZQ/controlfile/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/JZQ/cdump'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='JZQ'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='JZQ_PD'
*.FAL_CLIENT='JZQ_PD'
*.FAL_SERVER='JZQ_ST'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(JZQ_PD,JZQ_ST)'
*.log_archive_dest_1='location=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JZQ_PD'
*.log_archive_dest_2='SERVICE=JZQ_ST LGWR ASYNC DB_UNIQUE_NAME=JZQ_ST'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.nls_language='AMERICAN'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sga_target=283115520
*.standby_archive_dest='/u01/archivelog'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/JZQ/udump'

 

--========================================================

备库

JZQ.__db_cache_size=100663296
JZQ.__java_pool_size=4194304
JZQ.__large_pool_size=4194304
JZQ.__shared_pool_size=167772160
JZQ.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/JZQ/adump'
*.background_dump_dest='/u01/app/oracle/admin/JZQ/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/JZQ/controlfile/control01.ctl','/u01/app/oracle/oradata/JZQ/controlfile/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/JZQ/cdump'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='JZQ'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='JZQ_ST'
*.FAL_CLIENT='JZQ_ST'
*.FAL_SERVER='JZQ_PD'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(JZQ_PD,JZQ_ST)'
*.log_archive_dest_1='location=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JZQ_ST'
*.log_archive_dest_2='SERVICE=JZQ_PD LGWR ASYNC DB_UNIQUE_NAME=JZQ_PD'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.nls_language='AMERICAN'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sga_target=283115520
*.standby_archive_dest='/u01/archivelog'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/JZQ/udump'