ITPub博客

首页 > 数据库 > Oracle > dataguard安装和配置(DG)

dataguard安装和配置(DG)

原创 Oracle 作者:oracle_zsx 时间:2013-08-22 10:23:53 0 删除 编辑

1、
配置主库和备库信息
主库:
操作系统:oracle liunx 5.6
主机名:SZSCPDB
ip地址:192.168.20.10
oracle_sid:szsc
db_unqiue_name:szscpdb
监听名、端口:listener、1521
service_name:szscpdb
备库:
操作系统:oracle liunx 5.6
主机名:SZSCSTB
ip地址:192.168.20.11
oracle_sid:szsc
db_unqiue_name:szscstb
监听名、端口:listener、1521
service_name:szscstb

 

2、
启动主库的监听:
lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-MAY-2013 03:42:01

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SZSCPDB)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                15-MAY-2013 21:02:28
Uptime                    0 days 6 hr. 39 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/products/11.2.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/SZSCPDB/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SZSCPDB)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "szscXDB" has 1 instance(s).
  Instance "szsc", status READY, has 1 handler(s) for this service...
Service "szscpdb" has 2 instance(s).
  Instance "szsc", status UNKNOWN, has 1 handler(s) for this service...
  Instance "szsc", status READY, has 1 handler(s) for this service...
The command completed successfully

 

3、
查看数据库是否归档:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /RECO/arch/szsc/
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

 

4、
更改主库为force logging
SQL> alter database force logging;
Database altered.

 

5、
创建主库的密码文件
cd /u01/app/oracle/products/11.2.0/dbs/
orapwd file=$ORACLE_HOME/dbs/orapwszsc password=oracle entries=30;

 


6、
修改主库监听及tns文件
# listener.ora Network Configuration File: /u01/app/oracle/products/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = szscpdb)
      (ORACLE_HOME = /u01/app/oracle/products/11.2.0)
      (SID_NAME = szsc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SZSCPDB)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle


Vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/products/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SZSCPDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SZSCPDB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = szscpdb)
    )
  )

SZSCSTB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SZSCSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = szscstb)
    )
  )

 


7、
修改主库的参数文件
*.audit_file_dest='/u01/app/oracle/admin/szscpdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/szscpdb/control01.ctl','/u01/app/oracle/oradata/szscpdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szscpdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szscXDB)'
*.log_archive_config='dg_config=(szscpdb,szscstb)'
*.log_archive_dest_1='LOCATION=/RECO/arch/szsc/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=szscpdb'
*.log_archive_dest_2='SERVICE=szscstb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=szscstb'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscstb/','/u01/app/oracle/oradata/szscpdb/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscstb/','/u01/app/oracle/oradata/szscpdb/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=szscstb
fal_client=szscpdb
db_unique_name=szscpdb

 

8、
传输参数文件,密码文件,tns文件到备库
cd /u01/app/oracle/products/11.2.0/dbs
scp initszsc.ora orapwszsc SZSCSTB:$ORACLE_HOME/dbs
scp  orapwszsc SZSCSTB:/u01/app/oracle/products/11.2.0/dbs

cd /u01/app/oracle/products/11.2.0/network/admin/
scp tnsnames.ora SZSCSTB:$ORACLE_HOME/network/admin

 


9、
修改备库的参数文件
*.audit_file_dest='/u01/app/oracle/admin/szscstb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/szscstb/control01.ctl','/u01/app/oracle/oradata/szscstb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szscpdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szscXDB)'
*.log_archive_config='dg_config=(szscpdb,szscstb)'
*.log_archive_dest_1='LOCATION=/RECO/arch/szsc/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=szscstb'
*.log_archive_dest_2='SERVICE=szscpdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=szscpdb'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscpdb/','/u01/app/oracle/oradata/szscstb/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscpdb/','/u01/app/oracle/oradata/szscstb/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=szscpdb
fal_client=szscstb
db_unique_name=szscstb

 


10、
建立参数文件相关的目录
audit_file_dest
mkdir -p /u01/app/oracle/admin/szscstb/adump/

control_files
Mkdir -p '/u01/app/oracle/oradata/szscstb

 


11、
做一个主库的全备
mkdir  -p /u01/BACKUP/
chown -R oracle:oinstall /u01/BACKUP/
su - oracle
rman target /
RMAN>backup device type disk format '/u01/BACKUP/%U' database plus archivelog;

 


