DataGuard 12cr1 on Linux7
環境
| 項目 | ノード1 | ノード2 |
|---|---|---|
| ホスト名 | tdb01 | tdb02 |
| IPアドレス | 192.168.10.71 | 192.168.10.72 |
| DB_NAME(1インスタンス目) | tremoro | tremoro |
| DB_UNIQUE_NAME(1インスタンス目) | tremorop | tremoros |
| ORACLE_SID(1インスタンス目) | tremoro | tremoro |
| DB_NAME(2インスタンス目) | kotaro | kotaro |
| DB_UNIQUE_NAME(2インスタンス目) | kotarop | kotaros |
| ORACLE_SID(2インスタンス目) | kotaro | kotaro |
※1インスタンス目のＤＧが完了していることが前提
手順
DB設定
通常のＤＢ構築手順を実施
その後、以下ＤＧの設定を実施。
'24. Archive Logモード(ノード1)
export ORACLE_SID=kotaro
sqlplus / as sysdba
select log_mode from v$database;
shutdown
startup mount
alter database archivelog;
alter database open;
SELECT STATUS FROM V$INSTANCE;
select log_mode from v$database;
'25. Force Loggingモード(ノード1)
select force_logging FROM v$database;
alter database force logging;
select force_logging FROM v$database;
'26. スタンバイREDOログファイルの作成(ノード1)
set linesize 10000
set underline off
set pagesize 500
SELECT group#,type,member FROM v$logfile;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$log;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$standby_log;
ALTER DATABASE ADD standby logfile
GROUP 4 ('/data/oracle/kotaro/redo/stbredo41.log','/data/oracle/kotaro/redo/stbredo42.log') SIZE 100M,
GROUP 5 ('/data/oracle/kotaro/redo/stbredo51.log','/data/oracle/kotaro/redo/stbredo52.log') SIZE 100M,
GROUP 6 ('/data/oracle/kotaro/redo/stbredo61.log','/data/oracle/kotaro/redo/stbredo62.log') SIZE 100M,
GROUP 7 ('/data/oracle/kotaro/redo/stbredo71.log','/data/oracle/kotaro/redo/stbredo72.log') SIZE 100M;
SELECT group#,type,member FROM v$logfile;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$standby_log;
'27. 初期化パラメータ設定設定(ノード1)
sqlplus / as sysdba
set linesize 1000
set pagesize 10000
col NAME_COL_PLUS_SHOW_PARAM FORM A50;
col TYPE form A20;
col VALUE_COL_PLUS_SHOW_PARAM form A200;
set trimspool off
show parameter DB_NAME
show parameter DB_UNIQUE_NAME
show parameter LOG_ARCHIVE_CONFIG
show parameter CONTROL_FILES
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_ARCHIVE_DEST_2
show parameter LOG_ARCHIVE_DEST_STATE_1
show parameter LOG_ARCHIVE_DEST_STATE_2
show parameter REMOTE_LOGIN_PASSWORDFILE
show parameter LOG_ARCHIVE_FORMAT
show parameter FAL_SERVER
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter STANDBY_FILE_MANAGEMENT
ALTER SYSTEM SET db_unique_name=kotarop SCOPE = SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(kotarop,kotaros)' SCOPE = SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=kotarop' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=kotaros ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kotaros' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=kotaros SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
shutdown
startup
set linesize 1000
set pagesize 10000
col NAME_COL_PLUS_SHOW_PARAM FORM A50;
col TYPE form A20;
col VALUE_COL_PLUS_SHOW_PARAM form A200;
set trimspool off
show parameter DB_NAME
show parameter DB_UNIQUE_NAME
show parameter LOG_ARCHIVE_CONFIG
show parameter CONTROL_FILES
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_ARCHIVE_DEST_2
show parameter LOG_ARCHIVE_DEST_STATE_1
show parameter LOG_ARCHIVE_DEST_STATE_2
show parameter REMOTE_LOGIN_PASSWORDFILE
show parameter LOG_ARCHIVE_FORMAT
show parameter FAL_SERVER
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter STANDBY_FILE_MANAGEMENT
'28. tnsnames.ora設定(ノード1)
vi $ORACLE_HOME/network/admin/tnsnames.ora
tnsnames.ora
kotaro =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb01)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotaro)
)
)
kotaros =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb02)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotaro)
)
)
cat $ORACLE_HOME/network/admin/tnsnames.ora
'29. listener.ora設定(ノード1)
vi $ORACLE_HOME/network/admin/listener.ora
listener.ora
LISTENER_kotaro=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=tdb01)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER_kotaro=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=kotaro)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro)))
cat $ORACLE_HOME/network/admin/listener.ora
lsnrctl status LISTENER_kotaro
lsnrctl start LISTENER_kotaro
lsnrctl status LISTENER_kotaro
ps -ef |grep LISTENER
sqlplus system/system_password@kotaro
quit
'30. スタンバイ用pfile作成（ノード1)
sqlplus / as sysdba
create pfile from spfile;
quit
cd $ORACLE_HOME/dbs
cp -p initkotaro.ora initkotaros.ora
vi initkotaros.ora
initkotaros.ora
*.db_unique_name='kotaroP'
↓
*.db_unique_name='kotaroS'
*.fal_server='kotaroS'
↓
*.fal_server='kotaroP'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=kotarop'
↓
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=kotaros'
*.log_archive_dest_2='SERVICE=kotaros ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kotaros'
↓
*.log_archive_dest_2='SERVICE=kotarop ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kotarop'
cat initkotaros.ora
diff initkotaro.ora initkotaros.ora
'31. スタンバイ用パスワードファイル作成（ノード1)
cd $ORACLE_HOME/dbs
orapwd file=orapwkotaro entries=10 password=oracle12$
ls -l orapw*
cp -p orapwkotaro orapwkotaros
ls -l orapw*
'32. コールドバックアップ対象確認（ノード1)
sqlplus / as sysdba
set pages 1000
select name from v$datafile;
select member from v$logfile;
'33.スタンバイ用制御ファイル作成（ノード1)
rm -f /tmp/ora_control1
sqlplus / as sysdba
shutdown immediate
startup mount
alter database create standby controlfile as '/tmp/ora_control1';
shutdown immediate
'34.ディレクトリ作成（ノード2)
(oracle)
mkdir -p /u01/app/oracle/admin/kotaro/adump
mkdir /data/oracle
mkdir /data/oracle/kotaro
mkdir /data/oracle/kotaro/redo
mkdir /data/oracle/kotaro/dbf
mkdir /data/oracle/kotaro/fast_recovery_area
'34. スタンバイ用ファイルをノード1から、ノード2にコピー（ノード1→ノード2)
■pfile
プライマリの"\$ORACLE_HOME/dbs/initkotaros.ora" を
スタンバイの"\$ORACLE_HOME/dbs/initkotaro.ora"に配置
(ノード1)
ls -l $ORACLE_HOME/dbs/initkotaros.ora
scp $ORACLE_HOME/dbs/initkotaros.ora oracle@tdb02:$ORACLE_HOME/dbs/initkotaro.ora
(ノード2)
ls -l $ORACLE_HOME/dbs/initkotaro.ora
■パスワードファイル
プライマリの"\$ORACLE_HOME/dbs/orapwkotaro"を
セカンダリの"\$ORACLE_HOME/dbs/orapwkotaro"に配置
(ノード1)
ls -l $ORACLE_HOME/dbs/orapwkotaro
scp $ORACLE_HOME/dbs/orapwkotaro oracle@tdb02:$ORACLE_HOME/dbs/orapwkotaro
(ノード2)
ls -l $ORACLE_HOME/dbs/orapwkotaro
■データファイル
プライマリの/data/oracle/kotaro/dbf/*を
セカンダリの/data/oracle/kotaro/dbf/に配置
(ノード1)
ls -l /data/oracle/kotaro/dbf/
scp /data/oracle/kotaro/dbf/* oracle@tdb02:/data/oracle/kotaro/dbf/
(ノード2)
ls -l /data/oracle/kotaro/dbf/
rm -f /data/oracle/kotaro/dbf/temp01.dbf
ls -l /data/oracle/kotaro/dbf/
■REDOログ
プライマリの/data/oracle/kotaro/redo/redo*を
セカンダリの/data/oracle/kotaro/redo/に配置
(ノード1)
ls -l /data/oracle/kotaro/redo/
scp /data/oracle/kotaro/redo/redo* oracle@tdb02:/data/oracle/kotaro/redo/
(ノード2)
ls -l /data/oracle/kotaro/redo/redo*
■制御ファイル
プライマリの/tmp/ora_control1を
スタンバイの
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/ora_control1
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/ora_control2
に配置
(ノード1)
ls -l /tmp/ora_control1
scp /tmp/ora_control1 oracle@tdb02:/data/oracle/kotaro/kotaro_control1.ctl
scp /tmp/ora_control1 oracle@tdb02:/data/oracle/kotaro/kotaro_control2.ctl
(ノード2)
ls -l /data/oracle/kotaro/*
'35. tnsnames.ora設定(ノード2)
vi $ORACLE_HOME/network/admin/tnsnames.ora
tnsnames.ora
kotaro =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb02)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotaro)
)
)
kotarop =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb01)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotaro)
)
)
cat $ORACLE_HOME/network/admin/tnsnames.ora
'36. listener.ora設定(ノード2)
vi $ORACLE_HOME/network/admin/listener.ora
listener.ora
LISTENER_KOTARO=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=tdb02)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1522))))
SID_LIST_LISTENER_KOTARO=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=kotaro)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro)))
cat $ORACLE_HOME/network/admin/listener.ora
lsnrctl status LISTENER_KOTARO
lsnrctl start LISTENER_KOTARO
lsnrctl status LISTENER_KOTARO
ps -ef |grep LISTENER
'37. スタンバイ・データベースでstartup(ノード2)
export ORACLE_SID=kotaro
sqlplus / as sysdba
startup mount
★'38. 管理リカバリモードに変更(ノード2)
--管理リカバリモードかどうかを確認[スタンバイ]
SELECT PROCESS,STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
--リアルタイム適用かどうかを確認[プライマリ]
COL DEST_NAME FOR A30
COL RECOVERY_MODE FOR A50
SET PAGES 1000
SET LINE 1000
SELECT DEST_NAME,RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE TYPE = 'PHYSICAL';
recover managed standby database disconnect;
--管理リカバリモードかどうかを確認[スタンバイ]
SELECT PROCESS,STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
'39. スタンバイ・データベースでスタンバイREDOログを作成(ノード2)
★recover managed standby database cancel;
set linesize 1000
set pagesize 10000
col MEMBER form A50;
set trimspool off
SELECT group#,type,member FROM v$logfile;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$standby_log;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
SELECT group#,type,member FROM v$logfile;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$standby_log;
ALTER DATABASE ADD standby logfile
GROUP 4 ('/data/oracle/kotaro/redo/stbredo41.log','/data/oracle/kotaro/redo/stbredo42.log') SIZE 100M,
GROUP 5 ('/data/oracle/kotaro/redo/stbredo51.log','/data/oracle/kotaro/redo/stbredo52.log') SIZE 100M,
GROUP 6 ('/data/oracle/kotaro/redo/stbredo61.log','/data/oracle/kotaro/redo/stbredo62.log') SIZE 100M,
GROUP 7 ('/data/oracle/kotaro/redo/stbredo71.log','/data/oracle/kotaro/redo/stbredo72.log') SIZE 100M;
SELECT group#,type,member FROM v$logfile;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$standby_log;
★recover managed standby database disconnect;
★'40. スタンバイ・データベースで一時表領域を作成(ノード2)
(いらないかも）
recover managed standby database cancel;
alter database open;
alter tablespace temp ADD TEMPFILE '/data/oracle/kotaro/dbf/temp01.dbf' size 20M reuse;
recover managed standby database disconnect;
'41. プライマリ・データベースをstartupします(ノード1)
sqlplus / as sysdba
startup
'42. DG状態確認(ノード1)
sqlplus / as sysdba
select DATABASE_ROLE from v$database;
DATABASE_ROLE
------------------------------------------------
PRIMARY
select protection_mode from v$database;
PROTECTION_MODE
------------------------------------------------------------
MAXIMUM PERFORMANCE
select switchover_status from v$database;
SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TI NEXT_TIM APPLIED
---------- -------- -------- ---------------------------
12 18-05-26 18-05-27 NO
13 18-05-27 18-05-27 NO
14 18-05-27 18-05-27 NO
15 18-05-27 18-05-27 NO
15 18-05-27 18-05-27 YES
16 18-05-27 18-05-27 YES
16 18-05-27 18-05-27 NO
17 18-05-27 18-05-27 YES
17 18-05-27 18-05-27 NO
18 18-05-27 18-05-27 YES
18 18-05-27 18-05-27 NO
SEQUENCE# FIRST_TI NEXT_TIM APPLIED
---------- -------- -------- ---------------------------
19 18-05-27 18-05-27 NO
19 18-05-27 18-05-27 YES
20 18-05-27 18-05-27 NO
20 18-05-27 18-05-27 YES
21 18-05-27 18-05-27 NO
21 18-05-27 18-05-27 NO
'43. DG状態確認(ノード2)
sqlplus / as sysdba
select DATABASE_ROLE from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY
select protection_mode from v$database;
PROTECTION_MODE
------------------------------------------------------------
MAXIMUM PERFORMANCE
select switchover_status from v$database;
SWITCHOVER_STATUS
------------------------------------------------------------
NOT ALLOWED
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
PROCESS STATUS
--------------------------- ------------------------------------
MRP0
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TI NEXT_TIM APPLIED
---------- -------- -------- ---------------------------
15 18-05-27 18-05-27 YES
16 18-05-27 18-05-27 YES
17 18-05-27 18-05-27 YES
18 18-05-27 18-05-27 YES
19 18-05-27 18-05-27 YES
20 18-05-27 18-05-27 YES
21 18-05-27 18-05-27 IN-MEMORY
'42. アーカイブログが正常に転送されるかの確認(ノード1、ノード2)
(ノード1)
alter system switch logfile;
quit
ls -l /data/oracle/kotaro/fast_recovery_area/kotaroP/archivelog/2018_05_27
-rw-r----- 1 oracle oinstall 91589632 5月 27 06:02 o1_mf_1_12_fjmlt77k_.arc
-rw-r----- 1 oracle oinstall 28358656 5月 27 07:51 o1_mf_1_13_fjms7wq3_.arc
-rw-r----- 1 oracle oinstall 172032 5月 27 07:57 o1_mf_1_14_fjmslbjr_.arc
-rw-r----- 1 oracle oinstall 3786752 5月 27 10:33 o1_mf_1_15_fjn2q2ft_.arc
-rw-r----- 1 oracle oinstall 846848 5月 27 10:35 o1_mf_1_16_fjn2t6xs_.arc
-rw-r----- 1 oracle oinstall 187392 5月 27 10:40 o1_mf_1_17_fjn34584_.arc
-rw-r----- 1 oracle oinstall 1536 5月 27 10:40 o1_mf_1_18_fjn347f1_.arc
-rw-r----- 1 oracle oinstall 14615552 5月 27 11:08 o1_mf_1_19_fjn4s6ov_.arc
-rw-r----- 1 oracle oinstall 1536 5月 27 11:08 o1_mf_1_20_fjn4s90j_.arc
-rw-r----- 1 oracle oinstall 5320704 5月 27 12:12 o1_mf_1_21_fjn8kstn_.arc
-rw-r----- 1 oracle oinstall 537088 5月 27 12:27 o1_mf_1_22_fjn9f4b2_.arc
(ノード2)
ls -l /data/oracle/kotaro/fast_recovery_area/kotaroS/archivelog/2018_05_27
-rw-r----- 1 oracle oinstall 3786752 5月 27 11:09 o1_mf_1_15_fjn4topy_.arc
-rw-r----- 1 oracle oinstall 846848 5月 27 11:09 o1_mf_1_16_fjn4tqjp_.arc
-rw-r----- 1 oracle oinstall 187392 5月 27 11:09 o1_mf_1_17_fjn4tqxh_.arc
-rw-r----- 1 oracle oinstall 1536 5月 27 11:09 o1_mf_1_18_fjn4tvrs_.arc
-rw-r----- 1 oracle oinstall 14615552 5月 27 11:09 o1_mf_1_19_fjn4twhr_.arc
-rw-r----- 1 oracle oinstall 1536 5月 27 11:09 o1_mf_1_20_fjn4tx96_.arc
-rw-r----- 1 oracle oinstall 5320704 5月 27 12:13 o1_mf_1_21_fjn8kygc_.arc
-rw-r----- 1 oracle oinstall 537088 5月 27 12:27 o1_mf_1_22_fjn9f92j_.arc
sqlplus / as sysdba
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TI NEXT_TIM APPLIED
---------- -------- -------- ---------------------------
15 18-05-27 18-05-27 YES
16 18-05-27 18-05-27 YES
17 18-05-27 18-05-27 YES
18 18-05-27 18-05-27 YES
19 18-05-27 18-05-27 YES
20 18-05-27 18-05-27 YES
21 18-05-27 18-05-27 YES
22 18-05-27 18-05-27 IN-MEMORY
service="kotaros", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="KOTAROS" net_timeout=30, valid_for=(online_logfile
[oracle@tdb01 dbs]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
tremoro =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tremoro)
)
)
tremoros =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb02)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tremoro)
)
)
kotaro =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb01)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotaro)
)
)
kotarop =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb01)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotarop)
)
)
kotaros =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb02)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotaro)
)
)
[oracle@tdb01 dbs]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER_TREMORO=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=tdb01)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1521))))
SID_LIST_LISTENER_TREMORO=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=tremoro)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=tremoro))
(SID_DESC=
(GLOBAL_DBNAME=TREMORO_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=TREMORO)
))
LISTENER_KOTARO=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=tdb01)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1522))))
SID_LIST_LISTENER_KOTARO=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=kotaro)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro)
)
(SID_DESC=
(GLOBAL_DBNAME=KOTAROP_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=KOTARO)
)
(SID_DESC=
(GLOBAL_DBNAME=KOTAROP_DGB)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=KOTARO)
)
(SID_DESC=
(GLOBAL_DBNAME=KOTAROS_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=KOTARO)
)
)
■kotaro
<DBログ転送確認操作>
alter system switch logfile;
! ls -l /data/oracle/kotaro/fast_recovery_area/KOTAROP/archivelog/2018_05_28
! ls -l /data/oracle/kotaro/fast_recovery_area/KOTAROS/archivelog/2018_05_28
<初期化パラメータ確認>
set linesize 1000
set pagesize 10000
col NAME_COL_PLUS_SHOW_PARAM FORM A50;
col TYPE form A20;
col VALUE_COL_PLUS_SHOW_PARAM form A200;
set trimspool off
show parameter dest_2
<rmanオフラインバックアップ取得>
export ORACLE_SID=kotaro
sqlplus / as sysdba
shutdown
startup mount
exit
rman
connect target sys
oracle12$
backup database;
BACKUP DATABASE PLUS ARCHIVELOG;
<rmanリストア>
export ORACLE_SID=kotaro
rman
connect target sys
oracle12$
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
rm -rf /u01/app/oracle/admin/kotaro/adump
rm -rf /data/oracle/kotaro
■DGMGRL
<接続>
dgmgrl
CONNECT kotarop
oracle12$
<状態確認>
show configuration;
show database 'KOTAROP';
show database 'KOTAROS';
select switchover_status from v$database;
<初期設定>
show parameter dg_broker_start
alter system set dg_broker_start=true scope=both;
show parameter dg_broker_start
select pname from v$process where pname like 'DMON%';
dgmgrl
CONNECT kotaro
oracle12$
CREATE CONFIGURATION 'dg_kotaro' AS PRIMARY DATABASE IS 'KOTAROP' CONNECT IDENTIFIER IS KOTAROP;
ADD DATABASE 'KOTAROS' AS CONNECT IDENTIFIER IS KOTAROS MAINTAINED AS PHYSICAL;
show configuration;
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER_KOTARO=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=kotaro)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro))
(SID_DESC=
(GLOBAL_DBNAME=KOTAROP_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro)
)
(SID_DESC=
(GLOBAL_DBNAME=KOTAROP_DGB)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro)
))
lsnrctl STOP LISTENER_KOTARO
lsnrctl START LISTENER_KOTARO
vi $ORACLE_HOME/network/admin/tnsnames.ora
kotarop =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb01)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotarop)
)
)
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER_kotaro=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=kotaro)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro))
(SID_DESC=
(GLOBAL_DBNAME=KOTAROS_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro)
)
(SID_DESC=
(GLOBAL_DBNAME=KOTAROS_DGB)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=kotaro)
))
lsnrctl STOP LISTENER_KOTARO
lsnrctl START LISTENER_KOTARO
vi $ORACLE_HOME/network/admin/tnsnames.ora
kotaros =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb02)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kotaros)
)
)
dgmgrl
CONNECT KOTAROP
oracle12$
show configuration;
enable configuration;
alter system set log_Archive_dest_2='service="kotarop"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="KOTAROP" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
<スイッチオーバー>
DGMGRL> connect SYS
oracle12$
switchover to 'KOTAROS'
switchover to 'KOTAROP'
show database 'KOTAROP'
show database 'KOTAROS'
alter system set dg_broker_start=true scope=both;
alter system set dg_broker_start=false scope=both;
■DGMGRL
<接続>
dgmgrl
CONNECT tremorop
change_on_install
CONNECT kotarop
change_on_install
<状態確認>
show configuration;
show database 'TREMOROP';
show database 'TREMOROS';
select switchover_status from v$database;
<初期設定>
CREATE CONFIGURATION 'dg_tremoro' AS PRIMARY DATABASE IS 'TREMOROP' CONNECT IDENTIFIER IS TREMOROP;
ADD DATABASE 'TREMOROS' AS CONNECT IDENTIFIER IS TREMOROS MAINTAINED AS PHYSICAL;
enable CONFIGURATION;
database 'TREMOROS'
edit database 'TREMOROS' set state='ONLINE';
<スイッチオーバー>
DGMGRL> connect SYS
switchover to 'TREMOROS'
<broker構成削除>
DISABLE CONFIGURATION
REMOVE CONFIGURATION
alter system set log_Archive_dest_2='' scope=both;
FAL_CLIENT
<
ALTER USER SYS IDENTIFIED BY change_on_install;
ALTER USER SYSTEM IDENTIFIED BY manager;
recover managed standby database cancel;
shutdown
alter database recover managed standby database cancel;
startup mount
recover managed standby database disconnect;
alter user sys identified by oracle12$;
alter system set dg_broker_start=true scope=both;
show parameter spfile
CREATE SPFILE='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfiletremoro.ora' FROM PFILE='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/inittremoro.ora';
CREATE SPFILE='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilekotaro.ora' FROM PFILE='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initkotaro.ora';
rm -f /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfiletremoro.ora
rm -f /data/oracle/tremoro/dbf/*
rm -f /data/oracle/tremoro/redo/*
rm -f /data/oracle/tremoro/dr*
rm -rf /data/oracle/tremoro/fast_recovery_area/*
SQL> set linesize 1000
SQL> set pagesize 10000
SQL> col NAME_COL_PLUS_SHOW_PARAM FORM A50;
SQL> col TYPE form A20;
SQL> col VALUE_COL_PLUS_SHOW_PARAM form A200;
SQL> set trimspool off
SQL>
SQL> show parameter
NAME TYPE VALUE
-------------------------------------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBFIPS_140 boolean FALSE
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
active_instance_count integer
aq_tm_processes integer 1
archive_lag_target integer 0
asm_diskgroups string
asm_diskstring string
asm_power_limit integer 1
asm_preferred_read_failure_groups string
audit_file_dest string /u01/app/oracle/admin/kotaro/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
awr_snapshot_time_offset integer 0
background_core_dump string partial
background_dump_dest string /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/log
backup_tape_io_slaves boolean FALSE
bitmap_merge_area_size integer 1048576
blank_trimming boolean FALSE
buffer_pool_keep string
buffer_pool_recycle string
cell_offload_compaction string ADAPTIVE
cell_offload_decryption boolean TRUE
cell_offload_parameters string
cell_offload_plan_display string AUTO
cell_offload_processing boolean TRUE
cell_offloadgroup_name string
circuits integer
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
clonedb boolean FALSE
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
commit_logging string
commit_point_strength integer 1
commit_wait string
commit_write string
common_user_prefix string C##
compatible string 12.1.0
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
control_file_record_keep_time integer 7
control_files string /data/oracle/kotaro/kotaro_control1.ctl, /data/oracle/kotaro/kotaro_control2.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
core_dump_dest string /u01/app/oracle/diag/rdbms/kotarop/kotaro/cdump
cpu_count integer 1
create_bitmap_area_size integer 8388608
create_stored_outlines string
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_big_table_cache_percent_target string 0
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
db_cache_advice string ON
db_cache_size big integer 0
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
db_domain string
db_file_multiblock_read_count integer 77
db_file_name_convert string
db_files integer 200
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 1440
db_index_compression_inheritance string NONE
db_keep_cache_size big integer 0
db_lost_write_protect string NONE
db_name string kotaro
db_performance_profile string
db_recovery_file_dest string /data/oracle/kotaro/fast_recovery_area
db_recovery_file_dest_size big integer 2G
db_recycle_cache_size big integer 0
db_securefile string PREFERRED
db_ultra_safe string OFF
db_unique_name string KOTAROP
db_unrecoverable_scn_tracking boolean TRUE
db_writer_processes integer 1
dbwr_io_slaves integer 0
ddl_lock_timeout integer 0
deferred_segment_creation boolean TRUE
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1KOTAROP.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2KOTAROP.dat
dg_broker_start boolean TRUE
diagnostic_dest string /u01/app/oracle
disk_asynch_io boolean TRUE
dispatchers string (PROTOCOL=TCP) (SERVICE=ORCLXDB)
distributed_lock_timeout integer 60
dml_locks integer 1084
dnfs_batch_size integer 4096
dst_upgrade_insert_conv boolean TRUE
enable_ddl_logging boolean FALSE
enable_goldengate_replication boolean FALSE
enable_pluggable_database boolean FALSE
event string
exclude_seed_cdb_view boolean TRUE
fal_client string
fal_server string KOTAROS
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
file_mapping boolean FALSE
fileio_network_adapters string
filesystemio_options string none
fixed_date string
gcs_server_processes integer 0
global_context_pool_size string
global_names boolean FALSE
global_txn_processes integer 1
hash_area_size integer 131072
heat_map string OFF
hi_shared_memory_address integer 0
hs_autoregister boolean TRUE
ifile file
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_percent integer 1
instance_groups string
instance_name string kotaro
instance_number integer 0
instance_type string RDBMS
instant_restore boolean FALSE
java_jit_enabled boolean TRUE
java_max_sessionspace_size integer 0
java_pool_size big integer 0
java_restrict string none
java_soft_sessionspace_limit integer 0
job_queue_processes integer 1000
large_pool_size big integer 0
ldap_directory_access string NONE
ldap_directory_sysauth string no
license_max_sessions integer 0
license_max_users integer 0
license_sessions_warning integer 0
listener_networks string
local_listener string
lock_name_space string
lock_sga boolean FALSE
log_archive_config string dg_config=(KOTAROP,KOTAROS)
log_archive_dest string
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=kotarop
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service="kotaros", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="KOTAROS" net_timeout=30, valid_for=(online_logfile,all_roles)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.arc
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer big integer 5072K
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
max_dispatchers integer
max_dump_file_size string unlimited
max_enabled_roles integer 150
max_shared_servers integer
max_string_size string STANDARD
memory_max_target big integer 512M
memory_target big integer 512M
nls_calendar string GREGORIAN
nls_comp string BINARY
nls_currency string \
nls_date_format string RR-MM-DD
nls_date_language string JAPANESE
nls_dual_currency string \
nls_iso_currency string JAPAN
nls_language string JAPANESE
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string .,
nls_sort string BINARY
nls_territory string JAPAN
nls_time_format string HH24:MI:SSXFF
nls_time_tz_format string HH24:MI:SSXFF TZR
nls_timestamp_format string RR-MM-DD HH24:MI:SSXFF
nls_timestamp_tz_format string RR-MM-DD HH24:MI:SSXFF TZR
noncdb_compatible boolean FALSE
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size big integer 0
open_cursors integer 300
open_links integer 4
open_links_per_instance integer 4
optimizer_adaptive_features boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 12.1.0.2
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_inmemory_aware boolean TRUE
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
os_authent_prefix string ops$
os_roles boolean FALSE
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 40
parallel_min_percent integer 0
parallel_min_servers integer 4
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 16
parallel_threads_per_cpu integer 2
pdb_file_name_convert string
pdb_lockdown string
pdb_os_credential string
permit_92_wrap_format boolean TRUE
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 0
plscope_settings string IDENTIFIERS:NONE
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_debug boolean FALSE
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
pre_page_sga boolean TRUE
processes integer 150
processor_group_name string
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
rdbms_server_dn string
read_only_open_delayed boolean FALSE
recovery_parallelism integer 0
recyclebin string on
redo_transport_user string
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
replication_dependency_tracking boolean TRUE
resource_limit boolean TRUE
resource_manager_cpu_allocation integer 1
resource_manager_plan string
result_cache_max_result integer 5
result_cache_max_size big integer 1312K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
resumable_timeout integer 0
rollback_segments string
sec_case_sensitive_logon boolean TRUE
sec_max_failed_login_attempts integer 3
sec_protocol_error_further_action string (DROP,3)
sec_protocol_error_trace_action string TRACE
sec_return_server_release_banner boolean FALSE
serial_reuse string disable
service_names string KOTAROP
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 247
sga_max_size big integer 512M
sga_target big integer 0
shadow_core_dump string partial
shared_memory_address integer 0
shared_pool_reserved_size big integer 3984588
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
skip_unusable_indexes boolean TRUE
smtp_out_server string
sort_area_retained_size integer 0
sort_area_size integer 65536
spatial_vector_acceleration boolean FALSE
spfile string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilekotaro.ora
sql92_security boolean FALSE
sql_trace boolean FALSE
sqltune_category string DEFAULT
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
star_transformation_enabled string FALSE
statistics_level string TYPICAL
streams_pool_size big integer 0
tape_asynch_io boolean TRUE
temp_undo_enabled boolean FALSE
thread integer 0
threaded_execution boolean FALSE
timed_os_statistics integer 0
timed_statistics boolean TRUE
trace_enabled boolean TRUE
tracefile_identifier string
transactions integer 271
transactions_per_rollback_segment integer 5
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
unified_audit_sga_queue_size integer 1048576
use_dedicated_broker boolean FALSE
use_indirect_data_buffers boolean FALSE
use_large_pages string TRUE
user_dump_dest string /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/log
utl_file_dir string
workarea_size_policy string AUTO
xml_db_events string enable