1.基础环境准备
1.1基础环境说明:
操作系统:RHEL 5.5 64bit
数据库版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
1.2数据库服务名称及目录说明:
主库 | 备库 | |
IP地址 | 192.168.4.157 | 192.168.4.158 |
主机名称 | primary01 | standby01 |
db_name | testdb | testdb |
db_unique_name | pri_db | std_db |
service_names | pri_db | std_db |
local_listener | pri_1522 | std_1522 |
监听端口 | 1522 | 1522 |
基目录 | /u01/app/oracle | /u01/app/oracle |
数据文件目录 | /u01/app/oradata/testdb | /u01/app/oradata/testdb |
归档日志目录 | /u01/app/archivelog/testdb | /u01/app/archivelog/testdb |
RMAN备份目录 | /u01/app/rman_backup/ | /u01/app/rman_backup/ |
1.3环境准备:
1.3.1修改主、备库hosts文件:
# cat /etc/hosts
192.168.4.157 primary01
192.168.4.158 standby01
1.3.2修改主、备库监听文件:
$ cat tnsnames.ora
pri_1522 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = primary01)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME =pri_db)
)
)
std_1522 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = standby01)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME =std_db)
)
)
2.主库准备工作
2.1置于在mount状态,修改数据库force logging,开启归档。
SQL> SELECT name,log_mode, force_logging, open_mode FROM v$database;
NAME LOG_MODE FOR OPEN_MODE
--------- ------------ --- --------------------
TESTDB NOARCHIVELOG NO MOUNTED
SQL> alter database force logging;
SQL> alter database archivelog;
SQL> alter database open;
SQL> SELECT name,log_mode, force_logging, open_mode FROM v$database;
NAME LOG_MODE FOR OPEN_MODE
--------- ------------ --- --------------------
TESTDB ARCHIVELOG YES READ WRITE
2.2动态注册1522端口
SQL> show parameter local_listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string pri_1522
[oracle@primary01 ~]$ lsnrctl status
2.3主库确定采用口令认证
SQL> show parameter password;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
2.4主库配置LOG_ARCHIVE_CONFIG参数
此参数用于控制发送归档日志到远程位置、接收远程归档日志,并指定Data Guard配置的惟一数据库名。
SQL> show parameter log_archive_config;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
SQL> alter system set log_archive_config='dg_config=(pri_db,std_db)';
SQL> show parameter log_archive_config;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(pri_db,std_db)
2.5配置log_archive_dest_1日志归档路径:
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
SQL> alter system set log_archive_dest_1='location=/u01/app/archivelog/testdb valid_for=(all_logfiles,all_roles) db_unique_name=pri_db';
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/app/archivelog/t
estdb valid_for=(all_logfiles,
all_roles) db_unique_name=pri_
db
SQL> SELECT group#, sequence#, archived, status FROM v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 4 YES INACTIVE
2 5 YES INACTIVE
3 6 NO CURRENT
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> SELECT group#, sequence#, archived, status FROM v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 7 YES INACTIVE
2 8 YES INACTIVE
3 9 NO CURRENT
2.6为备库准备pfile样例
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfiletestdb.ora
SQL> create pfile from spfile;
2.7主库做RMAN全备
[oracle@primary01 ~]$ rman target /
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_namePRI_DB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 6 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_6_905435865.dbf
2 1 7 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_7_905435865.dbf
3 1 8 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_8_905435865.dbf
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/app/oradata/testdb/system01.dbf
2 0 /u01/app/oradata/testdb/sysaux01.dbf
3 0 /u01/app/oradata/testdb/undotbs01.dbf
4 0 /u01/app/oradata/testdb/users01.dbf
5 0 /u01/app/oradata/testdb/example01.dbf
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> backup database format'/u01/app/rman_backup/Full_%U.bak';
6> backup archivelog all format'/u01/app/rman_backup/Arc_%U.bak';
7> release channel c1;
8> release channel c2;
9> release channel c3; }
2.8建立备库control文件
RMAN> backup device type disk format'/u01/app/rman_backup/Standby_%U.ctl' current controlfile for standby;
2.9将主库的备份集、口令文件、pfile传至备库
说明主、备库的sys用户密码需要相同:
[oracle@primary01 ~]$ ls -lh /u01/app/rman_backup/
total 1.2G
-rw-r----- 1 oracle oinstall 9.8M Mar 2 14:49 Arc_06qvfpo8_1_1.bak
-rw-r----- 1 oracle oinstall 3.5K Mar 2 14:49 Arc_07qvfpo8_1_1.bak
-rw-r----- 1 oracle oinstall 10K Mar 2 14:49Arc_08qvfpo8_1_1.bak
-rw-r----- 1 oracle oinstall 590M Mar 2 14:49 Full_01qvfpl5_1_1.bak
-rw-r----- 1 oracle oinstall 359M Mar 2 14:49 Full_02qvfpl5_1_1.bak
-rw-r----- 1 oracle oinstall 163M Mar 2 14:49 Full_03qvfpl6_1_1.bak
-rw-r----- 1 oracle oinstall 9.4M Mar 2 14:49 Full_04qvfpnr_1_1.bak
-rw-r----- 1 oracle oinstall 96K Mar 2 14:49Full_05qvfpo4_1_1.bak
-rw-r----- 1 oracle oinstall 9.4M Mar 2 14:53 Standby_09qvfpuo_1_1.ctl
$ scp /u01/app/rman_backup/* standby01:/u01/app/rman_backup/
$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwtestdb standby01:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwtestdb
$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/inittestdb.ora standby01:/u01/app/oracle/product/11.2.0/db_1/dbs/inittestdb.ora
3.备库准备工作:
3.1备库参数文件配置:
[oracle@standby01 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/dbs/inittestdb.ora
testdb.__db_cache_size=197132288
testdb.__java_pool_size=4194304
testdb.__large_pool_size=4194304
testdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set fromenvironment
testdb.__pga_aggregate_target=222298112
testdb.__sga_target=331350016
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=117440512
testdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oradata/testdb/control01.ctl','/u01/app/oradata/testdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='testdb'
*.db_file_name_convert='/u01/app/oradata/testdb','/u01/app/oradata/testdb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='std_db'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.fal_client='std_1522'
*.fal_server='pri_1522'
*.local_listener='std_1522'
*.log_archive_config='dg_config=(pri_db,std_db)'
*.log_archive_dest_1='location=/u01/app/archivelog/testdbvalid_for=(all_logfiles,all_roles) db_unique_name=std_db'
*.log_file_name_convert='/u01/app/oradata/testdb','/u01/app/oradata/testdb'
*.memory_target=553648128
*.open_cursors=300
*.processes=250
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.service_names='std_db'
*.sessions=280
*.undo_tablespace='UNDOTBS1'
以上红色字段为新建目录,***并标红字段为新增内容。
db_unique_name可以与主库不同。
db_file_name_convert、log_file_name_convert参数是做数据文件、日志文件的路径转换用,本实验未涉及到目录不一致可以省略。
转换:
*.db_file_name_convert='/u01/app/oradata/testdb','/u01/app/oradata/phydb'
fal是Fetch Archive Log的简写,它是dataguard主备之间GAP的处理机制。
当Primary Database的某些日志没有成功发送到Standby Database,这时候发生饿了归档裂缝(Archive Gap)。
Primary上不会有GAP,所以fal_server和fal_client也是只在standby上生效的参数,当然为了switch over需要同样会在primary端进行预设置。
缺失的这些日志就是裂缝(Gap),Data Guard能够自动检测,解决归档裂缝,不需要DBA的介入。
3.2创建相应目录及备份集文件检查:
[oracle@standby01 ~]$ mkdir -p/u01/app/oracle/admin/testdb/adump
[oracle@standby01 ~]$ mkdir -p /u01/app/oradata/testdb/
[oracle@standby01 ~]$ mkdir -p/u01/app/oracle/flash_recovery_area
[oracle@standby01 ~]$ mkdir -p /u01/app/archivelog/testdb
[oracle@standby01 ~]$ ls -lh /u01/app/rman_backup/
total 1.2G
-rw-r----- 1 oracle oinstall 9.8M Mar 2 14:54 Arc_06qvfpo8_1_1.bak
-rw-r----- 1 oracle oinstall 3.5K Mar 2 14:54 Arc_07qvfpo8_1_1.bak
-rw-r----- 1 oracle oinstall 10K Mar 2 14:54Arc_08qvfpo8_1_1.bak
-rw-r----- 1 oracle oinstall 590M Mar 2 14:55 Full_01qvfpl5_1_1.bak
-rw-r----- 1 oracle oinstall 359M Mar 2 14:55 Full_02qvfpl5_1_1.bak
-rw-r----- 1 oracle oinstall 163M Mar 2 14:55 Full_03qvfpl6_1_1.bak
-rw-r----- 1 oracle oinstall 9.4M Mar 2 14:55 Full_04qvfpnr_1_1.bak
-rw-r----- 1 oracle oinstall 96K Mar 2 14:55Full_05qvfpo4_1_1.bak
-rw-r----- 1 oracle oinstall 9.4M Mar 2 14:55 Standby_09qvfpuo_1_1.ctl
3.3创建spfile文件,并将备库置于nomount状态
[oracle@standby01 ~]$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount;
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfiletestdb.ora
3.4恢复备库的控制文件,并将数据库至于mount状态,restore数据文件:
[oracle@standby01 ~]$ rman target /
RMAN> restore standby controlfile from'/u01/app/rman_backup/Standby_09qvfpuo_1_1.ctl';
RMAN> alter database mount;
RMAN> run {
2> allocate channelc1 type disk;
3> allocate channelc2 type disk;
4> allocate channelc3 type disk;
5> restore database;
6> release channel c1;
7> release channel c2;
8> release channel c3; }
4.创建物理备库
4.1主库状态查看
SQL> column db_unique_name format a10;
SQL> SELECT db_unique_name, name, switchover_status,database_role, open_mode FROM v$database;
DB_UNIQUE_ NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
---------- --------- -------------------- --------------- ---------------------
pri_db TESTDB NOT ALLOWED PRIMARY READ WRITE
4.2备库状态查看
SQL> column db_unique_name format a10;
SQL> SELECT db_unique_name, name, switchover_status, database_role, open_mode FROM v$database;
DB_UNIQUE_ NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
---------- --------- -------------------- ---------------- --------------------
std_db TESTDB TO PRIMARY PHYSICAL STANDBY MOUNTED
4.3备库查看数据文件、控制文件、日志文件、确认开启日志归档
SQL> column name format a40;
SQL> SELECT name FROM v$datafile;
NAME
----------------------------------------
/u01/app/oradata/testdb/system01.dbf
/u01/app/oradata/testdb/sysaux01.dbf
/u01/app/oradata/testdb/undotbs01.dbf
/u01/app/oradata/testdb/users01.dbf
/u01/app/oradata/testdb/example01.dbf
SQL> SELECT name FROM v$controlfile;
NAME
----------------------------------------
/u01/app/oradata/testdb/control01.ctl
/u01/app/oradata/testdb/control02.ctl
SQL> col member format a45;
SQL> SELECT * FROM v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
1 ONLINE /u01/app/oradata/testdb/redo01.rdo NO
2 ONLINE /u01/app/oradata/testdb/redo02.rdo NO
3 ONLINE /u01/app/oradata/testdb/redo03.rdo NO
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/archivelog/testdb
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
4.4备库创建Standby Redo Log组数
公式如下:
如果主库是单实例库:Standby Redo Log组数=主库日志组总数 + 1
如果主库是RAC环境:Standby Redo Log组数=(所有节点中日志组数最大值 + 1) * RAC节点数
SQL> alter database add standby logfile thread 1 group 4 '/u01/app/oradata/testdb/stdredo04.rdo' size 50m;
SQL> alter database add standby logfile thread 1 group 5 '/u01/app/oradata/testdb/stdredo05.rdo' size 50m;
SQL> alter database add standby logfile thread 1 group 6 '/u01/app/oradata/testdb/stdredo06.rdo' size 50m;
SQL> alter database add standby logfile thread 1 group 7 '/u01/app/oradata/testdb/stdredo07.rdo' size 50m;
SQL> SELECT * FROM v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
1 ONLINE /u01/app/oradata/testdb/redo01.rdo NO
2 ONLINE /u01/app/oradata/testdb/redo02.rdo NO
3 ONLINE /u01/app/oradata/testdb/redo03.rdo NO
4 STANDBY /u01/app/oradata/testdb/stdredo04.rdo NO
5 STANDBY /u01/app/oradata/testdb/stdredo05.rdo NO
6 STANDBY /u01/app/oradata/testdb/stdredo06.rdo NO
7 STANDBY /u01/app/oradata/testdb/stdredo07.rdo NO
4.5备库listener注册状态查看
SQL> show parameter local_listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string std_1522
[oracle@standby01 ~]$ ps -ef | grep tns
oracle 10706 10665 0 15:42 pts/0 00:00:00 grep tns
[oracle@standby01 ~]$ lsnrctl start
4.6主库修改归档参数LOG_ARCHIVE_DEST_2
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/app/archivelog/t
estdb valid_for=(all_logfiles,
all_roles) db_unique_name=pri_
db
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
SQL> alter system set log_archive_dest_2='service=std_1522 lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=std_db';
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_2 string service=std_1522 lgwr sync val
id_for=(online_logfiles,primar
y_role) db_unique_name=std_db
4.7主库切换日志,让其产生归档日志:
SQL> SELECT group#, thread#, sequence#, members, archived, status FROM v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 10 1 YES INACTIVE
2 1 11 1 NO CURRENT
3 1 9 1 YES INACTIVE
SQL> alter systemswitch logfile;
SQL> alter systemswitch logfile;
SQL> alter systemswitch logfile;
4.8主库查看归档日志传送状态:
SQL> SELECT process, client_process, sequence#, status FROM v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 13 CLOSING
ARCH ARCH 10 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 12 CLOSING
LGWR LGWR 14 WRITING
SQL> SELECT group#, thread#, sequence#, members, archived,status FROM v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 13 1 YES INACTIVE
2 1 14 1 NO CURRENT
3 1 12 1 YES INACTIVE
5.验证Active Data Guard
5.1备库查看是否接受到来自主库的归档日志:
[oracle@standby01 ~]$ rman target /
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_nameSTD_DB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 9 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_9_905435865.dbf
1 1 10 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_10_905435865.dbf
2 1 11 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_11_905435865.dbf
4 1 12 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_12_905435865.dbf
5 1 13 A 02-MAR-16
Name:/u01/app/archivelog/testdb/1_13_905435865.dbf
5.2备库查看日志的应用状态:
SQL> column name format a50;
SQL> set line 200;
SQL> SELECT thread#, sequence#, name, applied FROM v$archived_log order by sequence#;
THREAD# SEQUENCE# NAME APPLIED
---------- ---------- -------------------------------------------------- ---------
1 10 /u01/app/archivelog/testdb/1_10_905435865.dbf NO
1 11 /u01/app/archivelog/testdb/1_11_905435865.dbf NO
5.3备库应用日志
5.3.1应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
5.3.2查看备库日志的应用状态:
SQL> SELECT thread#, sequence#, name, applied FROM v$archived_log order by sequence#;
THREAD# SEQUENCE# NAME APPLIED
---------- ---------- -------------------------------------------------- ---------
1 10 /u01/app/archivelog/testdb/1_10_905435865.dbf NO
1 11 /u01/app/archivelog/testdb/1_11_905435865.dbf NO
5.3.3备库查看日志传送状态:
SQL> SELECT process, client_process, sequence#, status FROM v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 11 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 12 CLOSING
RFS LGWR 13 IDLE
RFS UNKNOWN 0 IDLE
RFS N/A 0 IDLE
MRP0 N/A 13 APPLYING_LOG
RFS UNKNOWN 0 IDLE
5.4将备库置于open状态(11G新特性redo only with apply)
5.4.1打开备库
SQL> column db_unique_name format a10;
SQL> column name format a10;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannotopen standby database; media recovery session may be in progress
5.4.2备库取消应用日志,并打开(read only):
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> SELECT db_unique_name, name, switchover_status, database_role, open_mode FROM v$database;
DB_UNIQUE_ NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
---------- ---------- -------------------- ---------------- --------------------
std_db TESTDB NOT ALLOWED PHYSICALSTANDBY READ ONLY
5.4.3备库应用日志(redo only with apply):
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> SELECT db_unique_name,name, switchover_status, database_role, open_mode FROM v$database;
DB_UNIQUE_ NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
---------- ---------- -------------------- ---------------- --------------------
std_db TESTDB NOT ALLOWED PHYSICALSTANDBY READ ONLY WITH APPLY
SQL> SELECT thread#, sequence#, name, applied FROM v$archived_log order by sequence#;
THREAD# SEQUENCE# NAME APPLIED
-------------------- -------------------------------------------------- ---------
1 9/u01/app/archivelog/testdb/1_9_905435865.dbf YES
1 10/u01/app/archivelog/testdb/1_10_905435865.dbf YES
1 11/u01/app/archivelog/testdb/1_11_905435865.dbf YES
1 12/u01/app/archivelog/testdb/1_12_905435865.dbf YES
1 13/u01/app/archivelog/testdb/1_13_905435865.dbf YES
5.4.5在主库上创建表做数据测试:
5.4.5.1主库建表,并插入数据:
SQL> SELECT username, account_status FROM dba_users WHERE username='HR';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
HR OPEN
SQL> create table hr.table1(id int, name varchar2(20));
SQL> insert into hr.table1 values (01, 'Active DataGuard');
SQL> SELECT * FROM hr.table1;
ID NAME
---------- --------------------
1 Active DataGuard
SQL> commit;
5.4.5.2备库验证
SQL> SELECT * FROM hr.table1;
ID NAME
---------- --------------------
1 Active DataGuard
注:关于配置Data Guard的参数,可参考:
http://czmmiao.iteye.com/blog/1311070
转载于:https://blog.51cto.com/ospub/1748614