ORACLE10G Data Guard的安装笔记

1、首先配置备库

[root@yang_dg ~]# /usr/sbin/groupadd oinstall [root@yang_dg ~]# /usr/sbin/groupadd dba [root@yang_dg ~]# /usr/sbin/groupadd oper [root@yang_dg ~]# /usr/sbin/useradd -g oinstall -G dba,oper oracle [root@yang_dg ~]# passwd oracle Changing password for user oracle. New UNIX password: BAD PASSWORD: it is based on a dictionary word Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@yang_dg ~]# mkdir /u01/app/oracle/oradata -p [root@yang_dg ~]# mkdir /u01/app/oracle/product [root@yang_dg ~]# chown -R oracle:oinstall /u01 [root@yang_dg ~]# 配置内核参数 [root@yang_dg ~]# vi /etc/sysctl.conf  添加以下参数 kernel.shmall = 2097152 kernel.shmmax  = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-MAX = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default = 262144 net.core.rmem_max = 262144 net.core.wmem_default = 262144 net.core.wmem_max = 262144   [root@yang_dg ~]# sysctl –p [root@yang_dg ~]# vi /etc/security/limits.conf oracle soft nproc 2047  oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 [root@yang_dg ~]# vi /etc/pam.d/login session required pam_limits.so [root@yang_dg ~]# vi /etc/profile if [ $USER = "oracle" ]; then         if [ $SHELL = "/bin/ksh" ]; then               ulimit -p 16384               ulimit -n 65536         else               ulimit -u 16384 -n 65536         fi fi 检查软件包 rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' binutils \ compat-libstdc++-33 \ elfutils-libelf \ elfutils-libelf-devel \ gcc \ gcc-c++ \ glibc \ glibc-common \ glibc-devel \ glibc-headers \ ksh \ libaio \ libaio-devel \ libgcc \ libstdc++ \ libstdc++-devel \ make \ sysstat \ unixODBC \ unixODBC-devel \ libXp [root@yang_dg ~]# su - oracle [oracle@yang_dg ~]$ cat .bash_profile # .bash_profile   # Get the aliases and functions if [ -f ~/.bashrc ]; then         . ~/.bashrc fi   # User specific environment and startup programs   PATH=$PATH:$HOME/bin   export PATH unset USERNAME export   ORACLE_BASE=/u01/app/oracle export  ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 export   PATH=$ORACLE_HOME/bin:$PATH export   LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib export  ORACLE_SID=orcl export  NLS_LANG=AMERICAN_AMERICA.ZHS16GBK [oracle@yang_dg ~]$ 安装数据库软件 –只需要安装软件即可

 

 

2、搭建DATA GUARD

 

2.1、主库启动强制记录日志 （force logging）模式

 

查看是否启用force logging SQL> select FORCE_LOGGING from v$database;   FOR --- NO   SQL>   启用force logging SQL> alter database force logging;   Database altered.   确认启用 SQL> select FORCE_LOGGING from v$database;   FOR --- YES   SQL>

 

2.2、主库必须在归档模式下

SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            +DATA/orcl/ Oldest online log sequence     25 Next log sequence to archive   27 Current log sequence           27 SQL>   如果不是归档模式   SQL>shutdown immediate; SQL> startup mount SQL> alter database archivelog; SQL>alter database open;

 

2.3、添加standby redo log

首先确认主库的日志文件大小是相同的   SQL> select group# from v$log;       GROUP# ----------          1          2          3   SQL> select GROUP#,BYTES/1024/1024 || 'M' from v$log;       GROUP# BYTES/1024/1024||'M' ---------- -----------------------------------------          1 50M          2 50M          3 50M   SQL> 确定备库日志组的数目    每个线程的日志文件最大数目+1 ×线程最大数   SQL> select GROUP#,THREAD# ,BYTES/1024/1024 || 'M' from v$log;       GROUP#    THREAD# BYTES/1024/1024||'M' ---------- ---------- -----------------------------------------          1          1 50M          2          1 50M          3          1 50M   SQL> 根据公式 可得到  3+1*1=4   在主库添加4组日志文件 SQL> alter database add standby logfile group 4 ('+data') size 50M;   Database altered.   SQL> alter database add standby logfile group 5 ('+data') size 50M;   Database altered.   SQL> alter database add standby logfile group 6 ('+data') size 50M;   Database altered.   SQL> alter database add standby logfile group 7 ('+data') size 50M;   Database altered. alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/ group_7.314.848799759') size 50m; SQL> 创建完成需要检查 SQL> select GROUP#,THREAD# , SEQUENCE# ,ARCHIVED ,STATUS from v$standby_log;       GROUP#    THREAD#  SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ----------          4          0          0 YES UNASSIGNED          5          0          0 YES UNASSIGNED          6          0          0 YES UNASSIGNED          7          0          0 YES UNASSIGNED   SQL>

 

2.4、配置静态注测监听

