基于rman 全备+归档在线搭建DG
主从两台主机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/fullusedbbackupdate +%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 <
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
