Oracle Database 12.2本机配置ADG

    随着Oracle Database 12.2的发布，越来越多的客户已经开始使用Oracle 12c，刚好下载了12.2，测试下12.2的物理备库。本次测试主库和备库在同一台主机上。 1、环境信息 [root@rh70db ~]# lsb_release -a LSB Version:    :core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch Distributor ID: RedHatEnterpriseServer Description:    Red Hat Enterprise Linux Server release 7.0 (Maipo) Release:        7.0 Codename:       Maipo BANNER                                                                               CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 PL/SQL Release 12.2.0.1.0 - Production                                                    0 CORE    12.2.0.1.0      Production                                                                0 TNS for Linux: Version 12.2.0.1.0 - Production                                            0 NLSRTL Version 12.2.0.1.0 - Production                                                    0 主机地址：192.168.15.110 2、数据库信息 主库配置： db_name: orcl db_unique_name:orcl Oracle_sid=orcl pdbs:orclpdb 归档目录：/home/oracle/arch 端口：1521 tnsnames:  pry ——> orcl  sty——> orcldg 备库配置： db_name: orcl db_unique_name:orcldg Oracle_sid=orcldg pdbs:orclpdb 归档目录：/home/oracle/styarch 端口：1521 tnsnames:  pry ——> orcl  sty——> orcldg 3、配置步骤  a、配置主库归档模式，force logging SQL> startup mount; SQL> alter database archivelog ; SQL> alter database open; SQL> ALTER DATABASE FORCE LOGGING; c、配置tnsnames [ora12@rhel66db admin]$ more tnsnames.ora  pry =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.110)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcl)       (SERVER = DEDICATED)     )   ) sty =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.110)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcldg)       (SERVER = DEDICATED)     )   ) c、修改数据库主数据库参数 ALTER SYSTEM SET log_archive_config='dg_config=(orcl,orcldg)';  --(local_unique_name，remote_unique_name) alter system set log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl';  alter system set log_archive_dest_2='SERVICE=sty async  valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';   alter system set fal_client='pry';  alter system set fal_server='sty'; alter system set LOG_ARCHIVE_MAX_PROCESSES=5;   alter system set standby_file_management=auto; 说明： log_archive_dest_2默认参数如下：NOAFFIRM  COMPRESSION(DISABLE) ENCRYPTION(disable) PRIORITY=1 DELAY=30min MAX_CONNECTIONS=1 NET_TIMEOUT=30s REOPEN=300s 详见：Oracle Database Reference 12c Release 2 (12.2) E49629-15 验证参数信息： set linesize 500 pages 100 col value for a90  col name for a50  select name, value  from v$parameter  where name in ('db_name','db_unique_name',  'log_archive_config',  'log_archive_dest_1','log_archive_dest_2',  'log_archive_dest_state_1',  'log_archive_dest_state_2',  'remote_login_passwordfile',  'log_archive_format',  'log_archive_max_processes',  'fal_server','fal_client','db_file_name_convert',  'log_file_name_convert',  'standby_file_management')  / d、创建备库参数文件，修改参数后内容如下： [oracle@rh70db dbs]$ more initorcldg.ora  orcldg.__data_transfer_cache_size=0 orcldg.__db_cache_size=230686720 orcldg.__inmemory_ext_roarea=0 orcldg.__inmemory_ext_rwarea=0 orcldg.__java_pool_size=4194304 orcldg.__large_pool_size=8388608 orcldg.__oracle_base='/app/12.2.0'#ORACLE_BASE set from environment orcldg.__pga_aggregate_target=369098752 orcldg.__sga_target=469762048 orcldg.__shared_io_pool_size=0 orcldg.__shared_pool_size=209715200 orcldg.__streams_pool_size=0 *.audit_file_dest='/app/12.2.0/admin/orcldg/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/app/12.2.0/oradata/orcldg/control01.ctl','/app/12.2.0/oradata/orcldg/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='/app/12.2.0/oradata/orcl/','/app/12.2.0/oradata/orcldg/' *.db_name='orcl' *.db_unique_name='orcldg' *.diagnostic_dest='/app/12.2.0' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.enable_pluggable_database=true *.fal_client='sty' *.fal_server='pry' *.local_listener='LISTENER_ORCL' *.log_archive_config='dg_config=(orcldg,orcl)' *.log_archive_dest_1='location=/home/oracle/styarch valid_for=(all_logfiles,all_roles) db_unique_name=orcldg' *.log_archive_dest_2='SERVICE=pry async valid_for=(online_logfiles,primary_role) db_unique_name=orcl' *.log_archive_max_processes=5 *.log_file_name_convert='/app/12.2.0/oradata/orcl/','/app/12.2.0/oradata/orcldg/' *.memory_max_target=800m *.memory_target=800m *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' e、配置密码文件 cd $ORACLE_HOME/dbs orapwd file=orapworcl password=Gyc_1234 entries=5 force=y cp orapworcl  orapworcldg f、创建备库相关文件 SQL> ho mkdir -p /app/12.2.0/admin/orcldg/adump SQL> ho mkdir -p /app/12.2.0/oradata/orcldg g、备份/恢复控制文件 backup current controlfile for standby format '/home/oracle/ctr';  export ORACLE_SID=orcldg restore standby controlfile from '/home/oracle/ctr';? alter database mount; 在备库上查看进程信息，出现RFS进程 SQL> select process,status,group#,thread#,sequence#,blocks from v$managed_standby; h、备份/恢复数据库 backup database   format '/home/oracle/orcl_%U'; catalog start with '/home/oracle/';  生产执行文件，其中name属性需要根据实际情况修改，本次测试将orcl修改为orcldg select 'set newname for datafile '||file#||' to '''||name||''';' from v$datafile;  run {  set newname for datafile 1 to '/app/12.2.0/oradata/orcldg/system01.dbf'; set newname for datafile 3 to '/app/12.2.0/oradata/orcldg/sysaux01.dbf'; set newname for datafile 4 to '/app/12.2.0/oradata/orcldg/undotbs01.dbf'; set newname for datafile 5 to '/app/12.2.0/oradata/orcldg/pdbseed/system01.dbf'; set newname for datafile 6 to '/app/12.2.0/oradata/orcldg/pdbseed/sysaux01.dbf'; set newname for datafile 7 to '/app/12.2.0/oradata/orcldg/users01.dbf'; set newname for datafile 8 to '/app/12.2.0/oradata/orcldg/pdbseed/undotbs01.dbf'; set newname for datafile 9 to '/app/12.2.0/oradata/orcldg/orclpdb/system01.dbf'; set newname for datafile 10 to '/app/12.2.0/oradata/orcldg/orclpdb/sysaux01.dbf'; set newname for datafile 11 to '/app/12.2.0/oradata/orcldg/orclpdb/undotbs01.dbf'; set newname for datafile 12 to '/app/12.2.0/oradata/orcldg/orclpdb/users01.dbf'; restore database;  switch datafile all;  }  i、添加standby日志 alter database add standby logfile  ('/app/12.2.0/oradata/orcldg/stdredo101.log') size 200m; alter database add standby logfile  ('/app/12.2.0/oradata/orcldg/stdredo102.log') size 200m; alter database add standby logfile  ('/app/12.2.0/oradata/orcldg/stdredo103.log') size 200m; alter database add standby logfile  ('/app/12.2.0/oradata/orcldg/stdredo104.log') size 200m; j、启动日志实时应用，检查进程 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; PROCESS   STATUS       GROUP#                                      THREAD#  SEQUENCE#     BLOCKS --------- ------------ ---------------------------------------- ---------- ---------- ---------- ARCH      CLOSING      5                                                 1         34        241 DGRD      ALLOCATED    N/A                                               0          0          0 DGRD      ALLOCATED    N/A                                               0          0          0 ARCH      CONNECTED    N/A                                               0          0          0 ARCH      CLOSING      4                                                 1         32        376 ARCH      CLOSING      4                                                 1         33       1170 ARCH      CONNECTED    N/A                                               0          0          0 RFS       IDLE         2                                                 1         35          1 RFS       IDLE         N/A                                               0          0          0 MRP0      APPLYING_LOG N/A                                               1         35     409600 RFS       IDLE         N/A                                               0          0          0 open数据库： alter database open; alter pluggable database all open; 调整temp表空间： alter tablespace temp add tempfile '/app/12.2.0/oradata/orcldg/temp01.dbf' size 1g; alter tablespace temp drop tempfile '/app/12.2.0/oradata/orcl/temp01.dbf';  alter session set container=orclpdb; alter tablespace temp add tempfile '/app/12.2.0/oradata/orcldg/orclpdb/temp01.dbf' size 1g;  alter tablespace temp drop tempfile '/app/12.2.0/oradata/orcl/orclpdb/temp01.dbf';  alter session set container=pdb$seed; alter tablespace temp add tempfile '/app/12.2.0/oradata/orcldg/pdbseed/temp012017-03-07_14-19-56-013-PM.dbf' size 1g;  alter tablespace temp drop tempfile '/app/12.2.0/oradata/orcl/pdbseed/temp012017-03-07_14-19-56-013-PM.dbf';  查看数据库状态： SQL>  select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FORCE_LOGGING                           OPEN_MODE            SWITCHOVER_STATUS ----------- -------------------- ---------------- --------------------------------------- -------------------- --------------------     1751774 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES                                     READ ONLY WITH APPLY NOT ALLOWED 4、测试验证 --测试 alter session set container=orclpdb; select table gyc as select * from dba_objects; create tablespace gyc datafile '/app/12.2.0/oradata/orcl/orclpdb/gyc.dbf' size 1m; alter database datafile '/app/12.2.0/oradata/orcl/orclpdb/gyc.dbf' resize 2m;  5、其他配置 主库调整删除归档限制： CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY; 12c数据库控制文件会自动备份，备份目录为$ORACLE_HOME/dbs下 6、物理dg基本操作 --开始应用归档 alter database recover managed standby database disconnect from session; --取消应用归档 alter database recover managed standby database cancel; --启动redo apply ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; --取消redo apply alter database recover managed standby database cancel; --查看primary端归档目录监控 col destination for a60 SELECT DESTINATION, DEST_NAME,STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; --查看日志状态  select group#,bytes/1024/1024 mb, members ,status from v$log; --查看switch状态 select switchover_status from v$database; --查看数据库状态  set lin 200 pages 100  select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; --查看进程状态 select process,status,thread#,sequence#,block#,blocks from v$managed_standby; --查看归档目录错误 col dest_name for a40 select dest_name,error,status from v$archive_dest where rownum<5; --查看归档路径状态 col message for a80 select SEVERITY,error_code,to_char(timestamp,'yyyymmdd hh24:mi:ss') timestamps,message from v$dataguard_status; 7、常见问题及处理方式 a、 sty                            LOG_ARCHIVE_DEST_2   ERROR     ORA-01033: ORACLE initialization or shutdown in progress 20170307 15:59:00 krsg_check_connection: Error 1033 connecting to standby 'sty' (Process:TT00) (PID:6576) 重新拷贝密码文件。 b、 ORA-16191: Primary log shipping client not logged on standby 修改主库密码后，standby密码修改，密码文件密码和用户密码不一致。重新拷贝密码文件 c、主库增加数据文件，备库失败 MRP0: Background Media Recovery process shutdown (orcldg) 2017-03-08T13:43:57.956254+08:00 Errors in file /app/12.2.0/diag/rdbms/orcldg/orcldg/trace/orcldg_m000_3928.trc: ORA-01110: data file 13: '/app/12.2.0/db/dbs/UNNAMED00013' ORA-01565: error in identifying file '/app/12.2.0/db/dbs/UNNAMED00013' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 Checker run found 1 new persistent data failures standby操作  alter system set standby_file_management=MANUAL; alter database create datafile '/app/12.2.0/db/dbs/UNNAMED00017' as '/app/12.2.0/oradata/orcldg/orclpdb/gyc.dbf'; alter system set standby_file_management=auto; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 后期会推出Database broker配置及一条命令切换DG。敬请期待！！