创建物理备用数据库（完美）
创建物理备用数据库（李）
2012年4月13日
11:29
|
|
|
|
|
1、备份主库（冷备）
2、将数据设置为强制logging
01:27:05 SQL> shutdown immediate;
Database closed. Database dismounted. ORACLE instance shut down.
01:27:32 SQL> startup mount
ORACLE instance started.
Total System Global Area 419430400 bytes Fixed Size 1219760 bytes Variable Size 58721104 bytes Database Buffers 356515840 bytes Redo Buffers 2973696 bytes Database mounted.
01:27:45 SQL> alter database force logging;
Database altered.
01:28:33 SQL> alter database open;
Database altered.
3、添加standby redo（最大保护、最大可用模式）日志组，日志组个数大于或等于online redo group
01:28:42 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 40 10485760 2 YES INACTIVE 147064 13-NOV-11 3 1 42 10485760 2 NO CURRENT 188701 13-NOV-11 2 1 41 10485760 2 YES INACTIVE 167776 13-NOV-11
01:30:04 SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stdredo04a.log' size 10m;
Database altered.
01:31:13 SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stdredo05a.log' size 10m;
Database altered.
01:31:19 SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stdredo06a.log' size 10m;
Database altered.
01:31:25 SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stdredo07a.log' size 10m;
Database altered.
01:31:31 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 40 10485760 2 YES INACTIVE 147064 13-NOV-11 3 1 42 10485760 2 NO CURRENT 188701 13-NOV-11 2 1 41 10485760 2 YES INACTIVE 167776 13-NOV-11
01:31:38 SQL> col member for a50 01:31:43 SQL> select group#,member from v$logfile;
GROUP# MEMBER ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/oradb/redo01a.log 2 /u01/app/oracle/oradata/oradb/redo02a.log 3 /u01/app/oracle/oradata/oradb/redo03a.log 1 /disk1/oradata/oradb/redo01b.log 2 /disk1/oradata/oradb/redo02b.log 3 /disk1/oradata/oradb/redo03b.log 4 /u01/app/oracle/oradata/oradb/stdredo04a.log 5 /u01/app/oracle/oradata/oradb/stdredo05a.log 6 /u01/app/oracle/oradata/oradb/stdredo06a.log 7 /u01/app/oracle/oradata/oradb/stdredo07a.log
01:31:50 SQL> desc v$standby_log; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- GROUP# NUMBER DBID VARCHAR2(40) THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER USED NUMBER ARCHIVED VARCHAR2(3) STATUS VARCHAR2(10) FIRST_CHANGE# NUMBER FIRST_TIME DATE LAST_CHANGE# NUMBER LAST_TIME DATE
01:32:16 SQL> select GROUP#,THREAD#,SEQUENCE# ,BYTES,STATUS ,FIRST_CHANGE# from v$standby_log;
GROUP# THREAD# SEQUENCE# BYTES STATUS FIRST_CHANGE# ---------- ---------- ---------- ---------- ---------- ------------- 4 0 0 10485760 UNASSIGNED 0 5 0 0 10485760 UNASSIGNED 0 6 0 0 10485760 UNASSIGNED 0 7 0 0 10485760 UNASSIGNED 0
4、将主库数据文件（数据库正常关闭） ，拷贝到备库数据文件所在的目录
01:33:07 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 01:33:36 SQL> ! [oracle@work dbs]$ cd [oracle@work ~]$ cd /u01/app/oracle/oradata/ [oracle@work oradata]$ mkdir oradbdg [oracle@work oradata]$ cp /u01/app/oracle/oradata/oradb/*.dbf /u01/app/oracle/oradata/oradbdg
5、生成备库的控制文件（主库在mount下）
01:37:43 SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/oradbdg/control01.ctl';
Database altered.
6、修改主库instance pfile或spfile，添加备库的信息
01:38:34 SQL> show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/10.2.0 /db_1/dbs/spfileoradb.ora
01:39:28 SQL> create pfile from spfile;
File created.
[oracle@work dbs]$ mkdir -p /disk1/oradata/oradbdg
[oracle@work dbs]$ vi initoradb.ora
-----------在initoradb.ora下添加以下信息
DB_UNIQUE_NAME=bjdb LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/disk1/arch/oradb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bjdb' ；
----------归档日志到本地的位置
LOG_ARCHIVE_DEST_2='SERVICE=shdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=shdb' ；
-------------归档日志到远程
LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_MAX_PROCESSES=3
FAL_SERVER=shdb ； -------------对方unique_name FAL_CLIENT=bjdb ; -------------本地 unique_name DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradbdg','/u01/app/oracle/oradata/oradb' ; LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradbdg','/u01/app/oracle/oradata/oradb','/disk1/oradata/oradbdg','/disk1/oradata/oradb' ； -------------redo 日志转换目录 STANDBY_FILE_MANAGEMENT=AUTO
7、将主库启动到nomount 状态，检查instance 初始化文件
01:52:26 SQL> conn /as sysdba Connected to an idle instance.
01:52:31 SQL> startup pfile=$ORACLE_HOME/dbs/initoradb.ora nomount
ORACLE instance started.
Total System Global Area 419430400 bytes Fixed Size 1219760 bytes Variable Size 58721104 bytes Database Buffers 356515840 bytes Redo Buffers 2973696 bytes
01:52:54 SQL> show parameter arch
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 log_archive_config string DG_CONFIG=(bjdb,shdb) log_archive_dest string log_archive_dest_1 string LOCATION=/disk1/arch/oradb VALID_FOR=(ALL_LOGFILES,ALL_ ROLES) DB_UNIQUE_NAME=bjdb log_archive_dest_10 string log_archive_dest_2 string SERVICE=shdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,P RIMARY_ROLE) DB_UNIQUE_NAME=shdb
01:54:12 SQL> show parameter name
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /u01/app/oradata/oradbdg, /u01 /app/oracle/oradata/oradb db_name string oradb db_unique_name string bjdb global_names boolean FALSE instance_name string oradb lock_name_space string log_file_name_convert string /u01/app/oradata/oradbdg, /u01 /app/oracle/oradata/oradb, /di sk1/oradata/oradbdg, /disk1/or adata/oradb service_names string bjdb
01:54:19 SQL> alter database mount;
Database altered.
8、配置网络（listener 和 tnsnames）
[oracle@work admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-NOV-2011 01:56:07
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 13-NOV-2011 01:55:39 Uptime 0 days 0 hr. 0 min. 28 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=work)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Services Summary... Service "bjdb" has 1 instance(s). Instance "oradb", status READY, has 1 handler(s) for this service... Service "bjdb_XPT" has 1 instance(s). Instance "oradb", status READY, has 1 handler(s) for this service... Service "plsextproc" has 1 instance(s). Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
配置tnsnames.ora 添加如下信息
bjdb = (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=192.168.8.240)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=bjdb)))
shdb = (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=192.168.8.240)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=shdb)))
9、添加备库的口令文件（注意：sys用户的口令和主库一致）
[oracle@work admin]$ cd ../../dbs [oracle@work dbs]$ orapwd file=orapworadbdg password=oracle entries=3
10、创建备库的初始化文件，通过主库的初始化文件生成
[oracle@work dbs]$ cp initoradb.ora initoradbdg.ora
然后配置initoradbdg.ora ，修改以下参数
*.control_files='/u01/app/oracle/oradata/oradbdg/control01.ctl' DB_UNIQUE_NAME=shdb LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)' LOG_ARCHIVE_DEST_1= 'LOCATION=/disk1/arch/oradbdg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=shdb'
LOG_ARCHIVE_DEST_2= 'SERVICE=bjdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bjdb' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_MAX_PROCESSES=10
FAL_SERVER=bjdb FAL_CLIENT=shdb DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradb','/u01/app/oracle/oradata/oradbdg' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradb','/u01/app/oracle/oradata/oradbdg','/disk1/oradata/oradb','/disk1/oradata/oradbdg' STANDBY_FILE_MANAGEMENT=AUTO
11、启动备库到nomount 状态
[oracle@work ~]$ export ORACLE_SID=oradbdg [oracle@work ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 13 02:03:58 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
02:03:58 SQL> 02:03:58 SQL> startup nomount; ORACLE instance started.
Total System Global Area 419430400 bytes Fixed Size 1219760 bytes Variable Size 58721104 bytes Database Buffers 356515840 bytes Redo Buffers 2973696 bytes 02:04:05 SQL> alter database mount;
Database altered.
12、测试网络环境，保证主库和备库可以互访
02:04:51 SQL> show parameter name ;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /u01/app/oradata/oradb, /u01/app/oracle/oradata/oradbdg
db_name string oradb db_unique_name string shdb global_names boolean FALSE instance_name string oradbdg lock_name_space string log_file_name_convert string /u01/app/oradata/oradb, /u01/app/oracle/oradata/oradbdg, /disk1/oradata/oradb, /disk1/oradata/oradbdg service_names string shdb
[oracle@work dbs]$ sqlplus sys/oracle@shdb as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 13 02:08:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
02:08:23 SQL>
[oracle@work dbs]$ sqlplus sys/oracle@bjdb as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 13 02:08:29 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
13、启动主库进行测试
02:09:19 SQL> alter database open;
Database altered.
02:09:35 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 43 10485760 2 YES INACTIVE 191448 13-NOV-11 3 1 45 10485760 2 NO CURRENT 191565 13-NOV-11 2 1 44 10485760 2 YES INACTIVE 191450 13-NOV-11
02:09:50 SQL> select name from v$archived_log;
NAME ------------------------------------------------------------------------------------------------------------------------ /disk1/arch/oradb/arch_1_41_767058760.log /disk1/arch/oradb/arch_1_42_767058760.log /disk1/arch/oradb/arch_1_43_767058760.log /disk1/arch/oradb/arch_1_44_767058760.log
---------主库信息
02:12:28 SQL> select name ,PROTECTION_MODE,GUARD_STATUS,SWITCHOVER_STATUS ,DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
NAME PROTECTION_MODE GUARD_S SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE --------- -------------------- ------- -------------------- ------------------------------ ---------------- ORADB MAXIMUM PERFORMANCE NONE TO STANDBY bjdb PRIMARY
---------备库信息
02:10:41 SQL> select name ,PROTECTION_MODE,GUARD_STATUS,SWITCHOVER_STATUS ,DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
NAME PROTECTION_MODE GUARD_S SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE --------- -------------------- ------- -------------------- ------------------------------ ---------------- ORADB MAXIMUM PERFORMANCE NONE RECOVERY NEEDED shdb PHYSICAL STANDBY
14、备库做recover，应用归档日志
|
|
|
|
