一、备库只安裝software
- verify the directories between primary and standby：
- open force logging mode:
SQL> alter database force logging;
- Redo:
Perform the following query on a redo source database to determine the size of each log file and the number of log groups in the redo log:
SQL> select group#,bytes from v$log;
Perform the following query on a redo destination database to determine the size of each log file and the number of log groups in the standby redo log:
SQL> SELECT GROUP#,members,BYTES FROM V$STANDBY\_LOG;
SQL> select member from v$logfile;
SQL>alter database add standby logfile thread 1 group 4 '/u01/app/oradata/gscmdb/redo04.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 '/u01/app/oradata/gscmdb/redo05.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 '/u01/app/oradata/gscmdb/redo06.log' size 50m;
SQL>alter database add standby logfile thread 1 group 7 '/u01/app/oradata/gscmdb/redo07.log' size 50m;
- 刪除：
SQL> alter database drop standby lofile group 4;
For example, the following SQL statements might be used to create a standby redo log at a database that is to receive redo from a redo source database that has two redo threads:
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
- 參數文件：
主库
SQL> create pfile from spfile;
[oracle@nick-testdb admin\]$ cd $ORACLE\_HOME/dbs
创建归档:
[oracle@nick-testdb TESTDB\]$ mkdir -p /u01/app/oracle/TESTDB/archivelog
[oracle@nick-testdb dbs\]$ vim inittestdb.ora
testdb.__db_cache_size=687865856
testdb.__java_pool_size=16777216
testdb.__large_pool_size=33554432
testdb.__oracle_base='/u01/app/oracle'#ORACLE\_BASE set from environment
testdb.__pga_aggregate_target=671088640
testdb.__sga_target=989855744
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=234881024
testdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/TESTDB/controlfileo1_mf_gq3chycs_.ctl','/u01/app/oracle/fast_recover
y_area/TESTDB/controlfile/o1_mf_gq3chyhs_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle'
*.db_domain=''
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1660944384
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
以下為新增：
DB_UNIQUE_NAME=testdb_pri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/TESTDB/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb_pri'
LOG_ARCHIVE_DEST_2='SERVICE=testdb_std ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_std'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=testdb\_std
STANDBY_FILE_MANAGEMENT=AUTO
SQL>startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb.ora';
SQL>create spfile from pfile;
scp {inittestdb.ora,orapwtestdb} oracle@10.67.38.236:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
6.修改備庫參數：
oracle@ostjdbatest01 dbs\]$ cat inittestdb.ora
testdb.__db_cache_size=687865856
testdb.__java_pool_size=16777216
testdb.__large_pool_size=33554432
testdb.\_\_oracle\_base='/u01/app/oracle'#ORACLE\_BASE set from environment
testdb.\_\_pga\_aggregate\_target=671088640
testdb.\_\_sga\_target=989855744
testdb.\_\_shared\_io\_pool\_size=0
testdb.\_\_shared\_pool\_size=234881024
testdb.\_\_streams\_pool\_size=0
\*.audit\_file\_dest='/u01/app/oracle/admin/testdb/adump'
\*.audit\_trail='db'
\*.compatible='11.2.0.4.0'
\*.control\_files='/u01/app/oracle/TESTDB/controlfile/o1\_mf\_gq3chycs\_.ctl','/u01/app/oracle/fast\_recovery\_area/TESTDB/controlfile/o1\_mf\_gq3chyhs\_.ctl'
\*.db\_block\_size=8192
\*.db\_create\_file\_dest='/u01/app/oracle'
\*.db\_domain=''
\*.db\_name='testdb'
\*.db\_recovery\_file\_dest='/u01/app/oracle/fast\_recovery\_area'
\*.db\_recovery\_file\_dest\_size=4385144832
\*.diagnostic\_dest='/u01/app/oracle'
\*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
\*.log\_archive\_format='%t\_%s\_%r.dbf'
\*.memory\_target=1660944384
\*.open\_cursors=300
\*.processes=150
\*.remote\_login\_passwordfile='EXCLUSIVE'
\*.undo\_tablespace='UNDOTBS1'
DB\_UNIQUE\_NAME=testdb\_std
LOG\_ARCHIVE\_CONFIG='DG\_CONFIG=(testdb\_pri,testdb\_std)'
LOG\_ARCHIVE\_DEST\_1=
'LOCATION=/u01/app/oracle/TESTDB/archivelog
VALID\_FOR=(ALL\_LOGFILES,ALL\_ROLES)
DB\_UNIQUE\_NAME=testdb\_std'
LOG\_ARCHIVE\_DEST\_2='SERVICE=testdb\_pri LGWR ASYNC VALID\_FOR=(ONLINE\_LOGFILES,PRIMARY\_ROLE) DB\_UNIQUE\_NAME=testdb\_pri'
LOG\_ARCHIVE\_DEST\_STATE\_1=ENABLE
LOG\_ARCHIVE\_DEST\_STATE\_2=ENABLE
REMOTE\_LOGIN\_PASSWORDFILE=EXCLUSIVE
LOG\_ARCHIVE\_FORMAT=%t\_%s\_%r.arc
FAL\_SERVER=testdb\_pri
STANDBY\_FILE\_MANAGEMENT=AUTO
用pfile啟動
startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome\_1/dbs/inittestdb.ora';
create spfile from pfile;
- 监听：
主：
[oracle@nick-testdb archivelog\]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
\# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome\_1/network/admin/listener.ora
\# Generated by Oracle configuration tools.
SID\_LIST\_LISTENER =
(SID\_LIST =
(SID\_DESC =
(GLOBAL\_DBNAME = testdb)
(ORACLE\_HOME = /u01/app/oracle/product/11.2.0/dbhome\_1)
(SID\_NAME = testdb)
)
)
LISTENER =
(DESCRIPTION\_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nick-testdb.novalocal)(PORT = 1521))
)
)
ADR\_BASE\_LISTENER = /u01/app/oracle
\[oracle@nick-testdb archivelog\]$ more /u01/app/oracle/product/11.2.0/dbhome\_1/network/admin/tnsnames.ora
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.67.38.89)(PORT = 1521))
(CONNECT\_DATA =
(SERVER = DEDICATED)
(SERVICE\_NAME = testdb)
)
)
TESTDB\_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.67.38.236)(PORT = 1521))
(CONNECT\_DATA =
(SERVER = DEDICATED)
(SERVICE\_NAME = testdb\_std)
)
)
TESTDB\_PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.67.38.89)(PORT = 1521))
(CONNECT\_DATA =
(SERVER = DEDICATED)
(SERVICE\_NAME = testdb)
)
)
#lsnrctl start/stop
备库类似。
- 创建standby控制文件：
备库控制文件信息：
control\_files='/u01/app/oracle/TESTDB/controlfile/o1\_mf\_gq3chycs\_.ctl','/u01/app/oracle/fast\_recovery\_area/TESTDB/controlfile/o1\_mf\_gq3chyhs\_.ctl'
- rman恢复或者手工创建：
restore standby controlfile from '/mnt/NETAPPDB/NETAPPxx\_control\_20190826\_4kua58pu\_1\_1.bak';--主库备份后，在备库rman中执行
或者:
alter database create standby controlfile as '/tmp/control01.ctl';
\[oracle@nick-testdb ~\]$ scp /tmp/control01.ctl oracle@10.67.38.236:/u01/app/oracle/TESTDB/controlfile/o1\_mf\_gq3chycs\_.ctl
\[oracle@nick-testdb ~\]$ scp /tmp/control01.ctl oracle@10.67.38.236:/u01/app/oracle/fast_recovery_area/TESTDB/controlfile/o1_mf_gq3chyhs_.ctl
9.检查主备库对应的目录。
- rman 备份和恢复
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset database format '/data/rmanbak/twb2bsys/full\_backup\_%d\_%T\_%U.bak';
backup spfile format ='/data/rmanbak/twb2bsys/spfile\_backup\_%s\_%p\_%s.ora';
sql 'alter system switch logfile';
backup current controlfile for standby format '/data/rmanbak/twb2bsys/sty\_ctlfile\_backup\_%s\_%p\_%s.ctl';
backup archivelog from time='sysdate-0.5' until time 'sysdate' format = '/data/rmanbak/twb2bsys/archivelog\_%n\_arch\_%T\_%U.bak';
release channel c1;
release channel c2;
}
二、
把備份文件傳送到備庫相同目錄下
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
crosscheck backupset;
delete noprompt obsolete recovery window of 5 days;
backup as compressed backupset database format = '/u01/rmanbak/full\_backup\_%d\_%T\_%U.bak' ;
sql 'alter system switch logfile';
backup as compressed backupset archivelog from time='sysdate-0.5' format = '/u01/rmanbak/archivelog\_%n\_arch\_%T\_%U.bak';
backup spfile format ='/u01/rmanbak/spfile\_backup\_%s\_%p\_%s.ora';
backup current controlfile for standby format='/u01/rmanbak/sty\_ctlfile\_backup\_%s\_%p\_%s.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
备库:
restore standby controlfile from '/u01/app/oracle/TESTDB/backup/sty\_ctlfile\_backup\_9\_1\_9.ctl';
RMAN> alter database mount;
RMAN> restore database;
alter database open read only;
recover managed standby database using current logfile disconnect;
alter database recover managed standby database using current logfile disconnect from session;
recover database;
11.rman duplicate方式:
[oracle@prora ~\]$ rman target / auxiliary sys/oracle@cc63
duplicate target database for standby nofilenamecheck from active database ;
duplicate target database for standby from active database nofilenamecheck;
- 驗證：
验证Standby DB数据同步状态
SELECT name,db_unique_name,database_role,protection_mode,SWITCHOVER_STATUS FROM V$DATABASE;
the following query would be used to check the status of the standby database associated with LOG_ARCHIVE_DEST_2:
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
Data Guard provides the V$DATAGUARD\_STATS view that can be used to evaluate each standby database in terms of the currency of the data in the standby database, and the time it will take to perform a role transition if all available redo data is applied to the standby database. For example:
SQL> COLUMN NAME FORMAT A24
SQL> COLUMN VALUE FORMAT A16
SQL> COLUMN DATUM_TIME FORMAT A24
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 06/18/2009 12:22:06
apply lag +00 00:00:00 06/18/2009 12:22:06
apply finish time +00 00:00:00.000
estimated startup time 9
On the standby database, query the V$ARCHIVED\_LOG view to verify that received redo has been applied:
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED\_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
--------- ---
8 YES
9 YES
10 YES
11 IN-MEMORY
Query the V$ARCHIVE\_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.
SELECT THREAD#, LOW\_SEQUENCE#, HIGH\_SEQUENCE# FROM V$ARCHIVE\_GAP;
9.standbylog
RFS[4]: Assigned to RFS process 28446
RFS[4]: No standby redo logfiles created for thread 1
RFS[4]: Opened log for thread 1 sequence 4327 dbid 768655661 branch 989754605
12.停止备库日志应用
select * from v$logfile;
SQL> alter database recover managed standby database cancel;
alter system set standby\_file\_management=manual;
alter database drop standby logfile group 5;
alter database add standby logfile thread 1 group 9 '/data1/oradata/podb/styredo03.log' size 52428800 reuse
alter system set standby\_file\_management=auto;
alter database recover managed standby database using current logfile disconnect from session;
\---------------------------------------------------------------------------------------------------------------------------------
\--dataguard的几个参数:
log\_archive\_config='DG\_CONFIG=(orcl01,orcl02)'
log\_archive\_dest\_1='LOCATION=/u09/archivelog/orcl01 VALID\_FOR=(ALL\_LOGFILES,ALL\_ROLES) DB\_UNIQUE\_NAME=orcl01'
LOG\_ARCHIVE\_DEST\_2='SERVICE=orcl02 LGWR ASYNC VALID\_FOR=(ONLINE\_LOGFILES,primary\_ROLE) DB\_UNIQUE\_NAME=orcl02'
LOG\_ARCHIVE\_DEST\_STATE\_1=ENABLE
LOG\_ARCHIVE\_DEST\_STATE\_2=defer
FAL\_SERVER=orcl02
FAL\_CLIENT=orcl01
db\_file\_name\_convert='orcl02','orcl01' scope=spfile;
log\_file\_name\_convert='orcl02','orcl01' scope=spfile;
standby\_file\_management=auto
13.路径转换：
db_file_name_convert +DATA_DISK/, /datafile/
log_file_name_convert +REDO_DISK/, /data/redolog/
----------------------------------------
在auxiliary 库上执行以下命令，需要保证target和auxiliary 库的sys用户口令相同。
[oracle@dba2 dbs]$ orapwd file=orapw$ORACLE_SID password=oracle entries=10 force=y ignorecase=Y
rman target / cmdfile=/userhomecle/rmanrun/rman.sh log=/home/oracle/run/rman.sh.log
DataGaurd检查
主、备库查看当前日志状况
select sequence#,status from v$log;
备库查看RFS接收日志和MRP应用日志同步主库情况
select process, sequence#, status, delay_mins from v$managed_standby;
备库查看已经归档的redo
select REGISTRAR,CREATOR,THREAD#,APPLIED,sequence#,first_change#,next_change#, COMPLETION_TIME from v$archived_log;
主、备库查看是否有gap
主:select status,gap_status from v$archive_dest_status where dest_id=2;
备：select * from v$archive_gap;
select thread#, low_sequence#,high_sequence# from v$archive_gap;
查看數據庫,可切換角色
select database_role,switchover_status from v$database;
查詢保護模式
select protection_mode from v$database;
啟動備庫：
alter database mount standby database;
查看進程：
select process,pid,status,client_process from v$managed_standby;
啟動日志應用
alter database recover managed standby database using current disconnect from session;
驗證REDO apply進程,MRP0.
select process,pid,status,client_process from v$managed_standby;
查詢當前日誌序列號：
select sequence# from v$log;
alter system switch logfile;
select sequence# from v$log;
查詢備庫的日誌號：
查詢歸檔日誌：
select name,sequence#,thread# from v$archived_log;
切换Standby 与 PRIMARY 角色：
验证Standby DB数据同步状态
SELECT name,db_unique_name,database_role,protection_mode,SWITCHOVER_STATUS FROM V$DATABASE;
the following query would be used to check the status of the standby database associated with LOG_ARCHIVE_DEST_2:
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
Data Guard provides the V$DATAGUARD_STATS view that can be used to evaluate each standby database in terms of the currency of the data in the standby database, and the time it will take to perform a role transition if all available redo data is applied to the standby database. For example:
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A16
COLUMN DATUM_TIME FORMAT A24
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 06/18/2009 12:22:06
apply lag +00 00:00:00 06/18/2009 12:22:06
apply finish time +00 00:00:00.000
estimated startup time 9
On the standby database, query the V$ARCHIVED_LOG view to verify that received redo has been applied:
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
--------- —
8 YES
9 YES
10 YES
11 IN-MEMORY
Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
切换当前Primary DB 为Standby DB
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; WAIT;
重啟一庫到mount狀態：
shutdown immediate;
startup mount;
select database_role,switchover_status from v$database;
原備庫切換成主庫：
验证Standby DB数据同步状态
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
确认archive log 同步状态与原始Primary DB是否同步
Archive log list
退出自动recover模式
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE Cancel;
切换当前Standby DB为Primary DB
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; WAIT;
alter database open;
alter system switch logfile;
select sequence#,applied from v$archived_log where sequence#>x;
在新備庫上啟動日誌應用：
alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database cancel;
alter database open;
–推遲
alter database recover managed standby database delay 120 disconnect;
alter database recover managed standby database nodelay;
驗證REDO apply進程,MRP0.
select process,pid,status,client_process from v$managed_standby;
start real-time apply:
alter database recover managed standby database using current logfile disconnect from session;
stop Redo apply:
alter database recover managed standby database cancel;
check the Redo apply service status:
select thread#,sequence#,process,client_process,status,blocks from v$managed_standby;
check the lastest log sequence:
select sequence#,applied from v$archived_log order by sequence#;
last archived sequence(can use also on primary)
select max(sequence#) from v$archived_log;
last applied sequence
select max(sequence#) from v$archived_log where applied=‘YES’;
互换Primary / Standby Server的(IP地址/TNSNAME/监听/电脑名)
验证切换后的Primary/Standby DB数据同步状态
Fail over:
如果主庫能起到mount:
alter system flush redo to ‘cosp’; 可以最大的程度保護數據
ALTER SYSTEM FLUSH REDO TO target_db_name;
備庫上檢查歸檔日誌的GAP;
select thread#,low_sequence#,high_sequence# from v$archive_gap;
now rows selected
將缺少的日誌從主庫複製到備庫，并用以下指令註冊這些歸檔：
alter database register physical logfile ‘filespec1’;
主備庫查詢每個線程對應的最大歸檔日誌序列號：
select thread#,max(sequence#) from v$archived_log group by thread#;
備庫執行下面命令初始化Failover
alter database recover managed standby database finish force;
select database_role,switchover_status from v$database;
切換到主庫角色
alter database commit to switchover to primary;
------------------------------------------------------------------
FAIL over后，主庫重新加入備庫:必須啟動了Flashback database功能
1.在新主庫上獲得原先備庫切換為主庫時的scn：
select standby_became_primary_scn from v$database;
2.啟動原主庫到Mount狀態：
3.閃回原主庫到SCN:
flashback database to scn xxxxx;
4.alter database convert to physical standby;
5.shutdown immediate;
6.startup mount;
7.alter system archive log current;
8.select dest_id,dest_name,status,destination from v$archive_dest_status;
9.如果不是valid,用下面指令啟動：
alter system set log_archive_dest_state_n=enable;
10.alter database recover managed standby database disconnect from session;
11.如果是實時應用：
alter database recover managed standby database using current logfile disconnect;
select name,open_mode,protection_mode,database_role,switchover_status from v$database;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle物理dg gap解决:
一般 redo传输服务（redo transport services）会自动探测并重发日志文件到备库来解决gap，但也有自动解决不了的时候，此时就需要人工介入，以下是操作步骤。
1.备库查看gap
select * from v$archive_gap;
2.主库查看gap对应的日志文件，sql中的40和49是上一步查出的日志sequence号
select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 40 and 49;
3.将上一步查询出的日志文件由 主库 拷贝 到备库，并在备库 注册日志文件
alter database register logfile ‘/oracle/arc/1_40_907779584.dbf’;
4.查看注册过一个日志文件后，备的gap情况，low_sequence#由40变成了41
select * from v$archive_gap;
5.将gap中的所有日志文件都 拷贝 到备库，并注册后，备库再次查看gap情况，此时视图中已无记录，则说明gap已解决
select * from v$archive_gap;
---------------------------------------------------------------------------------------------
–備庫自動使用主庫傳過來的日誌進行恢復：
aleter database recover automatic standby database; --有時standby中斷一段時間后起來，開啟應用無法從歸檔日誌恢復，需要執行這個指令應用歸檔日誌，等應用到最近 的一個日誌后，再開啟應該日誌模式
–更改保護模式：
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performance;
–查看進程活動的狀況
select process,client_process,sequence#,status,from v$managed_standby;
---------------------------------------------------------------------------------------------------
SQL> SET LINESIZE 120
SQL> COL USERNAME FOR A30
SQL> select USERNAME,count(*) from V$session where username not in (‘SYS’,‘SYSRAC’) GROUP BY USERNAME;
no rows selected
1.4 检查主库是否有定时任务在执
1.4.1 检查数据库中是否有定时任务正在执
select sid,job,LAST_DATE,INSTANCE from dba_jobs_running;
1.4.2 检查数据库的定时任务调度
select OWNER,JOB_NAME,JOB_STYLE,SESSION_ID,RUNNING_INSTANCE,ELAPSED_TIME from dba_scheduler_running_jobs;
select OWNER,SCHEDULE_NAME,START_DATE,REPEAT_INTERVAL,END_DATE,COMMENTS from dba_scheduler_schedules;
select OWNER,JOB_NAME,JOB_CREATOR,SCHEDULE_OWNER,SCHEDULE_NAME,START_DATE,ENABLED,STATE from dba_scheduler_jobs;
select OWNER,WINDOW_NAME,SCHEDULE_OWNER,SCHEDULE_NAME,START_DATE,END_DATE,ENABLED,ACTIVE from dba_scheduler_windows;
1.5 确认数据库定时任务是否可以关闭
如果检查发现有数据库定时任务正在执，需要联系业务确认是否可以关闭定时任务.
1.6 修改数据库定时任务参数
修改与Oracle数据库定时任务相关的参数，关闭数据库定时任务进程，防数据库切换过程中数据
库启动定时任务。
– 记录数据库原来的参数值.
show parameter job_queue_processes
– 修改参数，禁数据库切换期间执数据库定时任务.
alter system set job_queue_processes=0 sid=’*’ scope=both;
1.7 dataguard数据库应延时检查
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A16
COLUMN DATUM_TIME FORMAT A24
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
1.8 验证标standby数据库已经准备就绪
在主库执以下语，检查db_unique_name为mdbedb_am的pyhsical standby数据库是否准备就绪：
alter database switchover to mdbedb_am verify;
set linesize 300
col DB_UNIQUE_NAME for a15
select open_mode,NAME,LOG_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME,PRIMARY_DB_UNIQUE_NAME from v$database;
2. 执主备数据切换
2.1 主库发起切换操作
在主库执以下命令发起数据库切换操作.
alter database switchover to mdbedb_am;
2.2 打开新的主库
在标standby服务上执以下命令，打开新的主库
2.3 新备库启动恢复进程
在原来的主库执以下命令将数据库启动到mount状态
SQL> select name,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
MDBEDB MOUNTED PRIMARY mdbedb_am
SQL>
– 如果是RAC，需要在所有的实例执以下命令.
alter database open;
startup mount;
或者
startup
– RAC
srvctl start database -db mdbedb
srvctl status database -db mdbedb
alter database recover managed standby database disconnect from session;
– 如果要启real redolog apply。
alter database recover managed standby database nodelay disconnect from session;
– 检查dg志传输和应延时情况.
set linesize 200
col name for a30
col value for a30
col unit for a40
select NAME,VALUE,UNIT,TIME_COMPUTED,DATUM_TIME from V$DATAGUARD_STATS;
– 检查mgr进程状态是否为APPLYING_LOG.
select PROCESS,STATUS from V$MANAGED_STANDBY;
– 使切换前的参数值恢复参数值，启动数据库定时任务进程.
alter system set job_queue_processes=4000 sid=’*’ scope=both;
select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,
GUARD_STATUS,FORCE_LOGGING,FLASHBACK_ON,DB_UNIQUE_NAME
from v$database;
archive log list;
– 获取数据库连接统计信息
select USERNAME,STATUS,MACHINE,SERVICE_NAME,count(*)
from v$session
where username not in (‘SYS’,‘SYSRAC’)
group by USERNAME,STATUS,MACHINE,SERVICE_NAME;
查看数据库会话的详细信息
3.2 检查primary数据库的告警志件
检查新primary数据库的告警志件，确认数据库没有报错。
3.2 检查physical standby数据库
切换数据库后，需要检查所有的dataguard数据库的数据同步是否正常。
3.2.1 检查standby数据库的恢复进程
在standby数据库执以下sql语，检查mrp进程是否正常。
3.2.2 检查standby数据库的数据同步延时情况
在standby数据库执以下语，检查standby数据库数据同步延时是否在合范围内。
– 获取数据库会话信息
select USERNAME,SQL_ID,SQL_EXEC_START,LOGON_TIME,STATE from v$session where username not in (‘SYS’,‘SYSRAC’);
set linesize 300
set linesize 250
col username for a20
col SQL_TEXT for a120
select USERNAME,s.SQL_ID,SQL_EXEC_START,LOGON_TIME,STATE, t.sql_text
from v$session s, v$sql t
where s.sql_id = t.sql_id
and s.username not in (‘SYS’,‘SYSRAC’);
– mrp0进程的状态应该是 `APPLYING_LOG`
select PROCESS,STATUS from V$MANAGED_STANDBY;
select NAME,VALUE,UNIT,TIME_COMPUTED,DATUM_TIME from v$dataguard_stats;
– 根据实际情况确定是否需要设置以下参数的值。
alter system set log_archive_dest_3=‘service=mdg2 valid_for=(online_logfiles,primary_role) db_unique_name=mdg2’ scope=both;
--------------------------------------------------------------------------------------------------------
当主库的某些日志没有成功传送到备库，那么这时候就发生了归档裂缝（Archive Gap）。目前Oracle提供了两种日志GAP的检测和处理机制，分别是自动GAP处理（Automatic Gap Resolution）和FAL进程GAP处理（FAL Gap Resolution）。自动GAP处理即主库上的ARCn进程会每分钟检查备库上的日志GAP情况并做相应处理。FAL（Fetch Archive Log）是通过配置FAL_SERVER和FAL_CLIENT实现GAP检测的一种机制，它是备库主动发起的“取”日志的过程。备库就是FAL_CLIENT，它从FAL_SERVER中取这些GAP。Oracle会首先尝试使用FAL进程处理GAP，当发现FAL机制并没有配置生效的时候，进而尝试使用自动GAP处理。
FAL进程只在物理备库存在。FAL进程提供了一个CLIENT/SERVER的机制，用来解决检测在主库产生的连续的归档日志，而在备库接受的归档日志不连续的问题。该进程只有在需要的时候才会启动，而当工作完成后就关闭了，因此在正常情况下，该进程是无法看见的。
（一）物理DG手动解决GAP
在一些特殊情况下，如果GAP不能自动解决，那么就需要手工执行中断恢复。通过查询视图V$ARCHIVE_GAP可以确定断档的是哪些日志。
1SQL> SELECT * FROM V$ARCHIVE_GAP;
2THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
3----------- ------------- --------------
41 10 12
可以看到，当前物理备库丢失日志文件从线程1的序号10到序号12。接下来确定归档日志文件的路径：（假设在主数据库上的本地归档目的地是LOG_ARCHIVE_DEST_1）：
1SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 10 AND 12;
2NAME
3-----------------------------------------------------------------
4/arch/thread1_dest/arch_1_10.arc
5/arch/thread1_dest/arch_1_11.arc
6/arch/thread1_dest/arch_1_12.arc
接下来，复制这些日志文件到物理备库，然后在物理备库上使用“ALTER DATABASE REGISTER LOGFILE”语句来注册这些归档日志，如下所示：
1SQL> ALTER DATABASE REGISTER LOGFILE ‘/arch/thread1_dest/arch_1_10.arc’;
也可以使用如下的SQL语句：
1ALTER DATABASE REGISTER OR REPLACE LOGFILE ‘/arch/thread1_dest/arch_1_10.arc’;
2ALTER DATABASE REGISTER OR REPLACE PHYSICAL LOGFILE ‘/arch/thread1_dest/arch_1_11.arc’;
在物理备库上注册这些日志文件之后，重新打开重做应用进程。如果断档的归档日志较多，那么可以使用如下的SQL语句来生成要执行的SQL语句（如下的SQL也适用于逻辑DG）：
1SELECT ‘ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE ‘’/arch/thread1_dest/arch_1_’ || a ||’.arc’’;’ LOGICAL_DG,
2 ‘ALTER DATABASE REGISTER OR REPLACE LOGFILE ‘’/arch/thread1_dest/arch_1_’ || a ||’.arc’’;’ PHYSICAL_DG
3 FROM (SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <= 12)
4 WHERE A >= 10 ;
需要注意的是，视图V$ARCHIVE_GAP只返回当前妨碍重做应用继续的下一个中断。在解决中断并重启重做应用进程后，再次在物理备库上查询V$ARCHIVE_GAP视图来确定下一个中断序号，如果有的话，重复这个过程直到没有更多的中断。
（二）逻辑DG手动解决GAP
在逻辑备库上查询DBA_LOGSTDBY_LOG视图可以确定是否有归档中断。例如，下面的查询指出断档号为16至18：
1SYS@orclasm > COLUMN FILE_NAME FORMAT a60
2SYS@orclasm > SELECT THREAD#, SEQUENCE#, FILE_NAME
3 2 FROM DBA_LOGSTDBY_LOG L
4 3 WHERE NEXT_CHANGE# NOT IN (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#)
5 4 ORDER BY THREAD#, SEQUENCE#;
6THREAD# SEQUENCE# FILE_NAME
7--------- ---------- ---------------------------------------------
81 16 /arch/oracle/arch_1_16.arc
91 18 /arch/oracle/arch_1_18.arc
接下来复制丢失的日志文件到逻辑备库，并在逻辑备库上使用“ALTER DATABASE REGISTER LOGICAL LOGFILE”来注册这些日志文件。例如：
1SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE ‘/arch/oracle/arch_1_16.arc’;
在逻辑备库上注册这些日志文件之后，重启SQL应用。和物理DG一样，在逻辑备库上的DBA_LOGSTDBY_LOG视图只返回当前妨碍SQL应用继续的下一个中断。在解决指定的中断并重启SQL应用之后，再次在逻辑备库上查询DBA_LOGSTDBY_LOG视图，以确定下一个中断序号，如果有的话，重复这个过程直到没有更多的中断。
需要注意的是，如果需要的归档日志已经不在主库上了，但是有归档日志的RMAN备份，那么可以通过RMAN恢复把缺少的归档日志进行还原，如下所示：
1SET ARCHIVELOG DESTINATION TO ‘/arch’;
2RESTORE ARCHIVELOG FROM LOGSEQ 7;
如果断档的归档日志已经丢失，且RMAN又没有备份，那么在Oracle 10g之前没有办法修复了，只能重建DG，但是从Oracle 10g开始可以采用主库基于SCN的增量备份来恢复DG，详见【3.2.12.8 主库丢失归档，物理DG如何恢复？】。
---------------------------------------------------------------------------------------------
10.62.170.192
select name,log_mode,db_unique_name,force_logging from v$database;
NAME, LOG_MODE, DB_UNIQUE_NAME, FORCE_LOGGING
GSCMDB ARCHIVELOG gscmdb NO
[oracle@SYGGSCMDB01 data]$ echo $ORACLE_HOME
/u01/app/11.2.0.4/product
[oracle@SYGGSCMDB01 data]$ echo $ORACLE_BASE
/u01/app
select * from v$logfile;
3 ONLINE /u01/app/oradata/gscmdb/redo03.log NO
2 ONLINE /u01/app/oradata/gscmdb/redo02.log NO
1 ONLINE /u01/app/oradata/gscmdb/redo01.log NO
select group#,bytes/1024/1024 from v$log;
1 50
2 50
3 50
10.62.170.197
mkdir -p /u01/app/11.2.0.4/product
mkdir -p /u01/oraInventory
chown -R oracle:dba /u01/
chmod -R 755 /u01
/u01/oraInventory/orainstRoot.sh
/u01/app/11.2.0.4/product/root.sh
startup nomount pfile=/u01/app/11.2.0.4/product/dbs/initgscmdb.ora;
control_files=’/u01/app/oradata/gscmdb/control01.ctl’,’/u01/app/fast_recovery_area/gscmdb/control02.ctl’
alter system set log_archive_config=‘DG_CONFIG=(gscmdb,standbydb,gscmdb_std)’;
alter system set log_archive_dest_3=‘SERVICE=gscmdb_std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,primary_ROLE) DB_UNIQUE_NAME=gscmdb_std’;
alter system set fal_server=standbydb,gscmdb_std;
control_files string /u01/app/oradata/gscmdb/contro
l01.ctl, /u01/app/fast_recover
y_area/gscmdb/control02.ctl
-----------------------------------------------------------------------------------------
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SQL> SELECT max(sequence#) from v$archived_log where applied=‘YES’;
– 查询备库当前scn
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
9164156262
run{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
allocate channel t6 type disk;
backup INCREMENTAL from scn 9164156262 database format ‘/u01/arch/incre_%U’;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
release channel t6;
}
创建备库控制文件
SQL> alter database create standby controlfile as ‘/u01/arch/standby.ctl’;
Database altered.
SQL> startup nomount
RMAN> restore standby controlfile from ‘/u01/backup/standby.ctl’;
备库恢复增量备份
RMAN> catalog start with ‘/u01/backup’;
searching for all files that match the pattern /u01/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/incre_1itso2at_1_1
File Name: /u01/backup/incre_1jtso2av_1_1
File Name: /u01/backup/incre_1htso1vf_1_1
File Name: /u01/backup/incre_1etso1vf_1_1
File Name: /u01/backup/incre_1ftso1vf_1_1
File Name: /u01/backup/incre_1ctso1vf_1_1
File Name: /u01/backup/incre_1dtso1vf_1_1
File Name: /u01/backup/incre_1gtso1vf_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup/incre_1itso2at_1_1
File Name: /u01/backup/incre_1jtso2av_1_1
File Name: /u01/backup/incre_1htso1vf_1_1
File Name: /u01/backup/incre_1etso1vf_1_1
File Name: /u01/backup/incre_1ftso1vf_1_1
File Name: /u01/backup/incre_1ctso1vf_1_1
File Name: /u01/backup/incre_1dtso1vf_1_1
File Name: /u01/backup/incre_1gtso1vf_1_1
RMAN> recover database noredo;
--------------------------------------------------------------------------------------------------------------------------
3、GAP较多情况，基于RMAN SCN增量备份恢复
利用基于SCN的备份去恢复DG的备库，从而绕开中间过多或者丢失的归档。如果要成完成一次基于scn的恢复？找到备库端数据文件中最低的scn，然后在主库去基于这个scn进行备份，这个时候rman回去扫描整个主库的块，如果块内的scn小于备库端数据文件中最低的scn，则证明这个块从备库应用到的时间点到现在是没有改变的，就忽略掉这个块。
修复过程：
（1） 查询备库当前SCN
SQL>col CURRENT_SCN for 999999999999999999
SQL>SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-------------------
5995252523400
（2） 确定主库是否添加数据文件
如果添加了数据文件，需要手工在备库添加。
SQL>select FILE#,name from v$datafile where CREATION_CHANGE#> =5995252523400;
norows selected
（3） 备库停止日志应用
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
（4） 主库增量备份并传输到备库上
RMAN>run {
allocatechannel c1 device type disk;
backup incremental fromscn 5995252523400 database format ‘/other/rman/ora_scn_%U.bak’;
releasechannel c1;
}
scp other/rman/ora_scn_*.bak10.1.32.99:/other/rman/
（5） 备库上进行恢复
RMAN>RECOVER DATABASE NOREDO;
（6） 主库上创建Standbycontrolfile文件并传输到备库
RMAN>BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/other/rman/std_ctl.bak’;
scp std_ctl.bak10.1.32.99:/other/rman/
（7） 备库恢复控制文件
RMAN>shutdown;
RMAN>STARTUP NOMOUNT;
RMAN>RESTORE STANDBY CONTROLFILE FROM ‘/other/rman/std_ctl.bak’;
RMAN>alter database mount;
（8） 清空备库日志组（这里不用）
本次DG中使用了standbylog模式，不需要此步骤。
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 1;
如果配置了physical standby redo log则不需该步骤；
如果没有采用standby log模式，有几组需要清空几组。
（9） 备库重设flashback
备库重设flashback（根据实际情况选做，备库本身就没开启，所以不用操作）
SQL>ALTER DATABASE FLASHBACK OFF;
SQL>ALTER DATABASE FLASHBACK ON;
（10）备库重新接收并应用日志
备库重新接收并应用日志：
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfileDISCONNECT FROM SESSION;
恢复过程备库最后的日志（最后需要出现Media Recovery Waiting for 字样）
（11）备库重新开启readonly模式
根据实际情况，备库重新开启readonly模式，本次需求是需要备库read only状态应用日志（11g ADG特性）
SQL>alter database RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>alter database open;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfileDISCONNECT FROM SESSION;
（12） 验证修复是否成功
对比最大sequence#.
在主库中执行altersystem switch logfile;
分别主备库中执行：select max(sequence#) from v$archived_log;
通过跟踪alert文件
主库告警：
tail -200f oracle/app/oracle/diag/rdbms/xddb/xddb/trace/alert_xddb.log
备库告警：
tail -200f /oracle/app/oracle/diag/rdbms/xddb_dg/xddb/trace/alert_xddb.log
注：若备库是rac，或者asm存储，则在还原控制文件后需要把控制文件中的数据文件重命名为备库的原数据文件名称才可以执行恢复操作。
–重命名备库的数据文件
altersystem set standby_file_management=manual sid=’*’;
alterdatabase rename file ‘+DATADG/testdg/datafile/system.274.976812987’ TO’+DATADG/testdgphy/datafile/system.261.976877439’;
…
alterdatabase rename file ‘+DATADG/testdg/datafile/undotbs2.286.976813901’ TO’+DATADG/testdgphy/datafile/undotbs2.257.976877619’;
…
altersystem set standby_file_management=auto sid=’*’;
在执行RECOVER DATABASE NOREDO前，应该让备库和主库都处于同一个incarnation,否则会报错
listincarnation of database;
resetdatabase to incarnation 1;–应该和主库保持一致
附录：
Oracle的DG中常常用到的比较有用的性能视图：
V$MANAGED_STANDBY：包含与物理备库相关的数据库进程（例如：LGWR、RFS、LNS、ARCH、MRP等）的信息。
V$ARCHIVED_LOG：在备库执行此查询时，显示该备库接收到的日志。
V$LOG_HISTORY：包含归档历史的详细信息。
V$DATAGUARD_STATUS：包含DG生成的消息，这些消息被写入该特定数据库（主库或备库）的告警日志或跟踪文件中。
V$RECOVERY_PROGRESS：包含与备库恢复相关的统计信息。
V$STANDBY_EVENT_HISTOGRAM：包含某个物理备库的应用滞后的直方图。
DBA_LOGSTDBY_LOG：包含关于已经被或正在被SQL Apply处理的归档日志的信息。
DBA_LOGSTDBY_EVENTS：包含最近的SQL Apply事件（例如异常终止）的记录，这些事件也存在于运行SQL Apply的数据库实例的告警日志中。
V$LOGSTDBY_PROCESS：包含每个SQL Apply进程的当前状态。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database create datafile ‘/u01/app/database/11.2.0.4/product/dbs/UNNAMED00077’ as ‘/standby資料目錄/indx_111.dbf’```language