为了减少出错率 可以使用图形界面配置 [root@yang ~]# su - oracle [oracle@yang ~]$ export DISPLAY=192.168.56.1:0.0 [oracle@yang ~]$ xhost + access control disabled, clients can connect from any host [oracle@yang ~]$ netmgr   首先配置静态注册的listener.ora     SQL> show parameter instance   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ active_instance_count                integer cluster_database_instances           integer     1 instance_groups                      string instance_name                        string      orcl   --区分大小写 instance_number                      integer     0 instance_type                        string      RDBMS open_links_per_instance              integer     4 parallel_instance_group              string parallel_server_instances            integer     1 SQL> 完成后保存   [oracle@yang ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/ [oracle@yang admin]$ ls listener.ora  samples  shrept.lst  sqlnet.log  sqlnet.ora  tnsnames.ora [oracle@yang admin]$ cat listener.ora （上面配置的就是红色的字体）     SID_LIST_ORCL =   (SID_LIST =     (SID_DESC =       (SID_NAME = orcl)       (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)       (PROGRAM = extproc)     )     (SID_DESC =       (GLOBAL_DBNAME = ORCL)       (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)       (SID_NAME = orcl)     )   ) ORCL =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.189)(PORT = 1521))     )     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     使静态注册生效 [oracle@yang admin]$ lsnrctl reload   LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-MAY-2014 02:21:17   Copyright (c) 1991, 2005, Oracle.  All rights reserved.   Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully [oracle@yang admin]$ 查看监听的状态 [oracle@yang admin]$ lsnrctl status   LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-MAY-2014 02:41:46   Copyright (c) 1991, 2005, Oracle.  All rights reserved.   Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date                29-MAY-2014 02:35:43 Uptime                    0 days 0 hr. 6 min. 2 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yang)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s).   Instance "+ASM", status BLOCKED, has 1 handler(s) for this service... Service "+ASM_XPT" has 1 instance(s).   Instance "+ASM", status BLOCKED, has 1 handler(s) for this service... Service "ora10gs" has 1 instance(s).   Instance "orcl", status READY, has 2 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...   Instance "orcl", status READY, has 1 handler(s) for this service... Service "orcl_XPT" has 1 instance(s).   Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@yang admin]$   配置服务别名 [oracle@yang admin]$ vi tnsnames.ora      添加下列配置   ORCL_PD =                 -----主库别名    (DESCRIPTION =      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.189)(PORT = 1521))      )      (CONNECT_DATA =        (SERVER = DEDICATED)        (SID = orcl)      )    ) ORCL_ST =                 ------备库别名    (DESCRIPTION =      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.190)(PORT = 1521))      )      (CONNECT_DATA =        (SERVER = DEDICATED)        (SID = orcl)      )    )   查看是否有语法错误 [oracle@yang admin]$ tnsping orcl_pd   TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 29-MAY-2014 02:50:09   Copyright (c) 1997, 2005, Oracle.  All rights reserved.   Used parameter files: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora     Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.189)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = orcl))) OK (20 msec) [oracle@yang admin]$ tnsping orcl_st   TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 29-MAY-2014 02:50:16   Copyright (c) 1997, 2005, Oracle.  All rights reserved.   Used parameter files: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora     Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.190)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = orcl))) TNS-12541: TNS:no listener     ---因为备库还没有配监听 可以看到语法没有错误 [oracle@yang admin]$

2.5、创建备库密码文件

 

查看主库的密码文件 [oracle@yang ~]$ cd $ORACLE_HOME/dbs [oracle@yang dbs]$ ls ab_+ASM.dat     hc_orcl.dat   lkORCL              orapw+ASM alert_orcl.log  hc_ould.dat   lkQWE               orapworcl hc_+ASM.dat     hc_qwe.dat    lkTSPITR_ORCL_EAPV  scott1.dmp hc_eApv.dat     initdw.ora    lkTSPITR_ORCL_FQDH  snapcf_orcl.f hc_FqDh.dat     init.ora      lkTSPITR_ORCL_FVMP  spfile+ASM.ora hc_fvmp.dat     initorcl.ora  lkTSPITR_ORCL_KTJF  sqlnet.log hc_ktjf.dat     lk+ASM        lkTSPITR_ORCL_OULD  wallet [oracle@yang dbs]$   将主库密码文件传输到备库 [oracle@yang dbs]$ scp orapworcl 192.168.56.190:/u01/app/oracle/product/10.2.0/db_1/dbs/ The authenticity of host '192.168.56.190 (192.168.56.190)' can't be established. RSA key fingerprint is 02:97:5b:2f:6e:14:ce:ae:87:d7:4b:f0:05:0f:fb:e7. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.56.190' (RSA) to the list of known hosts. oracle@192.168.56.190's password: oracle orapworcl                                     100% 1536     1.5KB/s   00:00    [oracle@yang dbs]$   到备库确认 [oracle@yang_dg ~]$ cd $ORACLE_HOME/dbs [oracle@yang_dg dbs]$ ls initdw.ora  init.ora  orapworcl [oracle@yang_dg dbs]$

2.6、设置初始化参数文件

SQL> alter system set DB_UNIQUE_NAME='orcl_pd' scope=spfile;     ----设置主数据库服务别名为orcl_pd   System altered.   SQL> SQL> startup force;      ----因为是静态参数所以需要重启生效   SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)';  --告诉oracle 哪个是主库，哪些是备库   System altered.   SQL> SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_pd';  -- 指定主库的归档日志的存放位置   System altered.   SQL>------一定要建立路径 [oracle@yang dbs]$ mkdir /u01/arch1 这是Data guard 最主要的参数之一：直接设定Data guard 的保护级别 SQL> alter system set LOG_ARCHIVE_DEST_2='service=orcl_st   DB_UNIQUE_NAME=orcl_st';  ---指定远程的位置与名称                                                  （这里没有指定传输模式，默认为归档之后同步，参数lgwr async 异步与lgwr sync 实时同步） System altered.   SQL> SQL> alter system set FAL_SERVER=orcl_st;    ----日志出现缝隙时 找的远程服务器   System altered.   SQL> alter system set FAL_CLIENT=orcl_pd;     ----主库的服务器名   System altered.   SQL>   SQL> alter system set standby_file_management='AUTO';  ---在主库添加或删除数据文件时 备库做同样操作   System altered.   SQL> SQL> alter system set standby_archive_dest='/u01/arch1';    ---设置standby的归档日志路径   System altered.   SQL>   如果主库与备库的文件存放路径不同 SQL> alter system set log_file_name_convert='+data/orcl/ONLINELOG/','/u01/app/oracle/oradata/orcl/' scope=spfile;   System altered.   SQL> alter system set db_file_name_convert='+data/orcl/DATAFILE/','/u01/app/oracle/oradata/orcl/' scope=spfile;   System altered.   SQL> startup force      ---重启生效