12、
传输备份到备库
scp  u01/backup/*  SZSCSTB:/u01/BACKUP/

 


13、
备库启动到nomount
su - oracle
sqlplus / as sysdba
SQL> startup nomount;

 


14、
主库 duplicate target database for standby;
在主库上运行
su - oracle

rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 8 02:41:18 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZSCPDB (DBID=850224774)

RMAN> connect auxiliary sys/oracle@SZSCSTB    

connected to auxiliary database: SZSCPDB (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 08-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script.:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 08-MAR-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /BACKUP/0lo3vm4i_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/BACKUP/0lo3vm4i_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/szscstb/control01.ctl
output file name=/u01/app/oracle/oradata/szscstb/control02.ctl
Finished restore at 08-MAR-13

contents of Memory Script.:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script.:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/szscstb/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/szscstb/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/szscstb/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/szscstb/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/szscstb/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/szscstb/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-MAR-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to //u01/app/oracle/oradata/szscstb/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/szscstb/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/szscstb/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/szscstb/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/BACKUP/0ko3vm18_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/BACKUP/0ko3vm18_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAR-13

contents of Memory Script.:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/users01.dbf
Finished Duplicate Db at 08-MAR-13

 


15、
执行备库恢复模式
SQL> select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
szsc               MOUNTED

SQL> alter database recover managed standby database disconnect from session;

Database altered.

 


16、
查看日志同步情况
SQL> SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED
     FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


17、
创建STANDBY LOGFILE
主库:
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo01.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo02.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo03.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo04.log' size 512M;

备库:
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo01.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo02.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo03.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo04.log' size 512M;


当做完上面的添加standby logfile的时候就可以执行下面的(备库上执行)
alter database recover managed standby database disconnect from session;

现在DG就已经配置完成了,可以查看两边的数据库情况。

 


switchover:

1、查询主库的switchover状态
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
注意:当是to standby或session active的时候可以切换


SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY ;
2、关闭szscpdb也就是旧的主库,启动到mount状态
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;

3、在备库上执行
alter database recover managed standby database disconnect from session;
然后在执行
SQL>  SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
当查询的值为TO PRIMARY的时候可以切换

4、在原来的备库上执行(szscstb)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WITH SESSION SHUTDOWN];
注意:WITH SESSION SHUTDOWN子句可以省略,当之前查询的结果为TO PRIMARYAD的时候

5、打开新的主库也就是原来的备库(szscstb)
alter database open;
select switchover_status from v$database;
这时候出现的结果应该是to standby


6、现在你在原来的主库(szscpdb)也就是现在的备库上查询
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
注意:出现这种情况就代表你切换成功了,就是原来的主库变成了物理备库,原来的备库变成了主库


7、现在验证切换成功,看看你新的主库的日志是不是能传到备库上
insert into t values(2);
commit;
alter system switch logfile;  (注意监测两边的alert日志)
现在:
在新的主库(原来的备库)上看归档日志序列
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /RECO/arch/szsc/
Oldest online log sequence     56
Next log sequence to archive   58
Current log sequence           58
在新的备库上(原来的主库上)看
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /RECO/arch/szsc/
Oldest online log sequence     56
Next log sequence to archive   0
Current log sequence           58
注意:现在已经证明了新主库上的日志已经可以传到新的备库上了


8、在新的备库上应用你传过来的日志
SQL> alter database recover managed standby database disconnect from session;
Database altered.
或者
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE  DISCONNECT FROM SESSION;

 

9、查看表数据
SQL> select * from t;
        ID
----------
         1
         2
成功完成了主备库的switchover


10、查看主备库的角色信息
在新主库上:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
在新的备库上:
SQL>  select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

 

 

 

 

如果备库还有没有应用的日志,那么就执行(这句话如果执行了就不用在执行)
alter database recover managed standby database disconnect from session;


查看备库的alert日志文件,会应用你的归档,如果发现它停在某个归档上,那么就是缺失这个归档,
这时候你需要将你主库的归档路径下的归档日志传到备库上,备库会自动应用的


当你的归档全部应用完成之后,就可以执行
alter database recover managed standby database disconnect from session;
或者执行:
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-08-22

  • 博文量
    25
  • 访问量
    135478