浅谈DataGuard主从安装及测试

浅谈DataGuard主从安装及测试

DataGuard:
primary : 192.168.0.111; db_name=orcl; db_unique_name=orcl
standby : 192.168.0.113; db_name=orcl; db_unique_name=aux1

在主库执行的操作：

1.主库要运行在归档模式

archive log list
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list

点击(此处)折叠或打开

1. SQL> archive log list
   
2. Database log mode     Archive Mode
   
3. Automatic archival     Enabled
   
4. Archive destination     /home/oracle/arc_orcl_dest1/
   
5. Oldest online log sequence 36
   
6. Next log sequence to archive 38
   
7. Current log sequence     38

2.主库要打开force logging

select force_logging from v$database;
NO/YES
alter database force logging;

点击(此处)折叠或打开

1. SQL> select force_logging from v$database;
   
2. 
   
3. FOR
   
4. ---
   
5. YES
   
6. 
   
7. Elapsed: 00:00:00.01

3.主库要打开dataguard开关

alter system set log_archive_config='dg_config=(主库的唯一名，从库的唯一名)';
alter system set log_archive_config='dg_config=(orcl,aux1)';

4.修改主库本地归档参数

mkdir -p /home/oracle/arc_orcl_dest1/
alter system set log_archive_dest_1='location=/home/oracle/arc_orcl_dest1/ valid_for=(日志类型,数据库角色) db_unique_name=orcl';
alter system set log_archive_dest_1='location=/home/oracle/arc_orcl_dest1/ valid_for=(online_logfiles,primary_role) db_unique_name=orcl';

5.主库启用远程归档参数

alter system set log_archive_dest_2='service=连接从库的服务命名 valid_for=(日志类型,数据库角色) db_unique_name=aux1';
alter system set log_archive_dest_2='service=aux1srv valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

6.在主库为从库拷贝口令文件

scp $ORACLE_HOME/dbs/orapw$ORACLE_SID oracle@192.168.0.113:$ORACLE_HOME/dbs/orapwaux1

7.为从库准备参数文件：使用主库的参数文件修改

SQL> create pfile from spfile;
scp $ORACLE_HOME/dbs/init$ORACLE_SID.ora oracle@192.168.0.113:$ORACLE_HOME/dbs/initaux1.ora
-----------------------------------------------------------------------------------------
*.audit_file_dest='/u01/app/oracle/admin/aux1/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/aux1/control01.ctl'
*.db_block_size=8192
*.db_domain='example.com'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_config='dg_config=(orcl,aux1)'
*.memory_target=843055104
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_unique_name='aux1'
log_archive_dest_3='location=/home/oracle/arc_aux1_dest3/ valid_for=(standby_logfiles,standby_role) db_unique_name=aux1'
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/'
-----------------------------------------------------------------------------------------
备注：db_file_name_convert及log_file_name_convert路径通过下面命令查看得出的，这里为

点击(此处)折叠或打开

1. SQL> select name from v$datafile;
   
2. 
   
3. NAME
   
4. ----------------------------------------------------------------------------------------------------
   
5. /u01/app/oracle/oradata/orcl/system01.dbf
   
6. /u01/app/oracle/oradata/orcl/sysaux01.dbf
   
7. /u01/app/oracle/oradata/orcl/undotbs01.dbf
   
8. /u01/app/oracle/oradata/orcl/users01.dbf

点击(此处)折叠或打开

1. SQL> select member from v$logfile;
   
2. 
   
3. MEMBER
   
4. ----------------------------------------------------------------------------------------------------
   
5. /u01/app/oracle/oradata/orcl/redo03.log
   
6. /u01/app/oracle/oradata/orcl/redo02.log
   
7. /u01/app/oracle/oradata/orcl/redo01.log

8.为从库准备相关目录

mkdir -p /u01/app/oracle/admin/aux1/adump
mkdir -p /u01/app/oracle/oradata/aux1/
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /home/oracle/arc_aux1_dest3/

9.修改从库环境变量，创建spfile，启动实例到nomount

export ORACLE_SID=aux1  #(当前会话有限，要是想永久生效，修改环境变量)类似如下：
[oracle@slave ~]$ vim .bashrc

点击(此处)折叠或打开

1. export ORACLE_BASE=/u01/app/oracle
   
2. export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
   
3. export PATH=$ORACLE_HOME/bin:$PATH
   
4. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
   
5. #export ORACLE_SID=orcl
   
6. export ORACLE_SID=aux1 #standby
   
7. export ORACLE_OWNER=oracle
   
8. export ORACLE_TERM=vt100
   
9. export THREADS_FLAG=native
   
10. export LANG=en_US

