主从两台主机hosts配置 192.168.1.197 oradb1（从） 192.168.1.198 oradb2(主） 二:搭建数据库 主库的操作： 1.确认主库参数 SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE FOR
OREO READ WRITE PRIMARY ARCHIVELOG YES
SQL>show parameter name
NAME TYPE VALUE
db_file_name_convert string db_name string oracle9i db_unique_name string oradb2 global_names boolean FALSE instance_name string oradb2 lock_name_space string log_file_name_convert string /data/ora11g/, /data/ora11g/ service_names string oradb2 2.设置数据库的归档模式 SQL>archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /data/ora11g/oradata/oracle9i/archive/ Oldest online log sequence 74 Next log sequence to archive 76 Current log sequence 76 如果归档和force logging未开启执行下面操作 归档模式的设置： 1、shutdown immediate； 2、alter database mount; 3、alter database archive log； 4、alter database open； 3.开启force logging SQL> alter database force logging; SQL> select FORCE_LOGGING from v$database;
FORCE_
YES
SQL> select name,DB_UNIQUE_NAME,database_role,log_mode,force_logging from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE LOG_MODE FOR
ORACLE9I oradb2 PRIMARY ARCHIVELOG YES
4.配置监听 [root@oradb2 rman]# vim /data/ora11g/product/11.2.0/db_1/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME =oradb2) (ORACLE_HOME = /data/ora11g/product/11.2.0/db_1) (SID_NAME = oradb2) ) )
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.198)(PORT = 1521)) )
ADR_BASE_LISTENER = /data/ora11g
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF INBOUND_CONNECT_TIMEOUT_LISTENER = 5 DIAG_ADR_ENABLED_LISTENER = OFF
#TRACE_TIMESTAMP_LISTENER=true #TRACE_LEVEL_LISTENER=16
5.配置主库的TNSNAMES.ORA和密码文件并传到备库 [root@oradb2 rman]# vim /data/ora11g/product/11.2.0/db_1/network/admin/tnsnames.ora
tnsnames.ora Network Configuration File: /u01/app/oracle/product/OraDb11g_home1/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
ora11g_1.197 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.197)(PORT = 1521))
)
(CONNECT_DATA =
(SID = oradb2)
(SERVER = DEDICATED)
)
)
ora11g_1.198 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.198)(PORT = 1521))
)
(CONNECT_DATA =
(SID = oradb2)
(SERVER = DEDICATED)
)
)
密码文件在
/data/ora11g/product/11.2.0/db_1/dbs/orapworadb2
如果忘记密码可以修改密码:
orapwd file='/data/ora11g/product/11.2.0/db_1/dbs/orapworadb2' password=xxxxx
通过ftp，scp等传输监听文件和密码到备库；
6.修改主库参数
个别参数说明:
db_unique_name='主库唯一名'
log_archive_config='dg_config=(主库唯一名,从库唯一名)'
log_archive_dest_1='location=本地归档路径 VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=主库唯一名' --就是说不管你是主库还是备库，都把你的online放到“本地归档路径”里面去
log_archive_dest_2='service=去从库的TNS valid_for=(online_logfiles,primary_role) db_unique_name=从库唯一名' --当角色是主库的时候，使用"去从库的tns“发送online_redolog
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
standby_file_management='auto'
主库参数在线修改:(主要用于在线搭建，主库不停机;如果可以停机,先create pfile from spfile生成/data/ora11g/product/11.2.0/db_1/dbs/initoradb2.ora文件直接修改，然后使用pfile启动，也可以如下 CREATE SPFILE FROM PFILE;
，STARTUP OPEN启动 )
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradb2,oradb1)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/data/ora11g/oradata/oracle9i/archive/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ora11g_1.197 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb1';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';#配置从库后再开启
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
ALTER SYSTEM SET FAL_SERVER=ora11g_1.197;
ALTER SYSTEM SET FAL_CLIENT=ora11g_1.198;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/data/ora11g/oradata','/data/ora11g/oradata' scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/data/ora11g','/data/ora11g' scope=spfile;
7.生成主库的initoradb2.ora，传到从库做修改
create pfile from spfile;--这里创建pfile是为了做一些主库参数的配置，并且还得拷贝到备库再次修改成备库的配置
oracle9i.__db_cache_size=16374562816
oracle9i.__oracle_base='/data/ora11g'#ORACLE_BASE set from environment
oradb2.__oracle_base='/data/ora11g'#ORACLE_BASE set from environment
oracle9i._shared_pool_size=1342177280
*.audit_file_dest='/data/ora11g/admin/oracle9i/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/ora11g/oradata/oracle9i/control01.ctl','/data/ora11g/oradata/oracle9i/control02.ctl'
*.db_block_size=16384
*.db_cache_size=16777216000
*.db_domain=''
*.db_file_name_convert='/data/ora11g/oradata','/data/ora11g/oradata'
*.db_flashback_retention_target=60
*.db_name='oracle9i'
*.db_recovery_file_dest='/data/ora11g/flashback'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='oradb2'
*.diagnostic_dest='/data/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle9iXDB)'
*.fal_client='ora11g_1.198'
*.fal_server='ora11g_1.197'
*.java_pool_size=104857600
*.large_pool_size=209715200
*.log_archive_config='DG_CONFIG=(oradb2,oradb1)'
*.log_archive_dest_1='LOCATION=/data/ora11g/oradata/oracle9i/archive/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb2'
*.log_archive_dest_2='SERVICE=ora11g_1.197 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.log_archive_max_processes=10
*.log_file_name_convert='/data/ora11g','/data/ora11g'
*.open_cursors=1000
*.pga_aggregate_target=2097152000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=885
*.sga_max_size=23068672000
*.shared_pool_size=1048576000
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
8.备份数据和控制文件,由于主库数据量太大，这里我采用脚本在后台备份，建议一次全备+备份每天归档日志，然后传输到从库上
#/bin/bash
ORACLE_HOME=/data/ora11g/product/11.2.0/db_1
ORACLE_SID=oradb2
RMAN_LOG_FILE=/data/backup/logs/full_usedbbackupdate +%y%m%d.log
export ORACLE_HOME
export ORACLE_SID
export RMAN_LOG_FILE
PATH=$ORACLE_HOME/bin:$PATH
export PATH
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
echo >> $RMAN_LOG_FILE
echo ==== started on date ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
CMD_STR="
rman target / msglog $RMAN_LOG_FILE append <<EOF
report obsolete;
delete noprompt obsolete;
run { allocate channel c1 device type disk format '/data/backup/rman/%U-%d-%T.full'; allocate channel c2 device type disk format '/data/backup/rman/%U-%d-%T.full'; BACKUP AS COMPRESSED BACKUPSET SKIP INACCESSIBLE FILESPERSET 10 DATABASE PLUS ARCHIVELOG FILESPERSET 20; DELETE ALL INPUT; backup current controlfile for standby format='/data/backup/rman/control_%U'; release channel c1; release channel c2; } crosscheck backup; delete noprompt expired backup; report obsolete; delete noprompt obsolete; EOF "
bash -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
if [ "$RSTAT" = "0" ]
then
LOGMSG="ended successfully"
else
LOGMSG="ended in error"
fi
echo >> $RMAN_LOG_FILE
echo ==== $LOGMSG on date ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
二:从库操作
1，检查配置(修改db_unique_name
alter system set db_unique_name='oradb1' scope=spfile;重启)
SQL>show parameter name
NAME TYPE VALUE
db_file_name_convert string /data/ora11g/oradata, /data/or
a11g/oradata
db_name string oracle9i
db_unique_name string oradb1
global_names boolean FALSE
instance_name string oradb2
lock_name_space string
log_file_name_convert string /data/ora11g, /data/ora11g
service_names string oradb1
2.修改传输过来的initoradb2.ora
添加如下参数，需要替换
db_name='需要同主库一样'
db_unique_name='从库唯一名'
log_archive_config='dg_config=(主库唯一名,从库唯一名)'
log_archive_dest_1='location=本地归档路径 valid_for=(all_logfiles,all_roles) db_unique_name=从库唯一名' ## 这里的这个all_logfiles主要是为后面最大可用模式做准备
log_archive_dest_state_1=enable
standby_file_management='auto'
log_file_name_convert='主库中日志文件的路径','从库上日志文件的路径（自己定义）' ## 这个是用在rman复制时需要的，设置了这个就不需要做set new..
db_file_name_convert='主库中数据文件的路径','从库上数据文件的路径（自己定义）'
cp initoradb2.ora initoradb1.ora
vim initoradb1.ora
oracle9i.__db_cache_size=16374562816
oracle9i.__oracle_base='/data/ora11g'#ORACLE_BASE set from environment
oradb2.__oracle_base='/data/ora11g'#ORACLE_BASE set from environment
oracle9i.__shared_pool_size=1342177280
*.audit_file_dest='/data/ora11g/admin/oracle9i/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/ora11g/oradata/oracle9i/control01.ctl','/data/ora11g/oradata/oracle9i/control02.ctl'
*.db_block_size=16384
*.db_cache_size=16777216000
*.db_domain=''
*.db_file_name_convert='/data/ora11g/oradata','/data/ora11g/oradata'
*.db_flashback_retention_target=60
*.db_name='oracle9i'
*.db_recovery_file_dest='/data/ora11g/flashback'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='oradb1'
*.diagnostic_dest='/data/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle9iXDB)'
*.fal_client='ora11g_1.197'
*.fal_server='ora11g_1.198'
*.java_pool_size=104857600
*.large_pool_size=209715200
*.log_archive_config='DG_CONFIG=(oradb2,oradb1)'
*.log_archive_dest_1='LOCATION=/data/ora11g/oradata/oracle9i/archive/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb1'
*.log_archive_dest_2='SERVICE=ora11g_1.198 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=10
*.log_file_name_convert='/data/ora11g','/data/ora11g'
*.open_cursors=1000
*.pga_aggregate_target=2097152000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=885
*.sga_max_size=23068672000
*.shared_pool_size=1048576000
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
SQL> CREATE SPFILE FROM PFILE;
SQL> STARTUP nomount pfile='/data/ora11g/product/11.2.0/db_1/dbs/initoradb1.ora';
3,恢复数据和备份文件
rman target /
RMAN>set dbid=xxxxx;
rman>restore standby controlfile from '/data/backup/rman/control_xxx';
RMAN> alter database mount;
rman>catelog start with '/data/backup/rman/';#注册备份
rman>restore database;
rman>recover database;
RMAN> alter database open resetlogs;
4.将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE';
5.主备添加standby redo log
SQL> select member from v$logfile;
MEMBER
/data/ora11g/oradata/oracle9i/redo03.log /data/ora11g/oradata/oracle9i/redo02.log /data/ora11g/oradata/oracle9i/redo01.log
SQL>SELECT GROUP#, BYTES FROM V$STANDBY_LOG; no rows selected
#主库增加 STANDBY LOGFILE ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/data/ora11g/oradata/oracle9i/sredo04.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/data/ora11g/oradata/oracle9i/sredo05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/data/ora11g/oradata/oracle9i/sredo06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/data/ora11g/oradata/oracle9i/sredo07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/data/ora11g/oradata/oracle9i/sredo08.log' size 50M;
#备库增加STANDBY LOGFILE SQL> select member from v$logfile;
MEMBER
/data/ora11g/oradata/oracle9i/redo03.log /data/ora11g/oradata/oracle9i/redo02.log /data/ora11g/oradata/oracle9i/redo01.log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/data/ora11g/oradata/oracle9i/sredo04.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/data/ora11g/oradata/oracle9i/sredo05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/data/ora11g/oradata/oracle9i/sredo06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/data/ora11g/oradata/oracle9i/sredo07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/data/ora11g/oradata/oracle9i/sredo08.log' size 50M;
6.开启active dataguard
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';
PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
3671 MRP0 APPLYING_LOG 1 78 25867 1048576
4317 RFS IDLE 0 0 0 0
4313 RFS IDLE 1 78 25867 1
4319 RFS IDLE 0 0 0 0
