创建物理备用数据库
创建物理备用数据库
2012年4月12日
11:22
|
|
3.1 Preparing the Primary Database for Standby Database Creation |
|||||||||
|
|
3.1.1 Enable Forced Logging 强制将数据库变为logging模式
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
3.1.2 Create a Password File 创建口令文件
SQL> alter user sys identified by oracle;
User altered.
SQL> ! [oracle@solaris10 ~]$cd $ORACLE_HOME/dbs [oracle@solaris10 dbs]$orapwd file=orapwanny password=oracle entries=3 force=y
3.1.3 Configure a Standby Redo Log 创建备用redo日志组 （最大保护、最大可用模式）日志组，日志组个数大于或等于online redo group A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.
You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.
SQL> alter database add standby logfile group 5 2 ('/disk3/oradata/anny/redo05a.rdo','/disk1/oradata/anny/redo05b.rdo','/disk2/oradata/anny/redo05c.rdo') size 100m;
Database altered.
1 alter database add standby logfile group 6 2* ('/disk3/oradata/anny/redo06a.rdo','/disk1/oradata/anny/redo06b.rdo','/disk2/oradata/anny/redo06c.rdo') size 100m SQL> /
Database altered. 1 alter database add standby logfile group 7 2* ('/disk3/oradata/anny/redo07a.rdo','/disk1/oradata/anny/redo07b.rdo','/disk2/oradata/anny/redo07c.rdo') size 100m SQL> /
Database altered
SQL> alter database add standby logfile group 8 2 ('/disk3/oradata/anny/redo08a.rdo','/disk1/oradata/anny/redo08b.rdo','/disk2/oradata/anny/redo08c.rdo') size 100m;
Database altered.
查看一下是否已经建立成功： SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS ---------- ---------- ---------- --------- ------------------------------ 5 0 0 YES UNASSIGNED 6 0 0 YES UNASSIGNED 7 0 0 YES UNASSIGNED 8 0 0 YES UNASSIGNED
3.1.4 Set Primary Database Initialization Parameters 设置主数据库的参数文件
Primary Database: Primary Role Initialization Parameters
DB_NAME=chicago ——归档日志到本地的位置 ——归档日志到远程
Primary Database: Standby Role Initialization Parameters
FAL_SERVER=boston ——datafile 转换目录
当beijing是主数据库时
DB_UNIQUE_NAME=beijing LOG_ARCHIVE_DEST_2= 'SERVICE=tianjin LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tianjin'
当beijing是备用数据库时
FAL_SERVER=tianjin FAL_CLIENT=beijing DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/tianjin/','/u01/app/oracle/oradata/anny/' LOG_FILE_NAME_CONVERT='/disk1/oradata/tianjin/','/disk1/oradata/anny/' LOG_FILE_NAME_CONVERT='/disk2/oradata/tianjin/','/disk2/oradata/anny/' LOG_FILE_NAME_CONVERT='/disk3/oradata/tianjin/','/disk3/oradata/anny/'
SQL> create pfile from spfile; ——先更新一下pfile File created.
将以上信息添加到initanny.ora 参数文件中
将主库启动到nomount 状态，检查instance 初始化文件
SQL> startup pfile=$ORACLE_HOME/dbs/initanny.ora nomount ORACLE instance started.
Total System Global Area 314572800 bytes Fixed Size 1279964 bytes Variable Size 62916644 bytes Database Buffers 247463936 bytes Redo Buffers 2912256 bytes
SQL> show parameter arch
NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ archive_lag_target integer 0 log_archive_config string DG_CONFIG=(beijing,tianjin) log_archive_dest string log_archive_dest_1 string LOCATION=/disk1/arch/anny VA LID_FOR=(ALL_LOGFILES,ALL_ROLE S) DB_UNIQUE_NAME=beijing log_archive_dest_10 string log_archive_dest_2 string SERVICE=tianjin LGWR ASYNC V ALID_FOR=(ONLINE_LOGFILES,PRIM ARY_ROLE) DB_UNIQUE_NAME=tia njin SQL> show parameter name
NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_file_name_convert string /u01/app/oracle/oradata/tianji n/, /u01/app/oracle/oradata/anny/
db_name string anny db_unique_name string beijing global_names boolean FALSE instance_name string anny lock_name_space string log_file_name_convert string /u01/app/oracle/oradata/tianjin/, /u01/app/oracle/oradata/anny/
service_names string beijing
3.1.5 Enable Archiving 使归档模式生效
SQL> SHUTDOWN IMMEDIATE; |
|||||||||
|
|
3.2 Step-by-Step Instructions for Creating a Physical Standby Database |
|||||||||
|
|
3.2.1 Create a Backup Copy of the Primary Database Datafiles
RMAN> run{ 2> shutdown immediate; 3> startup mount; 4> allocate channel c1 type disk; 5> allocate channel c2 type disk; 6> backup database format '/disk1/rman/anny/%d_%s.bak'; 7> alter database open; 8> }
3.2.4 Copy Files from the Primary System to the Standby System 将主库数据文件（数据库正常关闭），拷贝到备库数据文件所在的目录 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> ! [oracle@solaris10 ~]$cd /u01/app/oracle/oradata/ [oracle@solaris10 oradata]$mkdir -p /u01/app/oracle/oradata/tianjin/ [oracle@solaris10 oradata]$cp /u01/app/oracle/oradata/anny/*.dbf /u01/app/oracle/oradata/tianjin/
3.2.2 Create a Control File for the Standby Database
主库在mount下，生成备库的控制文件
SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/tianjin/control01.ctl';
Database altered.
——如果控制文件有很多个，就只执行一次该命令，将其他的复制过去就行
'/u01/app/oracle/oradata/anny/control01.ctl','/disk1/oradata/tianjin/control02.ctl','/disk2/oradata/tianjin/control03.ctl'
3.2.3 Prepare an Initialization Parameter File for the Standby Database
创建备库的初始化文件，通过主库的初始化文件生成
[oracle@solaris10 dbs]$cp initanny.ora inittianjin.ora
或者SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;
然后配置inittianjin.ora ，修改以下参数 *.control_files='/u01/app/oracle/oradata/tianjin/control01.ctl'，'/disk1/oradata/tianjin/control02.ctl','/disk2/oradata/tianjin/control03.ctl' DB_UNIQUE_NAME=tianjin LOG_ARCHIVE_CONFIG='DG_CONFIG=(beijing,tianjin)' LOG_ARCHIVE_DEST_1= 'LOCATION=/disk1/arch/tianjin VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tianjin' LOG_ARCHIVE_DEST_2= 'SERVICE=tianjin LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=beijing'
FAL_SERVER=beijing FAL_CLIENT=tianjin DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/anny/','/u01/app/oracle/oradata/tianjin/' LOG_FILE_NAME_CONVERT='/disk1/oradata/anny/','/disk1/oradata/tianjin/' LOG_FILE_NAME_CONVERT='/disk2/oradata/anny/','/disk2/oradata/tianjin/' LOG_FILE_NAME_CONVERT='/disk3/oradata/anny/','/disk3/oradata/tianjin/' STANDBY_FILE_MANAGEMENT=AUTO
3.2.5 Set Up the Environment to Support the Standby Database
设置备用库的启动环境
Step 1 Create a Windows-based service. Step 2 Create a password file.
添加备库的口令文件（注意：sys用户的口令和主库一致）
[oracle@solaris10 dbs]$orapwd file=orapwtianjin password=oracle entries=3
配置网络（listener 和 tnsnames）
Step 3 Configure listeners for the primary and standby databases——配置监听并启动监听
[oracle@solaris10 dbs]$cd /u01/app/oracle/product/10.2.0/db_1/network/admin/ [oracle@solaris10 admin]$ls listener.ora samples shrept.lst sqlnet.log tnsnames.ora
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = solaris10)(PORT = 1521)) ) )
[oracle@solaris10 admin]$lsnrctl stop [oracle@solaris10 admin]$lsnrctl start [oracle@solaris10 admin]$lsnrctl status
LSNRCTL for Solaris: Version 10.2.0.2.0 - Production on 12-APR-2012 17:47:53
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=solaris10)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 10.2.0.2.0 - Production Start Date 12-APR-2012 17:45:20 Uptime 0 days 0 hr. 2 min. 33 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris10)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "beijing" has 1 instance(s). Instance "anny", status BLOCKED, has 1 handler(s) for this service... Service "beijing_XPT" has 1 instance(s). Instance "anny", status BLOCKED, has 1 handler(s) for this service... The command completed successfully
Step 4 Create Oracle Net service names——配置tnsnames.ora 添加如下信息
beijing= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.17)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = beijing) ) )
tianjin = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.17)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = tianjin ) ) )
Step 5 Create a server parameter file for the standby database.
3.2.6 Start the Physical Standby Database 启动备用库，并检测网络环境
SQL> startup nomount
SQL> create spfile from pfile;
备库 SQL> startup mount;
[oracle@solaris10 ~]$sqlplus sys/oracle@beijing as sysdba SQL*Plus: Release 10.2.0.2.0 - Production on Thu Apr 12 19:53:18 2012 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
主库 SQL> startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1279964 bytes Variable Size 62916644 bytes Database Buffers 247463936 bytes Redo Buffers 2912256 bytes Database mounted.
SQL> ! [oracle@solaris10 ~]$sqlplus 'sys/oracle@tianjin as sysdba' SQL*Plus: Release 10.2.0.2.0 - Production on Thu Apr 12 19:40:01 2012 Copyright (c) 1982, 2005, Oracle. All Rights Reserved Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open; Database altered.
告警日志信息： alter database open Fri Apr 13 15:07:34 2012 …… Fri Apr 13 15:07:40 2012 ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** …… Completed: alter database open Fri Apr 13 15:09:40 2012 Shutting down archive processes Fri Apr 13 15:09:45 2012 ARCH shutting down ARC2: Archival stopped
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- --------------- ------------- ------------------- 1 1 2 104857600 3 YES INACTIVE 850760 2012-04-13 12:19:37 4 1 4 104857600 3 YES INACTIVE 855219 2012-04-13 13:54:33 3 1 3 104857600 3 YES INACTIVE 851665 2012-04-13 12:47:43 2 1 5 104857600 3 NO CURRENT 856511 2012-04-13 15:07:35
SQL> select name from v$archived_log; NAME -------------------------------------------------- /disk1/arch/anny/1_2_777487681.dbf /disk1/arch/anny/1_3_777487681.dbf /disk1/arch/anny/1_4_777487681.dbf /disk1/arch/anny/1_5_777487681.dbf
主库信息： SQL> select name ,PROTECTION_MODE,GUARD_STATUS,SWITCHOVER_STATUS ,DB_UNIQUE_NAME,DATABASE_ROLE from v$database; NAME PROTECTION_MODE GUARD_STATUS SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE ----- -------------------- --------------------- -------------------- --------------- -------------------- ANNY MAXIMUM PERFORMANCE NONE SESSIONS ACTIVE beijing PRIMARY
备库信息： SQL> select name ,PROTECTION_MODE,GUARD_STATUS,SWITCHOVER_STATUS ,DB_UNIQUE_NAME,DATABASE_ROLE from v$database; NAME PROTECTION_MODE GUARD_STATUS SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE ---------- -------------------- --------------------- -------------------- -------------------- -------------------- ANNY MAXIMUM PERFORMANCE NONE SESSIONS ACTIVE tianjin PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
告警日志信息：
在主库做日志切换，查看日志信息 ALTER SYSTEM SWITCH LOGFILE; Media Recovery Log /disk1/arch/tianjin/arch_1_5_780522294.log Media Recovery Waiting for thread 1 sequence 6 (in transit) Fri Apr 13 20:03:20 2012 Primary database is in MAXIMUM PERFORMANCE mode RFS[2]: Successfully opened standby log 5: '/disk3/oradata/anny/redo05a.rdo' Fri Apr 13 20:03:22 2012 Media Recovery Log /disk1/arch/tianjin/arch_1_6_780522294.log Media Recovery Waiting for thread 1 sequence 7 (in transit)
3.2.7 Verify the Physical Standby Database Is Performing Properly
启动主库进行测试备用库的可用性 |
|||||||||
|
|
3.3 Post-Creation Steps |
|||||||||
|
|
SQL> alter database open; alter database open * ERROR at line 1: ORA-00314: log 2 of thread 1, expected sequence# 5 doesn't match 0 ORA-00312: online log 2 thread 1: '/disk2/oradata/anny/redo02c.log' ORA-00314: log 2 of thread 1, expected sequence# 5 doesn't match 0 ORA-00312: online log 2 thread 1: '/disk1/oradata/anny/redo02b.log' ORA-00314: log 2 of thread 1, expected sequence# 5 doesn't match 0 ORA-00312: online log 2 thread 1: '/disk3/oradata/anny/redo02a.log' |
|||||||||
|
|
|
|||||||||
|
|
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 将角色进行切换 将主的变成备的，因为主备相差一个日志量，主的先将当前日志归档 |
|||||||||
|
|
|
|||||||||
|
|
|
|||||||||
|
|
|
|||||||||
|
|
|
|||||||||
|
|
|
|||||||||
|
|
|