2.7、创建备库中相应的文件夹

[oracle@yang_dg ~]$ cd /u01/app/oracle/oradata [oracle@yang_dg oradata]$ mkdir orcl [oracle@yang_dg dbs]$ cd /u01/app/oracle/ [oracle@yang_dg oracle]$ mkdir flash_recovery_area  [oracle@yang_dg ~]$ mkdir /u01/app/oracle/admin/orcl -p [oracle@yang_dg ~]$ cd /u01/app/oracle/admin/orcl/ [oracle@yang_dg orcl]$ mkdir adump [oracle@yang_dg orcl]$ mkdir bdump [oracle@yang_dg orcl]$ mkdir cdump [oracle@yang_dg orcl]$ mkdir dpdump [oracle@yang_dg orcl]$ mkdir udump [oracle@yang_dg orcl]$ mkdir pfile [oracle@yang_dg orcl]$ ls adump  bdump  cdump  dpdump  pfile  udump [oracle@yang_dg orcl]$ 建立归档目录 [oracle@yang_dg orcl]$ mkdir /u01/arch1

2.8、修改备库参数文件

可以选择将主库的参数文件拷贝到备库   SQL> create pfile from spfile;   File created.   SQL> [oracle@yang dbs]$ pwd /u01/app/oracle/product/10.2.0/db_1/dbs [oracle@yang dbs]$ ls init* initdw.ora  init.ora  initorcl.ora [oracle@yang dbs]$ [oracle@yang dbs]$ scp initorcl.ora 192.168.56.190:/u01/app/oracle/product/10.2.0/db_1/dbs/ oracle@192.168.56.190's password: initorcl.ora                                  100% 1661     1.6KB/s   00:00    [oracle@yang dbs]$ 到备库确认 [oracle@yang_dg orcl]$ cd $ORACLE_HOME/dbs [oracle@yang_dg dbs]$ ls initdw.ora  init.ora  initorcl.ora  orapworcl [oracle@yang_dg dbs]$ 根据实际情况修改备库参数文件（我的主库为ASM磁盘管理，我的备库为文件系统） [oracle@yang_dg dbs]$ vi initorcl.ora   *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='NONE' *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata/orcl/' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_file_name_convert='+data/orcl/DATAFILE/','/u01/app/oracle/oradata/orcl/' *.db_flashback_retention_target=5400 *.db_name='orcl' *.db_recovery_file_dest_size=2147483648 *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/' *.db_unique_name='orcl_st' *.dispatchers='(protocol=tcp)(service=ora10gs)(dispatchers=2)' *.fal_client='ORCL_ST' *.fal_server='ORCL_PD' *.job_queue_processes=10 *.log_archive_config='DG_CONFIG=(orcl_pd,orcl_st)' *.log_archive_dest_1='LOCATION=/u01/arch1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_st' *.log_archive_dest_2='service=orcl_pd DB_UNIQUE_NAME=orcl_pd' *.log_archive_format='%t_%s_%r.dbf' *.log_file_name_convert='+data/orcl/ONLINELOG/','/u01/app/oracle/oradata/orcl/' *.open_cursors=300 *.pga_aggregate_target=199229440 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' orcl.resource_manager_plan='UPLAN' *.resource_manager_plan='UPLAN' *.sga_target=598736896 *.shared_servers=2 *.standby_archive_dest='/u01/arch1' *.standby_file_management='AUTO' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

 

2.9、备库监听配置

可以从主库拷贝到备库 [oracle@yang dbs]$ cd $ORACLE_HOME/network/admin [oracle@yang admin]$ ls listener.ora  samples  shrept.lst  sqlnet.log  sqlnet.ora  tnsnames.ora [oracle@yang admin]$ scp listener.ora 192.168.56.190:$ORACLE_HOME/network/admin/ oracle@192.168.56.190's password: listener.ora                                  100%  654     0.6KB/s   00:00    [oracle@yang admin]$ [oracle@yang admin]$ scp tnsnames.ora 192.168.56.190:$ORACLE_HOME/network/admin/ oracle@192.168.56.190's password: tnsnames.ora                                  100% 1401     1.4KB/s   00:00    [oracle@yang admin]$ 备库修改监听文件 [oracle@yang_dg admin]$ vi listener.ora   # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools.   SID_LIST_ORCL =   (SID_LIST =     (SID_DESC =       (SID_NAME = orcl)       (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)       (PROGRAM = extproc)     )     (SID_DESC =       (GLOBAL_DBNAME = ORCL)       (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)       (SID_NAME = orcl)     )   )   ORCL =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.190)(PORT = 1521))     )     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )   ) 启动监听 [oracle@yang_dg admin]$ lsnrctl start   修改tnsnames.ora文件 [oracle@yang_dg admin]$ vi tnsnames.ora   # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.   ORCL10G =    (DESCRIPTION =      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.190)(PORT = 1521))      )      (CONNECT_DATA =        (SERVER = DEDICATED)        (SID = orcl)      )    )   ORCL_PD =    (DESCRIPTION =      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.189)(PORT = 1521))      )      (CONNECT_DATA =        (SERVER = DEDICATED)        (SID = orcl)      )    ) ORCL_ST =    (DESCRIPTION =      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.190)(PORT = 1521))      )      (CONNECT_DATA =        (SERVER = DEDICATED)        (SID = orcl)      )    )

 

2.10、使用RMAN备份主库

先建立备份目录 [oracle@yang ~]$ mkdir /u01/backup   [oracle@yang ~]$ rman target sys/oracle@orcl_pd   Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 07:33:41 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   connected to target database: ORCL (DBID=1374419528)   RMAN>run { allocate channel cl type disk; allocate channel c2 type disk; sql 'alter system archive log current'; backup current controlfile for standby format='/u01/backup/control_%U'; backup format '/u01/backup/orcl_%U_%T' skip inaccessible filesperset 5 database; sql 'alter system archive log current'; backup format '/u01/backup/arch_%U_%T' skip inaccessible filesperset 5 archivelog all delete input; release channel c2; release channel c1; }     在备份创建文件夹 [oracle@yang_dg ~]$ mkdir /u01/backup [oracle@yang_dg ~]$ chmod 777 /u01/backup [oracle@yang_dg ~]$ 将备份的资源共享给备库 [oracle@yang backup]$ su - root Password: [root@yang ~]# vi /etc/exports   /u01/backup 192.168.56.190(rw,async,anonuid=65534,anongid=65534) [root@yang ~]# service nfs start    --启动NFS服务 Starting NFS services:  [  OK  ] Starting NFS quotas: [  OK  ] Starting NFS daemon: [  OK  ] Starting NFS mountd: [  OK  ] [root@yang ~]# [root@yang ~]# chkconfig nfs on     设置开机启动 [root@yang ~]# chkconfig --list nfs nfs             0:off   1:off   2:on    3:on    4:on    5:on    6:off [root@yang ~]# 在备库挂载nfs [oracle@yang_dg ~]$ su - root [root@yang_dg ~]# mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 192.168.56.189:/u01/backup /u01/backup [root@yang_dg ~]# [root@yang_dg ~]# cd /u01/backup [root@yang_dg backup]# ls arch_20p9fv3n_1_1_20140529  control_1sp9fv0n_1_1 arch_21p9fv3n_1_1_20140529  orcl_1tp9fv0r_1_1_20140529 arch_22p9fv3t_1_1_20140529  orcl_1up9fv0s_1_1_20140529 [root@yang_dg backup]#

 

2.11、使用RMAN复制数据库到备库

在备库启动数据库 [oracle@yang_dg admin]$ sqlplus / as sysdba   SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 08:55:49 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> startup nomount; ORACLE instance started.   Total System Global Area  599785472 bytes Fixed Size                  2022632 bytes Variable Size             163578648 bytes Database Buffers          432013312 bytes Redo Buffers                2170880 bytes SQL> exit     ----一定要保证没有人连接备库   主库登录到RMAN 并连接辅助数据库为备库 [oracle@yang admin]$ rman target / auxiliary sys/oracle@orcl_st   Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 08:59:26 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   connected to target database: ORCL (DBID=1374419528) connected to auxiliary database: ORCL (not mounted)   RMAN> duplicate target database for standby nofilenamecheck dorecover;   Starting Duplicate Db at 29-MAY-14 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=156 devtype=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: sid=155 devtype=DISK   contents of Memory Script: {    set until scn  905167;    restore clone standby controlfile;    sql clone 'alter database mount standby database'; } executing Memory Script   executing command: SET until clause   Starting restore at 29-MAY-14 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2   channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/control_1sp9fv0n_1_1 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/backup/control_1sp9fv0n_1_1 tag=TAG20140529T074111 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output filename=/u01/app/oracle/oradata/orcl/control01.ctl output filename=/u01/app/oracle/oradata/orcl/comtrol02.ctl Finished restore at 29-MAY-14   sql statement: alter database mount standby database released channel: ORA_AUX_DISK_1 released channel: ORA_AUX_DISK_2   contents of Memory Script: {    set until scn  905167;    set newname for tempfile  1 to  "+DATA/orcl/tempfile/temp1.dbf";    switch clone tempfile all;    set newname for datafile  1 to  "/u01/app/oracle/oradata/orcl/system.256.846638591";    set newname for datafile  2 to  "/u01/app/oracle/oradata/orcl/undotbs1.258.846638595";    set newname for datafile  3 to  "/u01/app/oracle/oradata/orcl/sysaux.257.846638593";    set newname for datafile  4 to  "/u01/app/oracle/oradata/orcl/users.259.846638599";    set newname for datafile  5 to  "/u01/app/oracle/oradata/orcl/example.269.846638953";    set newname for datafile  6 to  "/u01/app/oracle/oradata/orcl/u2.281.847420303";    restore    check readonly    clone database    ; } executing Memory Script   executing command: SET until clause   executing command: SET NEWNAME   renamed temporary file 1 to +DATA/orcl/tempfile/temp1.dbf in control file   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   Starting restore at 29-MAY-14 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=155 devtype=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: sid=156 devtype=DISK   channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs1.258.846638595 restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux.257.846638593 restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example.269.846638953 channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/orcl_1up9fv0s_1_1_20140529 channel ORA_AUX_DISK_2: starting datafile backupset restore channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system.256.846638591 restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users.259.846638599 restoring datafile 00006 to /u01/app/oracle/oradata/orcl/u2.281.847420303 channel ORA_AUX_DISK_2: reading from backup piece /u01/backup/orcl_1tp9fv0r_1_1_20140529 channel ORA_AUX_DISK_2: restored backup piece 1 piece handle=/u01/backup/orcl_1tp9fv0r_1_1_20140529 tag=TAG20140529T074115 channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:26 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/backup/orcl_1up9fv0s_1_1_20140529 tag=TAG20140529T074115 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:51 Finished restore at 29-MAY-14   contents of Memory Script: {    switch clone datafile all; } executing Memory Script   datafile 1 switched to datafile copy input datafile copy recid=24 stamp=848826200 filename=/u01/app/oracle/oradata/orcl/system.256.846638591 datafile 2 switched to datafile copy input datafile copy recid=25 stamp=848826200 filename=/u01/app/oracle/oradata/orcl/undotbs1.258.846638595 datafile 3 switched to datafile copy input datafile copy recid=26 stamp=848826200 filename=/u01/app/oracle/oradata/orcl/sysaux.257.846638593 datafile 4 switched to datafile copy input datafile copy recid=27 stamp=848826200 filename=/u01/app/oracle/oradata/orcl/users.259.846638599 datafile 5 switched to datafile copy input datafile copy recid=28 stamp=848826200 filename=/u01/app/oracle/oradata/orcl/example.269.846638953 datafile 6 switched to datafile copy input datafile copy recid=29 stamp=848826200 filename=/u01/app/oracle/oradata/orcl/u2.281.847420303   contents of Memory Script: {    set until scn  905167;    recover    standby    clone database     delete archivelog    ; } executing Memory Script   executing command: SET until clause   Starting recover at 29-MAY-14 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2   starting media recovery   channel ORA_AUX_DISK_1: starting archive log restore to default destination channel ORA_AUX_DISK_1: restoring archive log archive log thread=1 sequence=31 channel ORA_AUX_DISK_1: restoring archive log archive log thread=1 sequence=32 channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/arch_22p9fv3t_1_1_20140529 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/backup/arch_22p9fv3t_1_1_20140529 tag=TAG20140529T074246 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 archive log filename=/u01/arch1/1_31_846638803.dbf thread=1 sequence=31 channel clone_default: deleting archive log(s) archive log filename=/u01/arch1/1_31_846638803.dbf recid=1 stamp=848826203 archive log filename=/u01/arch1/1_32_846638803.dbf thread=1 sequence=32 channel clone_default: deleting archive log(s) archive log filename=/u01/arch1/1_32_846638803.dbf recid=2 stamp=848826203 media recovery complete, elapsed time: 00:00:02 Finished recover at 29-MAY-14 Finished Duplicate Db at 29-MAY-14   RMAN>    ---------如果没有成功指示归档日志没有找到 可以删除备库的所有文件及/u01/app/oracle/admin下所有子目录下的文件                   在删除主库/u01/backup/下的所有备份 在关闭主库 启动mount状态切换到noarchivelog 启动数据库，切换日志在关闭数据库              在切换到archivelog   在重新做备份   备库的操作： rm –rf /u01/app/oracle/oradata/orcl/* rm –rf /u01/app/oracle/admin/adump/* rm –rf /u01/app/oracle/admin/bdump/* rm –rf /u01/app/oracle/admin/udump/* 主库的操作： rm –rf /u01/backup/* sqlplus / as sysdba shutdown immediate; startup mount; alter database noarchivelog; alter database open; alter system switch logfile; shutdown immediate; startup mount; alter database archivelog; alter database open; alter system switch logfile; 查看主库与备库/u01/arch1目录是否有归档 知道切换出为止 然后重新备份 与同步       可以检查数据文件是否存放到备库指定路径 oracle@yang_dg orcl]$ pwd /u01/app/oracle/oradata/orcl [oracle@yang_dg orcl]$ ls comtrol02.ctl          sysaux.257.846638593  undotbs1.258.846638595 control01.ctl          system.256.846638591  users.259.846638599 example.269.846638953  u2.281.847420303 [oracle@yang_dg orcl]$ 启动备库 查看当前状态 [oracle@yang_dg arch1]$ sqlplus / as sysdba   SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 09:31:34 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options   SQL> select status from v$instance;   STATUS ------------ MOUNTED      -可以看到现在自动启动到mount状态   SQL>

 

2.12、启动日志应用确认是否同步

启用日志应用 SQL> alter database recover managed standby database disconnect from session;   Database altered.   SQL>  验证是否同步   在主库日志切换 SQL> alter system switch logfile;   System altered.   SQL> 查询主库当前最大的日志序列号 SQL>select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             33   SQL> 查询备库当前最大日志序列号 SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             33   SQL> 再切换一次查看 SQL> alter system switch logfile;   System altered.   SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             34   SQL> 在查询备库最大日志序列号 SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             34   SQL>

2.13、创建备库的spfile以spfile启动备库到mount

SQL> create spfile from pfile;   File created.   SQL> alter database recover managed standby database cancel;   Database altered.   SQL> shutdown immediate; ORA-01109: database not open     Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.   Total System Global Area  599785472 bytes Fixed Size                  2022632 bytes Variable Size             163578648 bytes Database Buffers          432013312 bytes Redo Buffers                2170880 bytes Database mounted. SQL> alter database recover managed standby database disconnect from session;   Database altered.   SQL> show parameter spfile;   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ spfile                               string      /u01/app/oracle/product/10.2.0                                                  /db_1/dbs/spfileorcl.ora SQL>

2.14 创建备库的standby

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/group_4.308.848799705') size 50m;   SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/ group_5.310.848799727') size 50m;   SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/ group_6.312.848799743') size 50m;   SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/ group_7.314.848799759') size 50m;

 

3查看数据库的模式与如何验证

3.1、查看主库与备库

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE from v$database;   NAME      DB_UNIQUE_NAME                 DATABASE_ROLE --------- ------------------------------ ---------------- ORCL      orcl_st                        PHYSICAL STANDBY    --物理备库   SQL> SQL>  select NAME,DB_UNIQUE_NAME,DATABASE_ROLE from v$database;   NAME      DB_UNIQUE_NAME                 DATABASE_ROLE --------- ------------------------------ ---------------- ORCL      orcl_pd                        PRIMARY   ---主库   SQL>

 

3.2、归档日志的最大值不一致 （有缝隙）

SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             34   SQL> 在查询备库最大日志序列号 SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             34   如果不一致 ----gap     要求在备库取消日志应用 在开启 SQL> alter database recover managed standby database cancel; SQL> alter database recover managed standby database disconnect from session;   再次查询 SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             34   SQL> 在查询备库最大日志序列号 SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) --------------             34 看主库与备库的时间是否同步(如果不同步 下列配置) 主库： [root@yang ~]# chkconfig time on 备库： [root@yang_dg ~]# crontab –e   添加以下内容 */1 * * * * rdate -s 192.168.56.189     ---主库的IP    [root@yang_dg ~]# crontab -l */1 * * * * rdate -s 192.168.56.189 [root@yang_dg ~]# rdate -s 192.168.56.189 [root@yang_dg ~]# 需要重启确认 如果还不同步查询视图 SQL> select * from v$archive_gap;   no rows selected   SQL> 查看参数fal 是否设置正确 SQL> show parameter fal   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ fal_client                           string      ORCL_ST fal_server                           string      ORCL_PD SQL> 验证是否能通过服务器别名连接主库 oracle@yang_dg ~]$ sqlplus sys/oracle@orcl_pd as sysdba   SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 30 02:05:49 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options   SQL> Ping下TNS 看是否设置错误 [oracle@yang_dg ~]$ tnsping orcl_pd   TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 30-MAY-2014 02:06:48   Copyright (c) 1997, 2005, Oracle.  All rights reserved.   Used parameter files:     Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.189)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = orcl))) OK (10 msec) [oracle@yang_dg ~]$   都没有问题—可以手工解决同步   首先在主库查询 SQL> select thread#, sequence# from dba_logstdby_log l where next_change# not in (select first_change# from dba_logstdby_log where l.thread#=thread#)    order by thread#, sequence#;   返还哪些归档日志丢失   将丢失的归档从主库copy到备库中 [oracle@yang ~]# cd /u01/arch1 [oracle@yang arch1]# scp 1_34_846638803.dbf 192.168.56.190:/u01/arch1/   然后在备库中执行 物理备库： SQL> alter database register logfile '/u01/arch1/1_34_846638803.dbf’; 逻辑备库： SQL> alter database register  logical  logfile '/u01/arch1/1_34_846638803.dbf’; 最后重启备库

 

4、用只读方式打开备库

在打开之前需要取消日志应用 SQL> alter database recover managed standby database cancel;   以只读方式打开数据库 SQL> alter database open read only;   Database altered.   SQL> 这时候就可以查询到表了 SQL> select sal from scott.emp;          SAL ----------       1001       1700       1350       4176   在备库open状态下数不应用日志的   如果应用日志备库会自动到mount状态   SQL> alter database recover managed standby database disconnect from session;   Database altered.   SQL> select OPEN_MODE from v$database;   OPEN_MODE ---------- MOUNTED   SQL>

 

5、改变数据库的保护模式

修改主库的远端目标站的初始化参数文件。 SQL> show parameter log_archive_dest_2   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2                   string      service=orcl_st DB_UNIQUE_NAME                                                  =orcl_st SQL> SQL> alter system set log_archive_dest_2=’SERVICE=orcl_st LGWR SYNC AFFORM VALID=FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_st’ scope=both; 关闭主库所有实例 以独占模式启动数据库 SQL> shutdown immediate; SQL>startup mount exclusive; 通过alter database 命令改变保护模式 SQL> alter database set standby to maximize availability; 打开数据库所有实例 SQL>alter database open;

 

6、物理备库转为逻辑备库

1.在物理备库上停止日志应用服务 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;     Database altered.   2. 重新设置主库为将来的角色转换做准备（switchover） 逻辑备库和物理备库不一样，在进行SQL应用的时候还会产生日志，即逻辑备库的在线重做日志，因此逻辑备库不但要对从主库传过来的Standby日志进行归档，还必须得对备库自己产生的在线日志进行归档。下面我们假定log_archive_dest_1指定standby日志的归档路径，log_archive_dest_3指定在线日志的归档路径。 虽然主库并不需要配置两个归档路径，但为了方便将来可能的角色转换（switchover），一般建议在主库中也做相应的配置。 首先，查看当前主库的log_archive_dest_1： SQL> show parameter log_archive_dest_1    NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1                   string      LOCATION=/u01/arch1 VALID_FOR=                                                  (ALL_LOGFILES,ALL_ROLES) DB_UN                                                  IQUE_NAME=orcl_pd log_archive_dest_10                  string 需要把valid_for属性修改为只对在线日志生效： SQL>alter system set log_archive_dest_1='location=/u01/arch1  valid_for=(online_logfiles,all_roles) db_unique_name=orcl_pd';      System altered.   接着在OS上新建standby归档目录，新增的log_archive_dest_3指向它： SQL> alter system set log_archive_dest_3='location=/u01/arch2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl_pd';      System altered.      SQL> alter system set log_archive_dest_state_3=enable;      System altered.   3. 在主库构建LogMiner字典 SQL> EXECUTE DBMS_LOGSTDBY.BUILD;      PL/SQL procedure successfully completed.   4. 把物理备库转换成逻辑备库 首先修改备库数据库的名字 SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY orcl2;      Database altered.   关闭备库，重启至mount状态让其生效： SQL> shutdown immediate   SQL> startup mount   5. 调整逻辑备库参数 这一步和第二步类似，首先查看当前的log_archive_dest_1的配置： SQL> show parameter log_archive_dest_1   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1                   string      LOCATION=/u01/arch1 VALID_FOR=                                                  (ALL_LOGFILES,ALL_ROLES) DB_UN                                                  IQUE_NAME=orcl_st log_archive_dest_10                  string 需要把valid_for属性修改为只对在线日志生效： SQL> alter system set log_archive_dest_1='location=/u01/arch1 valid_for=(online_logfiles,all_roles) db_unique_name=orcl_st';      System altered.   log_archive_dest_2保持不变： SQL> show parameter log_archive_dest_2   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2                   string      service=orcl_pd DB_UNIQUE_NAME                                                  =orcl_pd 在OS上新建standby归档目录，新增的log_archive_dest_3指向它： SQL> alter system set log_archive_dest_3='location=/u01/arch2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl_st';      System altered.      SQL> alter system set log_archive_dest_state_3=enable;      System altered.   6）用resetlogs方式打开逻辑备库 SQL>alter database open resetlogs;   SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

 

7、物理备库的角色转换(switchover)

查询主库转换的可行性 SQL> select switchover_status from v$database;   SWITCHOVER_STATUS -------------------- TO STANDBY   ----或者     SESSION ACTIVE             SQL>   将和主库转换为物理备用数据库 返还TO STANDBY值：   SQL>alter database commit to switchover to physical standby;   返还SESSION ACTIVE值：   SQL>alter database commit to switchover to physical standby with session shutdown;   关闭主库并以物理备库的角色启动主库 SQL>shutdown immediate; SQL>startup mount;   到备库查询数据库状态 SQL> select switchover_status from v$database;   SWITCHOVER_STATUS -------------------- TO_PRIMARY   SQL> V$DATABASE视图switchover_status列返还值： TO_PRIMARY------说明标记已经恢复，可执行SWITCHOVER TO PRIMARY SESSION ACTIVE ----此时应该断开活动会话的连接或执行带有 session shutdown 子句的switchover NOT ALLOWED---说明标记还未接收到，也没有被备用数据库恢复不能运行switchover   将备库转换为主库 SQL>alter database commit to switchover to primary; switchover_status的返还值为SESSION ACTIVE： SQL>alter database commit to switchover to primary  with session shutdown;   关闭备库并重新启动启动为新的主库 SQL>shutdownimmediate; SQL>startup;

8、逻辑备库的角色转换（switchover）

查询主库转换的可行性 SQL> select switchover_status from v$database;   SWITCHOVER_STATUS -------------------- TO STANDBY   ----或者     SESSION ACTIVE –或者---TO LOGICAL STANDBY  (返还这几个值都可以进行角色转换)             SQL>   让主库预备转换为逻辑备库 SQL> alter database prepare to switchover to logical standby;   让备库预备转换为主库 （备库执行） SQL>alter database prepare to switchover to primary;   再次查询视图v$database SQL> select switchover_status from v$database;   SWITCHOVER_STATUS -------------------- TO LOGICAL STANDBY   ----只有此状态才可以进行角色转换   在主库运行：将主库转换为逻辑备库 (如果很长时间未成功，应检查V$TRANSACTION视图终止长时间运行的事务) SQL>alter database commit to switchover to logical standby; 到备库查询数据库状态 SQL> select switchover_status from v$database;   SWITCHOVER_STATUS -------------------- TO_PRIMARY  --次返还值有效   将逻辑备库转换为主库 SQL>alter database commit to switchover to primary; 启动逻辑应用 SQL>alter database start logical standby apply ; 启动逻辑应用并且变更从主数据库到达备库时及时加以应用 SQL> alter database start logical standby apply  immediate;

9、物理备库故障切换

查询备库中的日志丢失 SQL> select * from v$archive_gap;   如果有丢失通过主库或者其他备库复制缺失的日志，将归档序列号大于最后一个到达备库的归档日志全部复制到备库 [oracle@yang ~]$ cd /u01/arch1  [oracle@yang arch1]$ ls 1_33_846638803.dbf  1_34_846638803.dbf  1_35_846638803.dbf  1_36_846638803.dbf  [oracle@yang arch1]$ scp 1_36_846638803.dbf 192.168.56.190:/u01/arch1/   将这些复制的归档日志进行注册 SQL>alter database register physical logfile ‘/u01/arch1/1_36_846638803.dbf’;   如果备库有重做日志切处于激活状态。在备库执行恢复 SQL>alter database recover managed standby database finish; 如果备库没有重做日志或者没有激活： SQL>alter database recover managed standby database finish skip standby logfile; 将备库转换为主库 SQL>alter database commit to switchover to primary; 重新启动新的主库 SQL>shutdown immediate; SQL>startup; 最后需要对新的主库进行备份

10、逻辑备库故障切换

