Linux Oracle Active Data Guard环境搭建

1.修改主库参数

select open_mode ,log_mode from v$database; --查询数据库归档模式

alter database archivelog;

select name,log_mode,force_logging from gv$database; --查询数据库是否强制归档

alter database force logging; --修改数据库强制归档

alter database open;

alter system set DB_UNIQUE_NAME=cddata scope=spfile; --数据库的唯一名字

alter system set log_archive_config = 'DG_CONFIG=(cddata,cddg)' scope=spfile; --列出主备库上的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=cddata' scope=spfile; --本地归档路径

alter system set log_archive_dest_2 = 'SERVICE=cddg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cddg' scope=spfile; --standby归档传输方式(sync or async)以及传输目标(即standby apply node)

alter system set standby_file_management='AUTO' scope=both; --控制是否自动将Primary数据库增加表空间或数据文件的改动，传播到物理Standby数据库

alter system set fal_client='cddata' scope=spfile; --dataguard主备之间GAP的处理机制

alter system set fal_server='cddg' scope=spfile; --dataguard主备之间GAP的处理机制

alter system set log_archive_dest_state_1 = ENABLE;

alter system set log_archive_dest_state_2 = ENABLE;

----alter system set db_file_name_convert='/oracle/app/oracle/oradata/cddata','/oracle/app/oracle/oradata/cddata' SCOPE=SPFILE; --设置库主备库数据文件路径

----ALTER SYSTEM SET log_file_name_convert='oracle/app/oracle/fast_recovery_area/CDDATA/onlinelog','oracle/app/oracle/fast_recovery_area/CDDATA/onlinelog' SCOPE=SPFILE; --设置主备库日志文件路径

select * from v$log;--查询数据库日志文件大小及线程

select * from v$logfile; --查询数据库日志组

Standby redo log 组数公式 >=（每个instance日志组个数+1）*instance个数

select * from v$standby_log;--查询standby日志文件大小及线程

----therad --该参数在多实例下使用，单实例不使用该参数

alter database add standby logfile group 4 size 50m, group 5 size 50m,group 6 size 50m,group 7 size 50m; --新增主库standby日志组

----RAC 使用：

alter database add standby logfile thread 1 group 4 size 500m,group 5 size 500m,group 6 size 500m; --新增主库standby日志组

2.配置主库监听及连接标识

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = cddg)

(ORACLE_HOME = /u01/app/oracle/product/11.2.4/dbhome_1)

(SID_NAME = cddg)

)

)

cd =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.64)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cd)

)

)

cddg =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.74)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cddg)

)

)

3.备份主库

----建议将主库修改状态到mount进行备份----

mkdir /oracle/backup/ --创建备份文件夹

chown -R oracle:oinstall /oracle/backup/

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

backup database format '/oracle/backup/Full_%U.bak';

backup archivelog all format '/oracle/backup/cd_%U.bak';

release channel c1;

release channel c2;

release channel c3;

} --备份数据文件、日志文件

backup device type disk format '/oracle/backup/standby_%U.ctl' current controlfile for standby; --备份控制文件

create pfile='/oracle/backup/initcddg.ora' from spfile; --创建pfile文件

scp /oracle/backup/Full_1* 192.168.23.70:/oracle/backup/ --拷贝数据文件至备库

scp /oracle/backup/cd_1* 192.168.23.70:/oracle/backup/ --拷贝日志文件至备库

scp /oracle/backup/standby_* 192.168.24.220:/oracle/backup/ --拷贝控制文件至备库

scp /oracle/backup/initcddg.ora* 192.168.23.70:/oracle/backup/ --拷贝参数文件至备库

scp //oracle/app/oracle/product/11.2.0/db_1/dbs/orapw* 192.168.23.70:/oracle/app/oracle/product/11.2.0/db_1/dbs/ --拷贝密码文件至备库

4.配置备库监听及连接标识

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = cddg)

(ORACLE_HOME = /u01/app/oracle/product/11.2.4/dbhome_1)

(SID_NAME = cddg)

)

)

cd =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.64)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cd)

)

)

cddg =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.74)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cddg)

)

)

5.备库创建数据库所需的路径

mkdir -p admin/cddata/adump --备库创建路径

mkdir -p admin/cddata/cdump --备库创建路径

...........................

6.备库修改pfile参数并启动数据库到nomount状态添加参数

alter system set DB_UNIQUE_NAME=cddg scope=spfile;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cddata,cddg)' scope=both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cddg';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=cddata LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cddata';

alter system set standby_file_management='AUTO' scope=both;

alter system set fal_client='cddg';

alter system set fal_server='cddata';

-----alter system set db_file_name_convert='/oracle/app/oracle/oradata/cddata','/oracle/app/oracle/oradata/cddata' SCOPE=SPFILE;

-----ALTER SYSTEM SET log_file_name_convert='oracle/app/oracle/fast_recovery_area/CDDATA/onlinelog','oracle/app/oracle/fast_recovery_area/CDDATA/onlinelog' SCOPE=SPFILE;

7.备库恢复数据

restore standby controlfile from'/oracle/backup/standby*.ctl'; --rman 恢复控制文件

alter database mount;

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

restore database;

release channel c1;

release channel c2;

release channel c3;

} --rman恢复数据文件

8.备库应用日志完成ADG搭建

alter database recover managed standby database disconnect from session; --应用归档

alter database recover managed standby database cancel; --取消应用归档

alter database open; --打开数据库

alter database recover managed standby database disconnect; --应用归档

---日常管理维护dataguard常用查询语句---

archive log list; --列出日志信息

alter system switch logfile; --切换日志信息

select process,client_process,sequence#,status from v$managed_standby; --查看standby启动的DG进程

select database_role,protection_mode,protection_level,open_mode from v$database; --查看数据库的保护模式

select * from v$dataguard_status; --查看DG的日志信息

alter database set standby database to maximize availability; ----切换为最大可用

alter database set standby database to maximize protection; ----切换为最大保护

dataguard 启动关闭顺序

--启动：先启从库再启主库

--关闭：先关主库在关从库