隨著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。敬請期待！！

Oracle Database 12.2本機配置ADG

相關文章