Oracle11g DataGuard部署与维护：物理Standby创建时的操作步骤
1、创建主库的备份
2、创建Standby数据库控件文件
3、配置主备库的监听和网络服务名
4、配置主备库的初始化参数文件
5、复制备份集到Standby服务器
6、恢复Standby数据库
7、启动物理Standby数据库REDO应用
8、打开Standby数据库
9、切换主库的日志检测日志的传输与应用是否正常
10、物理备库的日常管理
11、主备库的切换

物理Standby实际创建过程演示

一、库配置和相关操
1、配置vi /etc/hosts
192.168.0.158 bxdb
192.168.0.165 dg
2、确认主库处于归档模式：archive log list;
3、将主库置为Force Logging模式:
select force_logging from v$database;
alter database force logging;
4、配置主库的初始化参数
*.db_unique_name=ocp_pri
*.log_archive_config='DG_CONFIG=(ocp_pri,ocp_dg)'
*.log_archive_dest_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ocp_pri'
*.log_archive_dest_2='SERVICE=standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ocp_dg'
*.log_archive_dest_state_2=DEFER
--主库切为备库时要用到
*.fal_server=standby
*.fal_client=primary
*.standby_file_management=AUTO

*.db_file_name_convert='bxdb','bxdb'

*.log_file_name_convert='bxdb','bxdb'

5、配置主库的listener.ora与tnsnames.ora文件
vi listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=bxdb)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=bxdb)
(ORACLE_HOME=/u01/app/oracle/product/11g)
(SID_NAME=bxdb)))
vi tnsnames.ora
bxdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bxdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bxdb)
)
)
bxdb_dg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bxdb)
)
)
6、主库备份
backup database format '/backup/rman/full_db_%U';
7、创建Standby数据库的控制文件
alter database create standby controlfile as '/backup/std01.ctl';
8、创建密码文件(关闭强密码认证)
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;
orapwd file=orapwbxdb password=oracle force=y ignorecase=y;
9、复制备份集、控制文件、参数文件到Standby服务器
scp *.ora
scp pfile.ora
scp std01.ctl
scp full_db*
二、物理Standby数据库配置和相关操作
1、配置/etc/hosts
192.168.0.158 bxdb
192.168.0.165 dg
2、创建密码文件(关闭强密码认证)
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;
orapwd file=orapwbxdb password=oracle force=y ignorecase=y;
3、创建日志输出文件相关目录
mkdir -p /arch
mkdir -p /u01/app/oracle/oradata/oca
mkdir -p /u01/app/oracle/admin/oca/adump
--mkdir -p /u01/app/oracle/diag/rdbms/bxdb_dg/bxdb/trace //自动生成无需创建
--mkdir -p /u01/app/oracle/diag/rdbms/bxdb_dg/bxdb/cdump //自动生成无需创建
4、配置备库的listener.ora与tnsnames.ora文件
修改listener.ora主机名或IP
5、配置备库的初始化参数
*.audit_file_dest='/u01/app/oracle/admin/bxdb/adump'
*.db_unique_name=bxdb_dg
*.log_archive_config='DG_CONFIG=(ocp_pri,ocp_dg)'
*.control_files='/opt/oracle/oradata/standby/control1.ctl'
*.log_archive_dest_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bxdb_dg'
*.log_archive_dest_2='SERVICE=bxdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bxdb'
*.fal_server=bxdb
*.fal_client=bxdb_dg
*.standby_file_management=AUTO

*.db_file_name_convert='bxdb','bxdb'

*.log_file_name_convert='bxdb','bxdb'

*.background_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/trace'

*.core_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/cdump'

*.user_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/trace'

6、启动物理Standby数据库到MOUNT状态
startup mount pfile='/backup/pfile.ora';
7、恢复数据库
export ORACLE_SID=bxdb
rman target /
catalog start with '/backup/';
restore database;
8、接收归档文件(在主库上操作)
在主库上启动发送日志：ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
9.应用日志
alter database recover managed standby database disconnect from session;
10.查看一下告警日志:Media Recovery Waiting for thread 1 sequence 8 (in transit)
如这个提示:recover managed standby database cancel;
11.打开数据库
alter database open;
12、建standby redo log;--创建的大小要与主库的redolog一样
alter database add standby logfile
group 20 ('/u01/app/oracle/oradata/oca/stb1redo20.log') size 100m,
group 21 ('/u01/app/oracle/oradata/oca/stb1redo21.log') size 100m,
group 22 ('/u01/app/oracle/oradata/oca/stb1redo22.log') size 100m,
group 23('/u01/app/oracle/oradata/oca/stb1redo23.log') size 100m;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
假如状态全是:UNASSIGNED--->shutdown immediate,startup;--> ACTIVE
13、启动REDO应用
recover managed standby database using current logfile disconnect from session;