主库参数文件内容如下:
test.__db_cache_size=130023424
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=75497472
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/test/controlfile/current.261.733318571','+DATA/test/controlfile/current.260.733318571'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=214958080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
DB_UNIQUE_NAME=master
LOG_ARCHIVE_CONFIG='DG_CONFIG=(master,standby)'
LOG_ARCHIVE_DEST_2='SERVICE=standby_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=standby_db
FAL_CLIENT=master_db
STANDBY_FILE_MANAGEMENT=AUTO
备库参数文件内如下:
test.__db_cache_size=130023424
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=75497472
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/standby/controlfile/backup.273.735399141','+DATA/standby/controlfile/backup.277.735399141'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=214958080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(master,standby)'
LOG_ARCHIVE_DEST_2='SERVICE=master_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=master'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=master_db
FAL_CLIENT=standby_db
STANDBY_FILE_MANAGEMENT=AUTO
主库LISTENER设置(包括ASM的相关设置):
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = test)
)
(SID_DESC =
(SID_NAME = +ASM)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = db_master)(PORT = 1521))
)
)
LISTENERASM =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = db_master)(PORT = 1521))
)
)
客户端TNS设置(包括连接ASM的相关设置)
db_# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
MASTER_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
STANDBY_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)
ASM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
)
)
C:\Documents and Settings\Administrator>sqlplus sys/lyf625@asm as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 12月 8 11:41:49 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show sga;
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
SQL>(连接到ASM实例)
-> rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 10 18:01:53 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: test (not mounted)
RMAN> run {
2> set controlfile autobackup format for device type disk to '+DATA/test/controlfile/%F';
3> restore controlfile from '/u01/app/control.bak';
4> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog
Starting restore at 10-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/standby/controlfile/backup.256.734724225
output filename=+DATA/standby/controlfile/backup.257.734724225
Finished restore at 10-NOV-10
RMAN> exit
Recovery Manager complete.
db_Standby-> exit
exit
SQL> alter database mount;
Database altered.
db_Standby-> rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 10 18:04:29 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=2031465002, not open)
RMAN> restore database;
Starting restore at 10-NOV-10
Starting implicit crosscheck backup at 10-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=120 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 10-NOV-10
Starting implicit crosscheck copy at 10-NOV-10
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 10-NOV-10
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/test/datafile/system.256.733318485
restoring datafile 00002 to +DATA/test/datafile/undotbs1.258.733318485
restoring datafile 00003 to +DATA/test/datafile/sysaux.257.733318485
restoring datafile 00004 to +DATA/test/datafile/users.259.733318485
restoring datafile 00005 to +DATA/test/datafile/example.269.733318631
restoring datafile 00006 to +DATA/test/datafile/ceshi.271.733405923
restoring datafile 00007 to +DATA/test/datafile/ceshi.272.733406097
restoring datafile 00008 to +DATA/test/datafile/ceshi.273.733406115
channel ORA_DISK_1: reading from backup piece /u01/app/0jlsq5cr_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/0jlsq5cr_1_1 tag=TAG20101112T075819
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 10-NOV-10
RMAN> exit
Recovery Manager complete.
db_Standby-> exit
exit
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Data Gurad 主库和备库的切换:
主库全部操作如下(切换后变为备库):
select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL>select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;
COUNT(*)
----------
1
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> shutdown
immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> SP2-0042: unknown command "immediate" - rest of line ignored.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 83888084 bytes
Database Buffers 130023424 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
89
SQL> alter database open read only;
Database altered.
SQL> select * from leviton;
NAME
----------
HELLO
leviton
Good
Bye
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from leviton;(验证切换后,在主库上添加的两条记录,在备库有没有生成?关于添加的两条记录请参看下面在主库上的操作)
NAME
----------
HELLO
leviton
Good
Bye
ok
standby
6 rows selected.
备库全部操作如下(切换后变为主库):
select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;
COUNT(*)
----------
2
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> alter database open;
Database altered.
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE
SQL> insert into leviton values('ok');(在切换后的主库上插入两条新纪录)
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
89
SQL> insert into leviton values('standby');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> commit;
Commit complete.
SQL> !
10g物理standby主备switchover方式切换详述
以下给大家展现一下10g物理standby主备之间通过switchover方式进行切换的详细步骤,供参考。
1、主库检查是否为“TO STANDBY”状态,若不是,需要重新启动一下主库(主库ora10g操作)
sys@ora10g> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
sys@ora10g> startup force ;
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1266056 bytes
Variable Size 83889784 bytes
Database Buffers 16777216 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
sys@ora10g> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
2.将primary转换为standby角色(主库ora10g操作)
sys@ora10g> alter database commit to switchover to physical standby;
Database altered.
3.原主库重启动到mount状态(原主库ora10g操作)
NotConnected@> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
NotConnected@>
NotConnected@> startup mount;
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1266056 bytes
Variable Size 88084088 bytes
Database Buffers 12582912 bytes
Redo Buffers 2924544 bytes
Database mounted.
4.检查原备库是否为“TO PRIMARY”状态,如果为“SWITCHOVER PENDING”状态,需要先进行一下恢复再切换 (待切换备库ora10gdg操作)
ora10g@linux5 /home/oracle$ export ORACLE_SID=ora10gdg
ora10gdg@linux5 /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 28 08:26:53 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
NotConnected@> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING
NotConnected@> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
NotConnected@> alter database recover managed standby database disconnect from session;
Database altered.
NotConnected@> alter database recover managed standby database cancel;
Database altered.
NotConnected@> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
5.原备库转换角色到primary (待切换备库ora10gdg操作)
NotConnected@> alter database commit to switchover to primary;
Database altered.
6.主备切换完成,open新的primary数据库 (待切换备库ora10gdg操作)
NotConnected@> alter database open;
Database altered.
7.最后验证阶段
1).新的primary库在sec用户创建一个新表test_new
sec@ora10g> create table test_new (a int);
Table created.
sec@ora10g> insert into test_new values ( 100 );
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from test_new;
A
----------
100
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system switch logfile;
System altered.
sys@ora10g> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
69
2).查看新standby库,验证切换是否成功
ora10gdg@linux5 /home/oracle$ export ORACLE_SID=ora10g
ora10g@linux5 /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 28 08:55:09 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
NotConnected@> alter database recover managed standby database disconnect from session;
Database altered.
NotConnected@> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
69
NotConnected@> alter database recover managed standby database cancel;
Database altered.
NotConnected@> alter database open read only;
Database altered.
NotConnected@> conn sec/sec
Connected.
sec@ora10g> select * from test_new;
A
----------
100
OK!到此主备之间的switchover方式切换成功。
secooler
09.03.27
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13024285/viewspace-678214/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13024285/viewspace-678214/