--环境：
操作系统：redhat 7.5
oracle： 11.2.0.4
主：192.168.3.3
备：192.168.3.4
1、确定数据库是否为归档模式，不是则开启归档
select log_mode from v$database;
archive log list;
开启归档模式
shutdown immediate
startup mount
alter database archivelog;
alter system set log_archive_dest_1='location=/oracle/arch';
alter database open;
2、主库开启强制日志模式
select FORCE_LOGGING from v$database;
ALTER DATABASE FORCE LOGGING;
3、主库验证是否有密码文件（如果有HA，备库也验证）
cd $ORACLE_HOME/dbs
ls orap*
#if no exists orapw* file then
#cd $ORACLE_HOME/dbs
#orapwd file=orapwmydb11g password=sys
4、主库添加standby redo
确认redo大小和路径
set linesize 200
col member for a55
SQL> select a.group#,a.thread#,b.type,a.bytes/1024/1024 mb ,b.member from v$log a ,v$logfile b where a.group#=b.group# order by 2;
GROUP# THREAD# TYPE MB MEMBER
---------- ---------- ------- ---------- -------------------------------------------------------
1 1 ONLINE 50 /oracle/oradata/mydb11g/mydb11g/redo01.log
3 1 ONLINE 50 /oracle/oradata/mydb11g/mydb11g/redo03.log
2 1 ONLINE 50 /oracle/oradata/mydb11g/mydb11g/redo02.log
SQL> select a.group#,a.thread#,b.type,a.bytes/1024/1024 mb ,b.member from v$standby_log a ,v$logfile b where a.group#=b.group#;
no rows selected
SQL>
--添加比redo多一组的standby,大小一样
alter database add standby logfile thread 1 group 11 '/oracle/oradata/mydb11g/mydb11g/redo11std.log' size 50M;
alter database add standby logfile thread 1 group 12 '/oracle/oradata/mydb11g/mydb11g/redo12std.log' size 50M;
alter database add standby logfile thread 1 group 13 '/oracle/oradata/mydb11g/mydb11g/redo13std.log' size 50M;
alter database add standby logfile thread 1 group 14 '/oracle/oradata/mydb11g/mydb11g/redo14std.log' size 50M;
5、修改主库参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(mydb11g,mydb11gdg)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mydb11g';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=mydb11gdg LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mydb11gdg';
alter system set fal_server='mydb11gdg';
alter system set standby_file_management=AUTO scope=both;
下列2个参数需要下次重启后生效,主库的这个参数不是必须，当作为备库时生效
alter system set DB_FILE_NAME_CONVERT='/oracle/oradata/mydb11g/mydb11g','/oracle/oradata/mydb11g/mydb11g' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/oracle/oradata/mydb11g/mydb11g','/oracle/oradata/mydb11g/mydb11g' scope=spfile;
select name,display_value,value from v$spparameter where name='db_file_name_convert';
select name,display_value,value from v$spparameter where name='log_file_name_convert';
7、在备库创建静态监听文件
cd $ORACLE_HOME/network/admin
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mydb11gdg)
(ORACLE_HOME = /oracle/app/product/11.2.0/db_1)
(SID_NAME = mydb11gdg)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.4)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /oracle/app
8、启动备库监听
lsnrctl start
9、修改主、备库的TNSNAMES文件
mydb11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb11g)
)
)
mydb11gdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb11gdg)
)
)
11、tnsping测试，确保都能通
主库：
tnsping mydb11g
tnsping mydb11gdg
备库：
tnsping mydb11g
tnsping mydb11gdg
12、创建备库密码文件
从主库中scp 密码文件备库,sys密码不能有下划线
cd $ORACLE_HOME/dbs
scp orapwmydb11g 192.168.3.4:/oracle/app/product/11.2.0/db_1/dbs/orapwmydb11g
13、为备库创建初始化参数文件
主库：
create pfile='/tmp/dg.ora' from spfile;
scp /tmp/dg.ora 192.168.3.4:/oracle/app/product/11.2.0/db_1/dbs/
备库修改参数文件：
cd $ORACLE_HOME/dbs/
vi dg.ora
*.audit_file_dest='/oracle/app/admin/mydb11g/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/oradata/mydb11g/mydb11g/control01.ctl','/oracle/oradata/mydb11g/mydb11g/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/oracle/oradata/mydb11g/mydb11g','/oracle/oradata/mydb11g/mydb11g'
*.db_files=600
*.db_name='mydb11g'
*.db_unique_name='mydb11gdg'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oracle/app'
*.dispatchers=''
*.fal_server='mydb11g'
*.fal_client='mydb11gdg'
*.fast_start_mttr_target=300
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(mydb11gdg,mydb11g)'
*.log_archive_dest_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mydb11gdg'
*.log_archive_dest_2='SERVICE=mydb11g LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mydb11g'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='/oracle/oradata/mydb11g/mydb11g','/oracle/oradata/mydb11g/mydb11g'
*.open_cursors=1000
*.optimizer_index_caching=5
*.optimizer_index_cost_adj=90
*.parallel_max_servers=256
*.parallel_servers_target=64
*.pga_aggregate_target=268435456
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=500
*.sessions=1105
*.sga_target=805306368
*.standby_file_management='AUTO'
*.undo_retention=7200
*.undo_tablespace='UNDOTBS1'
检查备库数据文件目录权限,如果目录不存在创建对应目录
/oracle/oradata
/oracle/arch
15、为备库创建spfile
export ORACLE_SID=mydb11gdg
sqlplus / as sysdba
SQL> startup nomount pfile='/oracle/app/product/11.2.0/db_1/dbs/dg.ora'
SQL> create spfile from pfile='/oracle/app/product/11.2.0/db_1/dbs/dg.ora';
16、备库关闭重启
shutdown immediate
startup nomount
17、连接至主数据库作为其目标数据库，确保主备都可以互相连通
主库
sqlplus sys/'oracle'@mydb11g as sysdba
sqlplus sys/'oracle'@mydb11gdg as sysdba
备库
sqlplus sys/'oracle'@mydb11g as sysdba
sqlplus sys/'oracle'@mydb11gdg as sysdba
18、主库配置dataguard备库,使用duplicate 方式
export ORACLE_SID=mydb11g
rman target sys/oracle auxiliary sys/oracle@mydb11gdg nocatalog <<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database nofilenamecheck;
}
exit
EOF
--加上nofilenamecheck参数，在主备目录相同时不会报
RMAN-05001: auxiliary file name /oracle/oradata/mydb11g/mydb11g/users01.dbf conflicts with a file used by the target database
19、只读模式打开备库
备库：
alter database open;
orapwd file=orapwmydb11g password=welcome entries=10 force=y ignorecase=y;
alter system set fal_server='mydb11g';
alter system set fal_client='mydb11gdg';
alter system set SEC_CASE_SENSITIVE_LOGON=FALSE;
20、应用实时日志
备库：
alter database recover managed standby database using current logfile disconnect from session;