sqlplus / as sysdba
create spfile from pfile;
startup nomount

10.在从库配置并启动监听程序

vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

点击(此处)折叠或打开

1. SID_LIST_LISTENER =
   
2.   (SID_LIST =
   
3.     (SID_DESC =
   
4.       (GLOBAL_DBNAME = aux1.example.com)
   
5.       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
   
6.       (SID_NAME = aux1)
   
7.     )
   
8.   )
   
9. 
   
10. LISTENER =
    
11.   (DESCRIPTION_LIST =
    
12.     (DESCRIPTION =
    
13.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.113)(PORT = 1521))
    
14.     )
    
15.   )

备注：上面GLOBAL_DBNAME是通过show parameter name命令得出的
service_names                 string     aux1.example.com

lsnrctl stop
lsnrctl start

11.在主库配置服务命名

vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
-----------------------------------------------------------------------------------------
aux1srv =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = aux1.example.com)
    )
  )
-----------------------------------------------------------------------------------------

12.在主库使用网络连接从库

sqlplus sys/oracle@aux1srv as sysdba

13.在主库启动rman复制从库

rman target / auxiliary sys/oracle@aux1srv
RMAN> duplicate target database for standby from active database;

14.在从库增加standby log(和主库的online log必须一样大小)

select bytes from v$log;

点击(此处)折叠或打开

1. SQL> select bytes from v$log;
   
2. 
   
3.      BYTES
   
4. ----------
   
5.   52428800
   
6.   52428800
   
7.   52428800
   
8. 
   
9. Elapsed: 00:00:00.02

select member from v$logfile;

点击(此处)折叠或打开

1. SQL> select member from v$logfile;
   
2. 
   
3. MEMBER
   
4. ----------------------------------------------------------------------------------------------------
   
5. /u01/app/oracle/oradata/orcl/redo03.log
   
6. /u01/app/oracle/oradata/orcl/redo02.log
   
7. /u01/app/oracle/oradata/orcl/redo01.log

alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo04.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo05.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo06.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo07.log' size 52428800;
alter database add standby logfile '/u01/app/oracle/oradata/aux1/redo08.log' size 52428800;

15.打开从库

SQL> alter database open;

16.在从库启用日志应用

alter database recover managed standby database using current logfile disconnect from session;

17.在主库切换日志

select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;
SQL> alter system switch logfile;

点击(此处)折叠或打开

1. SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;
   
2. 
   
3.     GROUP# THREAD# SEQUENCE# STATUS
   
4. ---------- ---------- ---------- ----------------
   
5.      1     1     37 INACTIVE
   
6.      2     1     38 CURRENT
   
7.      3     1     36 INACTIVE
   
8. 
   
9. Elapsed: 00:00:00.00

18.在从库查看日志应用情况

SQL> select sequence#,applied from v$archived_log;

点击(此处)折叠或打开

1. SQL> select sequence#,applied from v$archived_log;
   
2. 
   
3.  SEQUENCE# APPLIED
   
4. ---------- ---------
   
5.     30 YES
   
6.     32 YES
   
7.     29 YES
   
8.     31 YES
   
9.     33 YES
   
10.     34 YES
    
11.     36 YES
    
12.     37 IN-MEMORY
    
13.     35 YES
    
14. 
    
15. 9 rows selected.
    
16. 
    
17. Elapsed: 00:00:00.01

19.在主库做数据修改，提交之后在从库看数据变化

主库：
SQL> select sal from scott.emp;
SQL> update scott.emp set sal=sal+1;
SQL> commit;
SQL> select sal from scott.emp;

点击(此处)折叠或打开

1. SQL> select sal from scott.emp;
   
2. 
   
3.        SAL
   
4. ----------
   
5.        812
   
6.       1612
   
7.       1262
   
8.       2987
   
9.       1262
   
10.       2862
    
11.       2462
    
12.       3012
    
13.       5012
    
14.       1512
    
15.       1112
    
16.        962
    
17.       3012
    
18.       1312
    
19. 
    
20. 14 rows selected.

从库：
SQL> select sal from scott.emp;

点击(此处)折叠或打开

1. SQL> select sal from scott.emp;
   
2. 
   
3.        SAL
   
4. ----------
   
5.        812
   
6.       1612
   
7.       1262
   
8.       2987
   
9.       1262
   
10.       2862
    
11.       2462
    
12.       3012
    
13.       5012
    
14.       1512
    
15.       1112
    
16.        962
    
17.       3012
    
18.       1312
    
19. 
    
20. 14 rows selected.
    
21. 
    
22. Elapsed: 00:00:00.00

可以看成主从数据已经同步了！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31403979/viewspace-2126918/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31403979/viewspace-2126918/