1.主库打开强制日志模式
select force_logging from v$database;
alter database force logging;
2.主库归档模式
alter database archivelog;
3.修改主库的本地归档路径
mkdir -p /oralog/orcl/archivelog
SQL> alter system set log_archive_dest_1='location=/oralog/orcl/archivelog/valid_for(online_logfiles,primary_role) db_unique_name=orcl';
表示将当该库的角色为主库时,将当前路径作为归档路径
4.激活主库的本地归档路径
SQL> alter system set log_archive_dest_state_1='enable';
5.启用主库远程归档路径
SQL> alter system set log_archive_dest_2='service=aux1srv valid_for=(online_logfiles,primary_role) db_unique_name=aux1';
说明:
service=aux1src表示主库使用名称为aux1src作为从库连接的服务命名
db_unique_name=aux1 表示从库的数据库唯一名称
6.激活主库的远程归档路径
SQL> alter system set log_archive_dest_state_2='enable';
7.主库打开DG开关
SQL> alter system set log_archive_config='dg_config=(orcl,aux1)';
8.为从库准备口令文件(直接复制主库口令文件并修改,传输日志走安全审核时使用的是sys做安全审核,保证主库和从库的用户名密码一致)
[oracle@localhost dbs]$ scp orapworcl oracle@192.168.106.165:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwaux1
oracle@192.168.106.165's password:
orapworcl ##注意,在传输前一定要给主库的sys用户设置密码再传输alter user sys identified by xsy123;
9.为从库准备参数文件(拿主库的pfile来修改)
SQL> create pfile='/home/oracle/spfile.ora' from spfile;
[oracle@localhost dbs]$ vim /home/oracle/spfile.ora将参数文件修改为如下内容,从库以aux1实例为例,修改结果如下
*.audit_file_dest='/u01/app/oracle/admin/aux1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/aux1/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'#必须和主库一致
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.job_queue_processes=0
*.log_archive_config='dg_config=(orcl,aux1)'
*.memory_target=1601175552
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#从库特有的选项参数
db_unique_name='aux1' #主库和从库的数据库唯一名不能一样
log_archive_dest_3='location=/oralog/aux1/archivelog/valid_for(standby_logfiles,standby_role) db_unique_name=aux1'
log_archive_dest_state_3='enable'
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/aux1/'#主从数据文件不在相同目录名时
log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/aux1/'
注意上面的中文不能配置到pfile中
将修改的参数文件复制到从库:
scp spfile.ora oracle@192.168.106.165:/u01/app/oracle/product/11.2.0/db_1/dbs/init/initaux1.ora
10.在从库中创建9步骤中涉及的目录
[oracle@localhost dbs]$ mkdir -p /u01/app/oracle/admin/aux1/adump
[oracle@localhost dbs]$ mkdir -p /u01/app/oracle/oradata/aux1
.....其它目录自己创建
11.根据9步骤创建的pfile创建spfile,从库将实例启动到nomount
[oracle@localhost dbs]$ export ORACLE_SID=aux1
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL>startup nomount;
SQL>create spfile from pfile
SQL>shutdown immediate
SQL>startup nomount;
12.为从库配置监听程序,用netmgr
添加一个监听
通过图形界面生成的配置如下:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = aux1.example.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = aux1)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.106.165)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
13.在主库添加用于从库连接的服务命名
[oracle@localhost admin]$ vim tnsnames.ora
aux1srv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.106.165)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = aux1.example.com)
)
)
14.在主库启动rman复制从库
[oracle@localhost ~]$rman target / auxiliary sys/xsy123@aux1srv
使用rman做出一个从库
RMAN> duplicate target database for standby from active database;##该步骤会自动运行rman内存脚本完成主库向从库恢复
出现successfull表示恢复从库完毕
恢复完毕,从库自动启动到mount状态,尝试打开数据库alter database open;
(本实验我做到这步就能通过alter database open数据库了)
15.(本实验没涉及该步骤)在从库添加standby log(如果在14步骤中没办法启动数据库到open提示需要recovery时,需要做该15,19,20步骤)
alter database add standby logfile 'xxx/redo4.log' size 50M大小要和主库一致;
alter database add standby logfile 'xxx/redo5.log' size 50M大小要和主库一致;
alter database add standby logfile 'xxx/redo6.log' size 50M大小要和主库一致;
16.主库切换日志
alter system switch logfile;
18.在从库查看归档应用情况
select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
114 NO
115 NO
116 NO
117 NO
日志默认是不应用的,DBA可以根据实际情况对日志进行应用
19.在从库启动恢复管理,运行该命令,从库使用standby log 做recover
SQL> alter database recover managed standby database disconnect from session;##使用该方式打开只有主库归档切换到从库时,才会做recover,不是时时,不包含联机中未归档数据
SQL> alter database recover managed standby database using current logfile disconnect from session;##添加current logfile 选项表示时时应用,主库产生了日志写,立即将日志写到从库中,从库与主库看到的数据时时同步
SEQUENCE# APPLIED
---------- ---------
114 YES
115 YES
116 YES
117 YES
从库停止恢复管理进程,运行次命令从库不会时时应用归档
alter database recover managed standby database cancel;
20.打开从库
alter database open;
原理:主库在联机日志写成功后,通过sys用户远程连接将主库联机日志传递到从库的standby日志中,主库切换日志
从库的standby也同时切换日志。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
从库接收到归档日志是否立即应用讲解:
从库可以比主库晚一段时间应用,这样可以绕过这段时间内主库发生的误操作,至于需要需要立即应用
这个根据实际需求来定。
案例1:在从库进行不完全恢复,绕过主库的误操作
alter database recover managed standby database until change 1090211 disconnect from session;
案例2:从库启动管理恢复,延迟应用归档日志(以分钟为单位)
alter database recover managed standby database disconnect from session dalay 120;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
额外:
配置从库的监听过程: