oracle data guard

1.配置oracle环境变量


export ORACLE_BASE=/export/home/oracle/ora12c    （对应oracle安装路径）
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:/bin:/sbin:/usr/ccs/bin:/usr/local/bin
export TMPDIR=/var/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/lib64:/usr/local/lib
umask 022 


2.开启监听


lsnrctl start


3.配置primary的listener.ora 和 tnsnames.ora


======================================================================================================
（/export/home/oracle/ora12c/product/12.1.0/db_home1/network/admin/listener.ora）
listener.ora


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /export/home/oracle/ora12c/product/12.1.0/db_home1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (ORACLE_HOME = /export/home/oracle/ora12c/product/12.1.0/db_home1) 
      (SID_NAME = orcl)
    )
  )




LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
  
=================================================================================================
 
  tnsnames.ora
  
  dbpry =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521))




dbstb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.231)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
===================================================================================================


将修改好的 listener.ora 和 tnsnames.ora 拷贝到 standby （修改listener.ora相应的路径）


scp -r listener.ora tnsnames.ora  192.168.1.118:/export/home/oracle/ora12c/product/12.1.0/dbhome_1/network/admin/


重新启动lsnrctl start --l lsnrctl stop
primary-----
$ sqlplus /nolog
SQL> conn sys/oracle@dbstb as sysdba
Connected to an idle instance.


standby----
$ sqlplus /nolog
SQL> conn sys/oracle@dbpry as sysdba
Connected.




4 强制primary force logging


SQL> alter database force logging;
SQL> create pfile from spfile;


5.创建pwd文件
orapwd file=orapwddbpry password=oracle entries=3 


6.修改initorcl.ora
添加-----------------




#DG CONFIG


log_archive_dest_1='LOCATION=/export/home/oracle/ora12c/fast_recovery_area/DBPRY/archivelog’ 


log_archive_dest_state_1=enable
log_archive_dest_2='SERVICE=dbstb' (standby unique id standby设置相反)
log_archive_dest_state_2=enable
log_archive_start=true
log_archive_format='%t_%s_%r.arc'
log_archive_max_processes=2
fal_client=dbpry (primary unique id  此参数在standby是dbstb)
fal_server=dbstb (standby unique id  此参数在standby是dbpry)
db_unique_name=dbpry (pry unique id  此参数在standby是dbstb)


***修改log_archive_dest_1 的值 （可以使用archive log list查找） 如果不存在则需要创建***






7.创建standby controlfile
startup mount;


alter database archivelog;


sql>archive log list 
Database log mode              Archive Mode
Automatic archival             Enabled(必须是enable模式）
Archive destination            /RECO/oreo/（归档路径）
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5


sql>shutdown immediate;


sql>startup mount;
sql> alter database create standby controlfile as '/export/home/oracle/ora12c/oradata/DBPRY/controlfile/standby.ctl';
sql> alter database create standby controlfile as '/export/home/oracle/ora12c/fast_recovery_area/DBPRY/controlfile/standby.ctl';
sql>shutdown immediate;










8.拷贝数据文件到standby
standby /export/home/oracle/ora12c/oradata 存放数据库数据


1:$ scp -r File-name standby-IP:/export/home/oracle/ora12c/oradata


如scp -r ORCL 192.168.1.233:/export/home/oracle/ora12c/oradata(检查目录结构，看是否在ORCL下)


standby  /export/home/oracle/ora12c/admin/ 


2:$scp -r primarySID standby-IP:/export/home/oracle/ora12c/admin/
如scp -r orcl 192.168.1.233:/export/home/oracle/ora12c/admin/(检查目录结构，看是否在orcl下)


3:$scp -r initora.ora orapworcl standby-IP:/export/home/oracle/ora12c/product/12.1.0/db_home1/dbs
  如scp -r initora12c1.ora orapwora12c1 192.168.1.118:/export/home/oracle/ora12c/product/12.1.0/dbhome_1/dbs


4:$ scp -r * 192.168.1.233:/export/home/oracle/ora12c/fast_recovery_area/


***需要将新的control file 替换原来的control 否则回报 ctl inconsistent **


standby 查看文件名 
$ls /export/home/oracle/ora12c/product/12.1.0/dbhome_1/dbs


$ mv initora12c1.ora initorcl.ora (initSID.ora)
$ mv orapwora12c1 orapworcl (oraSID)


$vi initorcl.ora  查看log_archive_dest_1路径是否存在不存在则创建，检查其他参数是否正确
====================================
#DG CONFIG


log_archive_dest_1='LOCATION=/export/home/oracle/ora12c/fast_recovery_area/DBPRY/archivelog' 


log_archive_dest_state_1=enable
log_archive_dest_2='SERVICE=dbpry'
log_archive_dest_state_2=enable
log_archive_start=true
log_archive_format='%t_%s_%r.arc'
log_archive_max_processes=2
fal_client=dbstb
fal_server=dbpry
db_unique_name=dbstb 
===================================================






$ cd /export/home/oracle/ora12c/oradata/
$ cp standby.ctl controlfile/


$ ls
 将新加入的ctl换成原来的文件名，备份原来的ctl文件或者删除
 
 9.启动主备库
 primary--
 xxx/dbs$ mv spfileSID.
 
 $ sqlplus /nolog
 sql>conn / as sysdba
 
sql> create spfile from pfile;
sql>startup




standby---
1。查看listener 启动 lsnrctl start


sql> create spfile from pfile; 
sql> startup nomount;
sql> alter database mount standby database;
sql> select name,database_role from v$database ;
sql> alter database recover managed standby database disconnect from session;(不是实时应用 延迟30分钟)


sql> select sequence#,first_time,next_time from v$archived_log order by sequence#;
 
 sql> select sequence#,applied from  v$archived_log order by sequence#;


sql> alter database recover managed standby database cancel;


sql> alter database open read only;
sql> alter system archive log current;




打开实时应用redo特性：
    物理standby： ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
    逻辑standby：ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


primary--
加速数据传递
sql>alter system switch logfile;








常见错误
1.shm max xxx======


#vi system
    添加如下内容
    set noexec_user_stack=1
    set semsys:seminfo_semmni=300
    set semsys:seminfo_semmns=1050
    set semsys:seminfo_semmsl=400
    set semsys:seminfo_semvmx=32767
    set shmsys:shminfo_shmmax=4294967295
    set shmsys:shminfo_shmmin=1
    set shmsys:shminfo_shmmni=300
    set shmsys:shminfo_shmseg=30


2.OS audit file could not be created;
路径没正确


3.
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:


这种情况，可能是两个原因造成
第一个可能是参数问题
第二个 就是主库在你RESTORE 到备库的时候进行了 归档， 而这个归档文件恰恰没有传输过来，
控制文件的信息记录了这个，但是没有这个文件 这个时候 就需要把归档目录下 缺失的文件拷贝到备库上，进行RECOVER即可。


（查看归档路径是否正确   $archive log list ）






ln -s 目标file 指定file (链接目标file到指定file)




ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> alter database recover managed standby database cancel;
Database altered.


SQL> alter database open;
Database altered.


SQL> alter database recover managed standby database using current logfile disconnect ; 






DGMGRL> show configuration;


Configuration - prod


  Protection Mode: MaxAvailability
  Databases:
    prod - Primary database
    sbdb - Physical standby database
      Error: ORA-16766: Redo Apply is stopped


Fast-Start Failover: DISABLED


Configuration Status:
ERROR


/* 以上可以看到物理备库SBDB上的REDO APPLY被停止了，我们可以在DGMGRL中启动其REDO APPLY */


DGMGRL> edit database sbdb set state='APPLY-ON';
Succeeded.


DGMGRL> show configuration;












*****************data backup***************






数据库备份**********************************




rman target /


run
{
 allocate channel c0 device type disk;
 allocate channel c1 device type disk;
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/RECO/rman/%F';
 backup  database format '/RECO/rman/ora11g_full_db_%d_%T_%u.bak';
 BACKUP ARCHIVELOG ALL FORMAT '/RECO/rman/ora11g_arc_%s_%p_%t.bak';
 }    
   
 
   
RMAN> list backup of controlfile;


using target database control file instead of recovery catalog


List of Backup Sets
===================




BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    9.36M      DISK        00:00:05     11-JAN-14      
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20140111T090409
        Piece Name: /RECO/rman/c-1347530101-20140111-00
  Control File Included: Ckp SCN: 1050232      Ckp time: 11-JAN-14
  
 
[oracle@oreopdb rman]$ scp * 192.168.1.233:/RECO/rman
oracle@oreostb's password: 
c-1347530101-20140111-00                                                                          100% 9600KB   9.4MB/s   00:01    
ora11g_arc_3_1_836557446.bak                                                                      100% 8990KB   8.8MB/s   00:00    
ora11g_arc_4_1_836557446.bak                                                                      100% 3473KB   3.4MB/s   00:01    
ora11g_full_db_OREO_20140111_01otpmf9.bak                                                         100%  602MB   8.6MB/s   01:10    
ora11g_full_db_OREO_20140111_02otpmf9.bak                                                         100%  458MB   6.5MB/s   01:10






#################################################
主库duplicate target database for standby


[oracle@oreopdb ~]$ rman target /


Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 11 09:14:07 2014


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


connected to target database: OREO (DBID=1347530101)


RMAN> connect auxiliary sys/oracle@dbstb


connected to auxiliary database: OREO (not mounted)


RMAN> duplicate target database for standby; duplicate target database for standby nofilenamecheck;  


将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE


ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE';


ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;




添加standby redo log
SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/ORADATA/oreopdb/redo03.log
/ORADATA/oreopdb/redo02.log
/ORADATA/oreopdb/redo01.log
         
SQL>SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
no rows selected


#FOR PRIMARY
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/export/home/oracle/ora12c/oradata/DBPRY/sredo04.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/export/home/oracle/ora12c/oradata/DBPRY/sredo05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/export/home/oracle/ora12c/oradata/DBPRY/sredo06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/export/home/oracle/ora12c/oradata/DBPRY/sredo07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/export/home/oracle/ora12c/oradata/DBPRY/sredo08.log' size 50M;
 


#FOR STANDBY
SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/ORADATA/oreostb/redo03.log
/ORADATA/oreostb/redo02.log
/ORADATA/oreostb/redo01.log




ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/export/home/oracle/ora12c/oradata/DBPRY/sredo04.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/export/home/oracle/ora12c/oradata/DBPRY/sredo05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/export/home/oracle/ora12c/oradata/DBPRY/sredo06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/export/home/oracle/ora12c/oradata/DBPRY/sredo07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/export/home/oracle/ora12c/oradata/DBPRY/sredo08.log' size 50M;
 


**********************************




 Oracle 10g R2 实现dataguard实时日志应用 




 在primary   select dest_name,status,recovery_mode from v$archive_dest_status; 检查是否是实时
 
SQL> select dest_name,status,recovery_mode from v$archive_dest_status
  2  ;


DEST_NAME
--------------------------------------------------------------------------------
STATUS             RECOVERY_MODE
------------------ ----------------------------------------------
LOG_ARCHIVE_DEST_1
VALID              MANAGED REAL TIME APPLY
 
SQL> show parameter log_archive_dest_2 (查看是否设置)


NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_2                   string
service=dbstb lgwr async
 
 
1.在主机端:设置归档方法: alter system set log_archive_dest_2='service=dbstb lgwr SYNC '; 其中dest为备机的tnsname,其他为归档关键字.




2.在备机端: 添加standby logfile :   使用此命令查看是否设置( SELECT GROUP#, BYTES FROM V$STANDBY_LOG; )
alter database add standby logfile
 group 5 '/db1/oradata/bbs/redo105.log' size 104858112 reuse,
 group 6 '/db1/oradata/bbs/redo106.log' size 104858112 reuse;
   
   
   
3.在备机端:启动数据库到实时恢复管理模式,
SQL> select process,status from v$managed_standby;


PROCESS            STATUS
------------------ ------------------------
ARCH               CONNECTED
ARCH               CONNECTED     将日志进行归档
MRP0               APPLYING_LOG   Managed recovery process (MRP)将归档文件应用到备用机上（正在使用,如没有则执行下面的command） 
RFS                IDLE           Remote file server (RFS) 负责从主数据库上接收归档文件
RFS                IDLE




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








--主要看RFS和MRP0
--RFS（Remote File Server）进程：接收primary数据库的redo，保存在standby redo log（arch模式不写standby，直接保存归档）
    状态值有：
                     WRITING：进程活跃，正在把redo写到归档日志中
                     IDLE：空闲，没有日志同步
                     RECEIVING：收听网络通信
                     OPENING：正在打开归档日志
--MRP0（Manager Recover Process）：负责日志应用个，只要启用到日志应用才会有这个服务。在物理standby中是MRP，在逻辑standby中是LSP。
状态值：
                        WAIT_FOR_LOG：等待日志完成
                        APPLYING_LOG：正在应用日志




从最大performance到最大availability步骤
1、修改主数据库归档参数
alter system set log_archive_dest_2='service=stby LGWR SYNC AFFIRM valid_for=(online_logfile,primary_role) db_unique_name=stby'
2、重启数据库到mount状态，然后执行
SQL>  alter database set standby database to maximize availability;
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
这个模式对于logical standby比较有用，如果在logical standby上面执行了Real-Time Apply
SQL>alter database start logical standby apply immediate
得到的数据是Real-Time，正常情况下数据和主数据库完全同步！




查看是否归档 select name,log_mode from v$database;










　1.改变非归档模式到归档模式:
 
　　1)SQL> conn / as sysdba (以DBA身份连接数据库)
 
　　2)SQL> shutdown immediate; (立即关闭数据库)
 
　　3)SQL> startup mount (启动实例并加载数据库，但不打开)
 
　　4)SQL> alter database archivelog; (更改数据库为归档模式)
 
　　5)SQL> alter database open; (打开数据库)
 
　　6)SQL> alter system archive log start; (启用自动归档)
 
　　7)SQL> exit (退出)




　2.改变归档模式到非归档模式:
 
　　1)SQL>SHUTDOWN NORMAL/IMMEDIATE;
 
　　2)SQL>STARTUP MOUNT;
 
　　3)SQL>ALTER DATABASE NOARCHIVELOG;
 

　　4)SQL>ALTER DATABASE OPEN;

如果还有什么疑问可以给我发QQ消息  394818428