分类： Oracle
2017-04-19 22:03:54
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Helvetica} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Helvetica; min-height: 13.0px} span.Apple-tab-span {white-space:pre}
说明：该库的参数文件存放在ASM上，两个节点使用同一个，便于管理。
节点1操作时，节点2数据库需关闭，等节点1调整完成，再开启节点2的库。
1.1.1 开启归档
该操作节点1操作
SQL>Sqlplus / as sysdba
SQL>create pfile='/u01/app/oracle/initorcl20160616.ora' from spfile;
－－备份参数文件，指定目录备份，如果直接生成的话，会覆盖现有pfile，现有pfile，内容只是指向ASM里的pfile
SQL>Shutdown immediate;
SQL>Startup mount;
SQL>Alter database archivelog;
SQL>alter database force logging;--归档模式下修改
SQL>alter database open;
1.1.2 修改各项参数
该操作节点1操作
SQL>alter system set log_file_name_convert='/rac_dg/app/oracle/oradata/orcl','+SSD/onlinelog' scope=spfile;
alter system set db_file_name_convert='/rac_dg/app/oracle/oradata/orcl','+HDD/orcl/datafile','/rac_dg/app/oracle/oradata/orcl','+SSD/orcl/datafile';
SQL>alter system set db_file_name_convert='/rac_dg/app/oracle/oradata/orcl','+HDD/orcl/datafile' scope=spfile;
SQL>Shutdown immediate;
SQL>startup
SQL>show parameter db_unique_name 默认是orcl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
SQL> alter system set log_archive_config='dg_config=(orcl,sdb)';
归档文件的生成路径，location代表本地机上，service指在另一台机器上
SQL> alter system set log_archive_dest_2='service=sdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=sdb';
对方的网络服务名
SQL> show parameter log_archive_dest_state_1
SQL> show parameter log_archive_dest_state_2 --确认这两个参数为enable的，激活定义的,可以先修改为defer 归档日志目录，允许redo 传输服务传输redo数据到指定的路径
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf --格式默认就好
SQL> alter system set fal_server=sdb;
SQL> alter system set fal_client=orcl; --注意这里反着写的，表示切换后的主和备
SQL> alter system set standby_file_management=auto;
节点2的库到这可以开启了。查询两边的状态，是否归档都已打开。
--至此，参数修改完毕,然后由修改后的spfile创建一个pfile文件，以便拷到备库修改使用。
SQL> create pfile='/u01/app/oracle/initorcl20161130.ora' from spfile;
1.2 主库Rman备份
该操作备库操作
mkdir /u01/arch_back 创建目录
[oracle@rac1~]$Rman target sys/oracle
RMAN> backup format '/rac_dg/arch_back/con_%U' database include current controlfile for standby;
1.3 备库配置
1.3.1 创建目录
该操作备库操作
Oracle用户下
mkdir /rac_dg/archivelog -p
mkdir /rac_dg/arch_back -p
mkdir /rac_dg/app/oracle/product/11.2.0/db_1/dbs -p
mkdir /rac_dg/app/oracle/oradata/orcl/ -p
mkdir /rac_dg/app/oracle/admin/orcl/{a,b,u,dp,c}dump -p
mkdir /rac_dg/app/oracle/diag/rdbms/orcl/orcl/ -p
mkdir /rac_dg/app/oracle/fast_recovery_area/orcl/ -p
1.3.2 拷贝文件
该操作节点1操作，拷贝参数文件、日志文件、rman备份
[oracle@ljstopri ~]$scp /u01/app/oracle/initorcl**.ora oracle@10.1.3.3:/rac_dg/app/oracle/product/11.2.0/db_1/dbs/
[oracle@ljstopri ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl1 oracle@10.1.3.3:/rac_dg/app/oracle/product/11.2.0/db_1/dbs/ --拷密码文件 ，拷贝了节点1的密码文件。之后需要更改文件名。
1.3.3 修改参数文件
该操作备库操作
*.control_files='/rac_dg/app/oracle/oradata/orcl/control01.ctl','/rac_dg/app/oracle/fast_recovery_area/orcl/control02.ctl'-----此路径需要改为本库存放路径
*.db_file_name_convert='+HDD/orcl/datafile','/rac_dg/app/oracle/oradata/orcl'
---db_file_name_convert在备库需要把主备目录顺序对换一下，主在前，备在后
*.db_recovery_file_dest='/rac_dg/app/oracle/fast_recovery_area'
----db_recovery_file_dest-改为本机路径
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='sdb' ----需要改成sdb
*.fal_client='sdb'
*.fal_server='orcl' ---- sdb orcl进行更换
*.log_archive_config='dg_config=(sdb,orcl)' ----顺序进行更换
*.log_archive_dest_1='location=/rac_dg/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=sdb'
-----Location在备库在此处修改成本地目录，db_unique_name也需要更改
*.log_archive_dest_2='service=orcl lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl' ----需要把sdb修改orcl
*.log_file_name_convert='+SSD/onlinelog','/rac_dg/app/oracle/oradata/orcl'
-------log_file_name_convert在备库需要把主备目录顺序对换一下，主在前，备在后
*.pga_aggregate_target=1073741824------注意备库内存
*.sga_target=3221225472------注意备库内存
*.undo_tablespace='UNDOTBS1' ----保留一个
1.4 配置监听
节点1，2各添加了一个SDB
SDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.160)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
备库
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl2)
)
)
SDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.170)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)hhhhhhhhhhhbbb
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.150)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
tnsping orcl，orcl２，sdb
--上面都互相能tnsping通，最好在主备库都互相远程连接一下主库，要求成功，才说明监听没问题
1.5 备库Rman恢复
该操作备库操作
[oracle@std ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/rac_dg/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'--启动到mount状态
SQL>create spfile from pfile;--重新生成一个spfile
SQL> shutdown immediate;
SQL> startup nomount--启动到mount状态
[oracle@std ~]$ rman target sys/sysoraclet@orcl auxiliary /
connected to target database: ORCL (DBID=1271897179)
connected to auxiliary database (not mounted)
--可以从连接信息看到是同时连到主和备（注意此时主库为open状态，备库为nomount状态）
RMAN> duplicate target database for standby nofilenamecheck;
---把拷过来的备份（全备和控制文件备份）进行恢复
---恢复完成后，备库自动会由nomount状态到mount状态
[oracle@std ~]$ sqlplus / as sysdba
SQL>select status from v$instance; 查看是否mount状态
SQL>select database_role from v$database; 查看是否standby状态
1.6 检查DG状态
该操作备库操作
1、创建日志组
SQL>alter database add standby logfile thread 1 group 5 size 500m;
SQL>alter database add standby logfile thread 1 group 6 size 500m;
SQL>alter database add standby logfile thread 2 group 7 size 500m;
SQL>alter database add standby logfile thread 2 group 8 size 500m;
2、打开日志传递应用功能
SQL>alter database recover managed standby database disconnect from session;
3、在备库上面确认相关的进程已经启动
SQL> select process,status,thread#,sequence# from v$managed_standby order by 3,1;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------- ---------- ----------------
ARCH CONNECTED 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
ARCH CLOSING 1 269
ARCH CLOSING 1 270
RFS IDLE 1 271
ARCH CLOSING 2 106
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ------------------------------------------
MRP0 WAIT_FOR_LOG 2 111
RFS IDLE 2 111
4、检查日志是否传输、是否应用正常。
SQL> select sequence#,applied,thread#,deleted from v$archived_log;
SEQUENCE# APPLIED THREAD# DEL
---------- --------- ---------- ---
106 YES 2 NO
107 YES 2 NO
当前我们在应用日志同步的状态。在进行同步的时候，是不能open的。我们需要首先将这个日志同步动作cancel中。
SQL> conn / as sysdba
Connected.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY dg2
SQL> select status from v$standby_log;
STATUS
----------
ACTIVE
UNASSIGNED
UNASSIGNED
UNASSIGNED
注意：在11g中，READ ONLY WITH APPLY状态表示在同步同时，也可以应用数据库Redo Log。如果是之前的版本，数据库需要恢复到mount状态。这个也就是11g中Advanced。
Oracle 11g Data Guard被称为Active Data Guard，是一个重要的升级版本。
