物理standby创建
1.确认主库处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/wjldg/archive
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
2.将primary数据库置为forcelogging模式
SQL> alter database force logging;
Database altered.
3.创建standby数据库控制文件
SQL> alter database create standby controlfile as '/opt/oracle/standby/wjldg01.ctl';
Database altered.
4.创建primary数据库客户端初始化参数文件
SQL> create pfile from spfile;
File created.
在/opt/oracle/product/9iR2/dbs下生成：initwjldg.ora
mkdir bdump cdump create pfile scripts udump
oracle@dg2:~/oradata> scp -P 88 -r 10.230.17.246:/opt/oracle/oradata/wjldg .
oracle@dg2:~/product/9iR2/dbs> scp -P 88 10.230.17.246:/opt/oracle/product/9iR2/dbs/*wjl* .
oracle@dg2:~> scp -P 88 -r 10.230.17.246:/opt/oracle/standby .
oracle@dg2:~/oradata/wjldg> cp wjldg01.ctl standby01.ctl
oracle@dg2:~/oradata/wjldg> cp wjldg01.ctl standby02.ctl
oracle@dg2:~/oradata/wjldg> cp wjldg01.ctl standby03.ctl
oracle@dg1:~/product/9iR2/dbs> vi initwjldg.ora
*.background_dump_dest='/opt/oracle/admin/wjldg/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/opt/oracle/oradata/wjldg/control01.ctl','/opt/oracle/oradata/wjldg/control02.ctl','/opt/oracle/oradata/wjldg/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/wjldg/cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='wjldg'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='wjldg'
*.java_pool_size=0
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/wjldg/archive'
*.log_archive_dest_2='SERVICE=standby LGWR'
*.log_archive_dest_state_2=enable
*.log_archive_format='%t_%s.dbf'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=115343360
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/wjldg/udump'
oracle@dg2:~/product/9iR2/dbs> cat stdinit.ora
*.background_dump_dest='/opt/oracle/admin/wjldg/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/opt/oracle/oradata/wjldg/standby01.ctl','/opt/oracle/oradata/wjldg/standby02.ctl','/opt/oracle/oradata/wjldg/standby03.ctl'
*.core_dump_dest='/opt/oracle/admin/wjldg/cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='wjldg'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='wjldg'
*.java_pool_size=0
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/wjldg/archive'
*.log_archive_format='%t_%s.dbf'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=115343360
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/wjldg/udump'
*.fal_server='primary'
*.fal_client='standby'
DB_FILE_NAME_CONVERT=('/opt/oracle/oradata/wjldg','/opt/oracle/oradata/wjldg')
LOG_FILE_NAME_CONVERT=('/opt/oracle/oradata/wjldg','/opt/oracle/oradata/wjldg')
*.standby_file_management=auto
*.standby_archive_dest='/opt/oracle/oradata/wjldg/archive'
LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/oradata/wjldg/archive'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
oracle@dg1:~/product/9iR2/network/admin> cat tnsnames.ora
# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9iR2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = )
)
)
WJLDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.230.17.246)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wjldg)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.230.17.247)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wjldg)
)
)
oracle@dg1:~/product/9iR2/network/admin> cat listener.ora
# LISTENER.ORA Network Configuration File: /opt/oracle/product/9iR2/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PL***tProc)
(ORACLE_HOME = /opt/oracle/product/9iR2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = wjldg)
(ORACLE_HOME = /opt/oracle/product/9iR2)
(SID_NAME = wjldg)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
)
)
oracle@dg1:~/product/9iR2/network/admin>
oracle@dg2:~/product/9iR2/network/admin> cat tnsnames.ora
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.230.17.246)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wjldg)
)
)
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.230.17.247)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wjldg)
)
)
wjldg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.230.17.247)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wjldg)
)
)
oracle@dg2:~/product/9iR2/network/admin> cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PL***tProc)
(ORACLE_HOME = /opt/oracle/product/9iR2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = wjlcn)
(ORACLE_HOME = /opt/oracle/product/9iR2)
(SID_NAME = wjldg)
)
)
oracle@dg2:~/product/9iR2/network/admin>
SQL> startup pfile='/opt/oracle/product/9iR2/dbs/initwjldg.ora';
ORACLE instance started.
Total System Global Area 202865936 bytes
Fixed Size 740624 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
将standby启动到nomount状态
SQL> startup nomount pfile='/opt/oracle/product/9iR2/dbs/stdinit.ora';
ORACLE instance started.
Total System Global Area 202865936 bytes
Fixed Size 740624 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
SQL>
SQL> alter database mount standby database;
Database altered.
SQL> alter database add standby logfile group 4
2 ('/opt/oracle/oradata/wjldg/stdy_redo04.log') size 102400k;
Database altered.
SQL> alter database add standby logfile group 5
2 ('/opt/oracle/oradata/wjldg/stdy_redo05.log') size 102400k;
Database altered.
SQL> alter database add standby logfile group 6
2 ('/opt/oracle/oradata/wjldg/stdy_redo06.log') size 102400k;
Database altered.
SQL> alter database add standby logfile group 7
2 ('/opt/oracle/oradata/wjldg/stdy_redo07.log') size 102400k;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
1 STALE ONLINE
/opt/oracle/oradata/wjldg/redo01.log
3 STALE ONLINE
/opt/oracle/oradata/wjldg/redo03.log
2 ONLINE
/opt/oracle/oradata/wjldg/redo02.log
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
4 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo04.log
5 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo05.log
6 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo06.log
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
7 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo07.log
7 rows selected.
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
到这里standby database已经创建完成。
下面验证standby database是否正常工作。
启动primary database，
执行日志切换：
SQL> alter system switch logfile;
System altered.
在standby上查询v$archived_log，
SQL> select sequence#,applied from v$archived_log;
日常管理：
启动到只读方式
startup nomount pfile='/u01/product/oracle9i/dbs/initora81db.ora'
alter database mount standby database
alter database open read only
如果在管理恢复模式下到只读模式
alter database recover managed standby database cancel;
alter database open read only;
从只读方式到管理恢复方式
recover managed standby database disconnect from session;
SQL> startup nomount pfile='/opt/oracle/product/9iR2/dbs/stdinit.ora';
alter database mount standby database;
启动standby
startup mount;
启动redo应用
alter database recover managed standby database disconnect from session;
启动实时应用
alter database recover managed standby database using current logfile disconnect from session;
停止standby
正常情况下，我们停止也应该是先停止redo应用
alter database recover managed standby database cancle;
然后再停止standby数据库
shutdown immediate;
物理standby的角色转换
转换standby数据库到maximize performance 执行下列语句即可：
alter database set standby database to maximize performance;
1.检查是否支持switchover操作
登录primary数据库，查询v$database视图的switchover_status列
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
2.启动switchover primary数据库操作 DG1
首先将primary转换为standby角色
SQL> alter database commit to switchover to physical standby;
Database altered.
执行后，primary数据库将会转换为standby数据库，并自动备份控制文件到trace。
wjldg_ora_15328.trc
3.重启动到mount 原primary数据库操作 DG1
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 202865936 bytes
Fixed Size 740624 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
SQL> alter database mount standby database;
Database altered.
4.检查是否支持switchover操作 DG2
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO PRIMARY
5.转换角色到primary DG2
通过下列语句转换standby到primary角色：
SQL> alter database commit to switchover to primary;
Database altered.
6.完成转换，打开新的primary数据库 DG2
SQL> create spfile from pfile='/opt/oracle/product/9iR2/dbs/stdinit.ora';
File created.
SQL>
SQL>
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 202865936 bytes
Fixed Size 740624 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL>
7.验证
show parameter db_unique (10g)
alter system switch logfile;
select max(sequence#) from v$archived_log;
转换成功
DG1上也应该配置standby logfile等相对应的文件
********************************************************************************
SQL> alter database add standby logfile group 4
('/opt/oracle/oradata/wjldg/stdy_redo04.log') size 102400k;
SQL> alter database add standby logfile group 5
('/opt/oracle/oradata/wjldg/stdy_redo05.log') size 102400k;
SQL> alter database add standby logfile group 6
('/opt/oracle/oradata/wjldg/stdy_redo06.log') size 102400k;
SQL> alter database add standby logfile group 7
('/opt/oracle/oradata/wjldg/stdy_redo07.log') size 102400k;
DG1
SQL> select * from v$logfile
2 ;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
1 STALE ONLINE
/opt/oracle/oradata/wjldg/redo01.log
3 STALE ONLINE
/opt/oracle/oradata/wjldg/redo03.log
2 ONLINE
/opt/oracle/oradata/wjldg/redo02.log
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
4 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo04.log
5 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo05.log
6 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo06.log
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
7 STANDBY
/opt/oracle/oradata/wjldg/stdy_redo07.log
7 rows selected.
SQL> startup pfile='/opt/oracle/product/9iR2/dbs/stdinit.ora';
ORACLE instance started.
Total System Global Area 202865936 bytes
Fixed Size 740624 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL>
************************************************************************************
物理standby的failover
1.检查归档文件是否连续
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;
no rows selected
2.检查归档文件是否完整
分别在primary、standby执行
SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;
THREAD# A
---------- ----------
1 25
3.启动failover
SQL> alter database recover managed standby database finish;
Database altered.
4.切换物理standby角色为primary
******************************************************************************************
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
Switchover: Media recovery required - standby not in limbo
ORA-16139 signalled during: alter database commit to switchover to primary...
Tue Apr 26 09:08:16 2011
ARC1: Evaluating archive log 4 thread 1 sequence 28
ARC1: Beginning to archive log 4 thread 1 sequence 28
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/wjldg/archive/1_28.dbf'
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
17 YES
18 YES
19 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
22 rows selected.
********************************************************************************************
阅读(2070) | 评论(0) | 转发(0) |
