目录
一、环境配置
1、系统安装版本和主库一致:
系统版本:Oracle Linux 7.4+GUI
2、资源配置:
CPU:4*8 内存:128G /:60G /data ---目录建议和主机库一致,后期不用做目录转换
3、关闭并禁用防火墙
systemctl stop firewalld systemctl disable firewalld
4、关闭并禁用selinux
vi /etc/selinux/config SELINUX=disabled
#setenforce 0
二、系统实施
1、创建和主库系统用户一致的oracle用户。
[root@localhost ~]# /usr/sbin/groupadd -g 1001 dba [root@localhost ~]# useradd -u 1001 -g dba orauat1 [root@localhost ~]# passwd orauat1 oracle密码默认为oracle。
2、拷贝/home/orauat1下的数据到备库
[orauat1@EBSMESUAT] scp -r /home/orauat1 orauat1@172.18.1.145:/home/orauat1
3、拷贝/u01/uat1/db的文件到备库/u01/uat1
[root@EBSMESUAT uat1]# scp -r /u01/uat1/db orauat1@172.18.1.145:/u01/uat1
4、配置.bash_profile变量
export PATH . /u01/uat1/db/12.1.0/EBSUAT1_ebsmesuat.env alias sqlplus='rlwrap sqlplus' alias rman='rlwrap rman'
三、配置主库
1、启动归档日志
启动归档需要在mount阶段
SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open;
增大恢复区空间:
SQL>alter system set db_recovery_file_dest_size=200G;
2、查看force logging是否开启
SQL> select name,log_mode,force_logging from v$database;
3、启动强制日志
SQL> alter database force logging;
4、增加standby的redolog
查看日志文件获取redolog文件路径和大小,保证主备redolog的大小是一致的
查看日志文件获取redolog文件路径和大小,保证主备redolog的大小是一致的
(1)查看当前logfile的数量
SQL>select member from v$logfile; /u01/uat1/db/data/log06a.dbf /u01/uat1/db/data/log06b.dbf /u01/uat1/db/data/log05a.dbf /u01/uat1/db/data/log05b.dbf /u01/uat1/db/data/log04a.dbf /u01/uat1/db/data/log04b.dbf /u01/uat1/db/data/log03a.dbf /u01/uat1/db/data/log03b.dbf /u01/uat1/db/data/log02a.dbf /u01/uat1/db/data/log02b.dbf /u01/uat1/db/data/log01a.dbf /u01/uat1/db/data/log01b.dbf
(2)查看当前logfile的大小
SQL>select bytes/1024/1024 as filesize from v$log;
FILESIZE ---------- 1000 1000 1000 1000 1000 1000
(3)增加standby的logfile
standby logfile的要求:
①大小与主机大小一致,
②文件数量
Standby redo log数量>=(每个instance日志组个数+1)*instance实例个数。我们通常为单实例:
Standby redo log数量=(3+1)*1=4即可。
SQL>alter database add standby logfile group 7 '/u01/uat1/db/data/std_redo07a.log' size 1048576000; SQL>alter database add standby logfile group 8 '/u01/uat1/db/data/std_redo08a.log' size 1048576000; SQL>alter database add standby logfile group 9 '/u01/uat1/db/data/std_redo09a.log' size 1048576000; SQL>alter database add standby logfile group 10 '/u01/uat1/db/data/std_redo10a.log' size 1048576000; SQL>alter database add standby logfile group 11 '/u01/uat1/db/data/std_redo11a.log' size 1048576000; SQL>alter database add standby logfile group 12 '/u01/uat1/db/data/std_redo12a.log' size 1048576000; SQL>alter database add standby logfile group 13 '/u01/uat1/db/data/std_redo13a.log' size 1048576000;
5、查看一下数据文件
SQL> select file_name from dba_data_files; /u01/uat1/db/data/a_ref02.dbf 一共88个数据文件
6、创建主备转换目录
【使用当前即可:/oracle/app/oracle/oradata/mesorcl/】
7、主库创建密码并拷贝至备库
(1)方法1:
直接在主库拷贝到备库
/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwmesorcl
(2)方法2:
手动在主库创建并传输到备库
主库创建密码:
[orauat1@EBSMESUAT dbs]$ orapwd file=/u01/uat1/db/12.1.0/dbs/orapwEBSUAT1 password=oracle force=y;
备库改名:因为双方SID一样的,所以无需改名:orapwEBSUAT1
8、主库参数文件配置
(1)查看配置文件位置,并备份spfile文件
SQL>show parameter spfile;
(2)创建pfie
SQL>create pfile from spfile;
(3)配置DB_RECOVERY_FILE_DEST
ORA-01078: failure in processing system parameters ORA-16032: parameter LOG_ARCHIVE_DEST_3 destination string cannot be translated ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not set
alter system set db_recovery_file_dest='/u01/uat1/db/data/archive'; alter system set db_recovery_file_size=1024G; alter system set log_archive_dest_1='';
SQL>shutdown immediate SQL>startup mount
(4)配置pfile
alter system set db_unique_name='EBSUAT1' scope=spfile; alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(EBSUAT1,EBSUAT1STD)' scope=both; alter system set LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EBSUAT1' scope=both; alter system set LOG_ARCHIVE_DEST_4='SERVICE=EBSUAT1STD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EBSUAT1STD' scope=both; alter system set fal_client='EBSUAT1' scope=both;#自己作为备机时填的信息,此处client端填自己的tnsnames的名 alter system set FAL_SERVER='EBSUAT1STD' scope=both;#自己作为备机时填的信息,此处client端填自己的tnsnames的名
#注:此处如果数据文件有多个路径,则向后增加对应路径即可如:
alter system set DB_FILE_NAME_CONVERT='/u01/uat1/db/data/','/u01/uat1/db/data/' scope=spfile; alter system set LOG_FILE_NAME_CONVERT='/u01/uat1/db/data/','/u01/uat1/db/data/' scope=spfile; alter system set standby_file_management='auto' scope=both;
重启数据库,是上述配置生效:
SQL>shutdown immediate SQL>startup
9、主库增加如下红色listener静态监听,并重启监听(如果有静态监听,不需要更改)
SID_LIST_EBSUAT1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = EBSUAT1) (ORACLE_HOME= /u01/uat1/db/12.1.0) (SID_NAME = EBSUAT1) ) )
10、修改tnsnames.ora文件,增加备库信息MESORCLSTD
EBSUAT1= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsmesuat.zhiban.com)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=EBSUAT1) (INSTANCE_NAME=EBSUAT1) ) ) EBSUAT1STD= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.145)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=EBSUAT1STD) (INSTANCE_NAME=EBSUAT1) ) )
11、重启监听
lsnrctl stop EBSUAT1
四、备库
0、替换1-4步骤
因为数据库是在线拷贝,所以数据不一致,无法mount,也就无法更改如下spfile文件。所以我们需要从1.149cp一个最新的pfile文件到1.145,在1.145手动进行修改:
(1)传输主库initEBSUAT1.ora文件到备库(1.145)
(2)修改initEBSUAT1.ora中根据4.4的内容修改即可:
(3)SQL>create spfile from pfile;
1、启动归档
启动归档需要在mount阶段,如果从主机克隆的可以不用考虑
SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open;
设置恢复空间
SQL>alter system set db_recovery_file_dest_size=1024G; SQL>alter system set db_recovery_file_dest='/u01/uat1/db/data/archive'; alter system set db_recovery_file_size=1024G; alter system set log_archive_dest_1='';
2、查看一下数据文件
SQL> select file_name from dba_data_files;
3、创建主备转换目录
【/oracle/app/oracle/oradata/mesorcl/即可】
4、备库参数文件
(1)查看配置文件位置,并备份sfpile文件
SQL>show parameter spfile;
(2)创建pfie
SQL>create pfile from spfile;
(3)配置DB_RECOVERY_FILE_DEST
ORA-01078: failure in processing system parameters ORA-16032: parameter LOG_ARCHIVE_DEST_3 destination string cannot be translated ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not set
alter system set db_recovery_file_dest='/u01/uat1/db/data/archive'; alter system set db_recovery_file_size=1024G; alter system set log_archive_dest_1='';
SQL>shutdown immediate SQL>startup mount
(4)通过alter直接配置spfile;
alter system set db_unique_name='EBSUAT1STD' scope=spfile; alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(EBSUAT1STD,EBSUAT1)' scope=both; 下述需要重启数据库,才能把db_unique_name生效; alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EBSUAT1STD' scope=both; alter system set LOG_ARCHIVE_DEST_2='SERVICE=EBSUAT1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EBSUAT1' scope=both; alter system set fal_client='EBSUAT1STD' scope=both; #自己作为备机时填的信息,此处client端填自己的tnsnames的名 alter system set FAL_SERVER='EBSUAT1' scope=both;#自己作为备机时填的信息,此处server端填对方的tnsnames的名
#注:此处如果数据文件有多个路径,则向后增加对应路径即可如: alter system set DB_FILE_NAME_CONVERT='/u01/uat1/db/data/','/u01/uat1/db/data/' scope=spfile; alter system set LOG_FILE_NAME_CONVERT='/u01/uat1/db/data/','/u01/uat1/db/data/' scope=spfile; alter system set standby_file_management='auto' scope=both;
5、备库增加listener静态监听,并重启监听【此处:global_name=mesorcl 作为静态注册的服务名称】
SID_LIST_EBSUAT1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = EBSUAT1STD) (ORACLE_HOME= /u01/uat1/db/12.1.0) (SID_NAME = EBSUAT1) ) )
6、修改tnsnames.ora文件,增加主备的tnsname信
EBSUAT1= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.149)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=EBSUAT1) (INSTANCE_NAME=EBSUAT1) ) ) EBSUAT1STD= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.145)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=EBSUAT1) (INSTANCE_NAME=EBSUAT1) ) )
7、重启监听:
lsnrctl stop EBSUAT1 lsnrctl start EBSUAT1
五、测试下面是否都通:
主库:tnsping EBSUAT1和tnsping EBSUAT1STD
备库:tnsping EBSUAT1和tnsping EBSUAT1STD
两者都通即可,如果不通,检查listener.ora和tnsnames.ora
六、数据恢复、同步(备库操作)
1、利用RMAN在备库上恢复主库
SQL>shutdown immediate SYS > startup nomount
[oracle@std ~]$ rman target sys/1qaz2WSX@mesorcl auxiliary sys/1qaz2WSX@mesorclstd
原库:sys/1qaz2WSX@mesorcl
备库:sys/1qaz2WSX@mesorclstd
RMAN> duplicate target database for standby from active database nofilenamecheck;#【nofilenamecheck 这个参数在出现RMAN-05001再加上】
这条命令可以直接恢复数据文件,standby控制文件,standby日志组,非常霸道,查看如下日志:同步
上述备库数据同步完成,下面进行登录检查:
2、尝试开启
登陆并查看数据库当前状态
[oracle@std ~]$ sqlplus / as sysdba SYS@std> select status from v$instance;
MOUNTED (RMAN恢复完直接就是mount状态)
七、检查、开启、关闭数据库同步
1、检查Dataguard 状态
SQL>select database_role,protection_mode,protection_level,open_mode from v$database; SQL>select name,open_mode,protection_mode,database_role,switchover_status from v$database;
2、启动dataguard数据库
(备库执行),开启后,数据开始从主库同步至备库[最好手动执行一下SQL> alter system switch logfile;手动归档,数据会立即传输到备库
SQL> alter database recover managed standby database disconnect from session; --归档方式同步
或
SQL>alter database recover managed standby database using current logfile disconnect from session; --实时同步
3、关闭datagurade数据同
(备库执行),关闭后,数据不再同步至备库(如果关闭了数据同步功能,即使archivelog从主库同步过来,也不会像备库表中写入数据)
SQL>alter database recover managed standby database cancel;
4、打开数据库,再开启同步数据
SQL>alter database open; SQL>alter database recover managed standby database using current logfile disconnect from session;
5、主库redo 日志归档到archive 日志
SQL> alter system switch logfile;(主库执行)
6、主备库检查dataguard 状态【查看archivelog序列号是否一致,下面两种都可以查看】
SQL>select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log; SQL>archive log list;【此命令不要使用,不准确】
7、检查dataguard日志
主库日志:tail -100f /home/oracle/app/oracle/diag/rdbms/pri/pri/trace/alert_std.log
备库日志:tail -100f /home/oracle/app/oracle/diag/rdbms/std/std/trace/alert_std.log
或使用命令:SQL>select * from v$dataguard_status;
SQL>select * from v$diag_instance;
8、查看日志应用情
SYS@std> set pagesize 10; SYS@std> select sequence#,applied from v$archived_log order by sequence#;
如上,如果发现有个NO的,也是正常的,说明该日志在主库上还没有归档,可以在主库上运行alter system switch logfile;命令来进行日志切换,再到备库查看日志应用
至此,DataGuard的搭建成.