在线创建dg环境 adg

在线创建dg环境 adg


在两个库的环境变量中添加如下
export TNS_ADMIN=$ORACE_HOME/network/admin


主库：
lsnrctl stop
Shutdown immediate
Startup mount

alter database force logging;
alter database flashback on; 
alter database open;

alter database add standby logfile '/u01/app/oracle/oradata/testnewall/redo_standby01.log'  size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/testnewall/redo_standby02.log'  size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/testnewall/redo_standby03.log'  size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/testnewall/redo_standby04.log'  size 50m;
select member from v$logfile;

lsnrctl start

dg环境目录

[oracle@testdg oracle]$ mkdir oradata/testdg -p
[oracle@testdg oracle]$ mkdir admin/testdg -p

sid:testdg

dg环境卸载 psu
./opatch rollback -id 20760982

[oracle@testdg OPatch]$ ./opatch lspatches
此 Oracle 主目录中未安装任何中间补丁程序 "/u01/app/oracle/product/11.2.4/db_1".

主库
db_name                              string      testnewa
db_unique_name                       string      testnewall

create pfile='/home/oracle/pfileback.ora' from spfile;备份spfile文件

alter system set log_archive_config='dg_config=(testnewall,testdg)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=testnewall';
alter system set log_archive_dest_2='service=testdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=testdg';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_max_processes=10;
alter system set fal_server=testdg;
alter system set fal_client=testnewall;
alter system set standby_file_management=auto;
alter system set db_unique_name='testnewall'scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/testdg/','/u01/app/oracle/oradata/testnewall/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/testdg/','/u01/app/oracle/oradata/testnewall/' scope=spfile;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

----重启一次数据库生效

备库pfile
cat pfiledg.ora 
*.audit_file_dest='/u01/app/oracle/admin/testdg/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/testdg/control01.ctl','/u01/app/oracle/fast_recovery_area/testdg/control02.ctl'
*.db_block_size=8192
*.db_name='testnewa'
*.db_create_file_dest='/u01/app/oracle/oradata/testdg'
*.diagnostic_dest='/u01/app/oracle'
*.db_unique_name='testdg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8192m
*.db_file_name_convert='/u01/app/oracle/oradata/testnewall/','/u01/app/oracle/oradata/testdg/'
*.log_file_name_convert='/u01/app/oracle/oradata/testnewall/','/u01/app/oracle/oradata/testdg/'
*.log_archive_config='dg_config=(testnewall,testdg)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=testdg'; ---多了一个分号
*.log_archive_dest_2='service=testnewall lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=testnewall'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=10
*.fal_server=testnewall
*.fal_client=testdg
*.standby_file_management=auto
##*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))'

备库上
环境变量
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_TERM=xterm; export ORACLE_TERM
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.4/db_1; export ORACLE_HOME
ORACLE_SID=testdg; export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG
if [ $USER = "oracle" ]; then
        if [ $SHELL = "/bin/ksh" ]; then
         ulimit -p 16384
              ulimit -n 65536
        else
              ulimit -u 16384 -n 65536
        fi
        umask 022
fi


配置监听
cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = testnewa)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.4/db_1)
      (SID_NAME = testdg)
    )
  )


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


ADR_BASE_LISTENER = /u01/app/oracle

监听的设置
如果是GI环境，则使用grid home下的netmgr创建一个database service,global name是主库的db_name, service name是实例名称。这是配置静态监听

tnsname.ora的话 直接在主库上的oracle home下手动编辑tnsname.ora，然后复制到备库的响应目录。
cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


TESTNEWALL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = newtesta)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testnewall)
    )
  )


TESTDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.17.219)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testnewall)
      (UR = A) 
    )
  )


测试的时候要注意
注意：
        [oracle@newtesta admin]$ sqlplus system/TestOracle201604@testdg
        
        SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 14:31:02 2016
        
        Copyright (c) 1982, 2013, Oracle.  All rights reserved.
        
        ERROR:
        ORA-01033: ORACLE initialization or shutdown in progress
        Process ID: 0
        Session ID: 0 Serial number: 0
        
        [oracle@newtesta admin]$ sqlplus sys/TestOracle201604@testdg as sysdba
        
        SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 14:31:12 2016
        
        Copyright (c) 1982, 2013, Oracle.  All rights reserved.
        
        
        Connected to:
        Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options
        
        SQL> exit
        
不要使用system账户测试 尽量使用sys账户测试。可能因为密码文件中没有System用户的原因，测试会失败。

如果遇到错误  ORA-12528: TNS:listener: all appropriate instances are blocking new connection
在tnsname.ora中加上 两遍的监听要同步
TESTDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.17.219)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testnewall)  ---testnewall是主库上的实例名称
      (UR = A) 
    )
  )

备库使用pfile启动到nomount状态
sqlplus / as sysdba
startup nomount pfile='/home/oracle/pfiledg.ora';


主库进行在线克隆 显示主库 然后是备库
rman target / AUXILIARY sys/TestOracle201604@testdg
或者
rman target sys/TestOracle201604@testnewall AUXILIARY sys/TestOracle201604@testdg


RMAN> run {
duplicate target database for standby from active database nofilenamecheck;  
}
如果不加参数nofilenamecheck 会报错：
RMAN-05517: tempfile +DATA/rpdm/tempfile/temp.264.896017989 conflicts with file used by target database

下面是启动和停止dg的命令
alter database recover managed standby database using current logfile disconnect; 
alter database recover managed standby database disconnect from session; 
alter database recover managed standby database cancel;  

在备库执行：   
  shutdown immediate
  create spfile from pfile='/home/oracle/pfiledg.ora';
   SQL>startup mount;
   SQL>alter database open read only;
   SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
   
如果已经启用了Active Data Guard，备库的V$DATABASE会显示为"READ ONLY WITH APPLY':
   SQL> SELECT open_mode FROM V$DATABASE;
    OPEN_MODE
    --------------------
    READ ONLY WITH APPLY

部署日常维护脚本
主备库 上时间同步
00 * * * * /usr/sbin/ntpdate 10.168.100.109 >/dev/null 2>&1

主备库上的维护脚本
[oracle@newtesta ~]$ crontab -l
30 1 * * * sh /backup/script/back_full.sh     >/dev/null 2>&1
59 23 * * * sh /backup/script/del_trace_log.sh  >/dev/null 2>&1
59 23 * * * sh /backup/script/alert_log_archive.sh >/dev/null 2>&1

rman
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTNEWALL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 4;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.4/db_1/dbs/snapcf_testnewall.f'; # default


启动dgmgrl
主库上：

alter system set dg_broker_start=true;
SQL> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.4
                                                 /db_1/dbs/dr1testnewall.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.4
                                                 /db_1/dbs/dr2testnewall.dat
dg_broker_start                      boolean     TRUE

备库：
SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.4
                                                 /db_1/dbs/dr1testdg.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.4
                                                 /db_1/dbs/dr2testdg.dat
dg_broker_start                      boolean     TRUE

主库
dgmgrl
DGMGRL> connect sys/TestOracle201604
DGMGRL> create configuration 'DGTEST' as  primary database is 'testnewall' connect identifier is testnewall;
  Configuration "DGTEST" created with primary database "testnewall"
DGMGRL> add database 'testdg' as connect identifier is testdg maintained as physical;
  Database "testdg" added
DGMGRL> show configuration
Configuration - DGTEST
  Protection Mode: MaxPerformance
  Databases:
    testnewall - Primary database
    testdg     - Physical standby database

Fast-Start Failover: DISABLED


Configuration Status:
DISABLED


DGMGRL> enable configuration
Enabled.

DGMGRL>  show configuration

Configuration - DGTEST

  Protection Mode: MaxPerformance
  Databases:
    testnewall - Primary database
    testdg     - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

show database 'testnewall'
show database 'testdg'
DGMGRL> show database 'testnewall'
Database - testnewall

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    testnewall

Database Status:
SUCCESS

DGMGRL> show database 'testdg'
Database - testdg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    testdg


Database Status:
SUCCESS
DGMGRL> edit configuration set protection mode as maxperformance;
Succeeded.

SQL> select name,db_unique_name,protection_mode from v$database;

NAME      DB_UNIQUE_NAME                 PROTECTION_MODE
--------- ------------------------------ --------------------
TESTNEWA  testnewall                     MAXIMUM PERFORMANCE