注意： 当通过逻辑备库实现故障切换时，旧的主库与物理备库将不再是Data Guard配置中的一部分。但是，在大多数情况下，配置中的其他逻辑备库依旧是处于Data Guard 的配置中。

查询备库中的日志丢失 SQL> select * from v$archive_gap;   如果有丢失通过主库或者其他备库复制缺失的日志，将归档序列号大于最后一个到达备库的归档日志全部复制到备库 [oracle@yang ~]$ cd /u01/arch1  [oracle@yang arch1]$ ls 1_33_846638803.dbf  1_34_846638803.dbf  1_35_846638803.dbf  1_36_846638803.dbf  [oracle@yang arch1]$ scp 1_36_846638803.dbf 192.168.56.190:/u01/arch1/   将这些复制的归档日志进行注册 SQL>alter database register logical logfile ‘/u01/arch1/1_36_846638803.dbf’; 查看视图 DBA_LOGSTDBY_PROGRESS确认什么时候完成所有可用事务的应用，当NEWEST_SCN 的值等于 APPLIED_SCN的值时，说明事务都已经应用。   SQL> select applied_scn,newest_scn from dba_logstdby_progress;   APPLIED_SCN    NEWEST_SCN ------------------------------------------------- 927632          927632 激活逻辑备库，将备库转换为新的主库 SQL>alter database stop logical standby apply; SQL>alter database activate logical standby database; 对新的数据库备份

 

11、在故障切换后使用闪回技术     

通过闪回数据库，可以将旧的主库恢复到故障发生前，并将它重新放到Data Guard的配置中，形成一个新的备库，然后通过switchover,也就是角色转换，可以将主库放回到最初的主机上。

这里假设主库是可以闪回的，并且已经对物理备库进行了故障切换。   在新的主库上查询备库是在那个SCN上变成的主库   SQL> select to_char(standby_became_primary_scn) from v$database;   TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 927630 SQL> 将旧的主库放到安装状态闪回到上面查询的SCN SQL>startup mount; SQL>flashback database to scn 927630; 在旧的主库上禁用闪回数据库，因为闪回日志将不在可用 SQL>alter database flashback off; 在旧的主库上创建备库控制文件 SQL>alter database create standby controlfile as  ‘/tmp/standby.ctl’; 将备库控制文件复制到初始化参数CONTRIL_FILES 中所指定的目录   SQL> show parameter control_files   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ control_files                        string      +DATA/orcl/controlfile/current                                                  .261.846638793, +DATA/orcl/con                                                  trolfile/current.260.846638797 SQL> 由于旧的主库是ASM管理的可以使用RMAN [oracle@yang ~]$ rman target / RMAN>alter database nomount; RMAN> restore controlfile   from '/tmp/standby.ctl’; RMAN> alter database mount; RMAN> recover database;   启用闪回数据库 SQL> alter database failover on; 在新的主库上启用一个指向主库的归档目标 SQL> alter system set log_archive_dest_state_2=enable; 在旧的主库上启用托管恢复。 SQL>alter database recover managed standby database disconnect;   这时候就可以使用switchover 进行角色转换------查看第七节的物理备库的角色转换

12、Data Guard 的代理和客户端

想要使用CLI接口时，主库与备库的dg_broker_start参数必须为true 主库与备库都要修改 SQL> show parameter dg_broker_start   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start                      boolean     FALSE   SQL> alter system set dg_broker_start=true;   System altered.   SQL> show parameter dg_broker_start   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start                      boolean     TRUE SQL> 主库与备库都必须应用的是spfile [oracle@yang ~]$ dgmgrl DGMGRL for Linux: Version 10.2.0.1.0 - 64bit Production   Copyright (c) 2000, 2005, Oracle. All rights reserved.   Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/oracle@orcl_pd Connected. DGMGRL> 这时候就可以进行配置了，第一步定义主数据库 DGMGRL> create configuration 'MYDG' as   ----这里只是定义一个名称 > primary database is 'orcl_pd'             ----这里定义的是DB_UNIQUE_NAME的值 > connect identifier is orcl_pd;             ----这里定义的是oracle Net的别名 Configuration "MYDG" created with primary database "orcl_pd" DGMGRL> 可以查看目前创建了什么 DGMGRL> show configuration   Configuration   Name:                MYDG   Enabled:             NO   Protection Mode:     MaxPerformance   Fast-Start Failover: DISABLED   Databases:     orcl_pd - Primary database   Current status for "MYDG": DISABLED   DGMGRL> 添加备库 DGMGRL> add database 'orcl_st' as     ----这里定义了备库DB_UNIQUE_NAME的值 > connect identifier is orcl_st         -----这里定义的是oracle net 别名 > maintained as physical; Database "orcl_st" added DGMGRL> 使我们的配置生效 DGMGRL> enable configuration; Enabled. DGMGRL> 再次查看命令运行的结果 DGMGRL> show configuration   Configuration   Name:                MYDG   Enabled:             YES   Protection Mode:     MaxPerformance   Databases:     orcl_pd - Primary database     orcl_st – Physical standby database Current status for "MYDG": SUCCESS   DGMGRL>   将备库改为只读模式 DGMGRL> edit database ‘orcl_st’ set state=’READ=ONLY’;   将备库至于恢复模式（也就是备库日志应用开启） DGMGRL> edit database ‘orcl_st’ set state=’ONLINE’;   使用命令行进行switchover角色转换 DGMGRL> switchover TO  “orcl_st”;

 

 

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

转载于:http://blog.itpub.net/29532781/viewspace-1176095/