oracle++dtcol,Oracle 灾备 -- DG 常用SQL

1.ORA-19527 设置 alter system set log_file_name_convert='/u01/oracle/oradata/systex/','/u01/oracle/oradata/systex/' scope=spfile;

set head off feedback off lines 133 pages 0 verify off echo off

spool register_logs_for_standby.sql

select 'alter database register logfile ' || chr(39) || name || chr(39) || ';'

from v$archived_log

where first_change# >= &1

/

SELECT DT,

SUM(RB8/36000000001.3) TOTAL_Mbps_REQ_FOR_A_DAY,

MIN(RB8/36000000001.3) MIN_Mbps_REQ_FOR_AN_HOUR,

MAX(RB8/36000000001.3) MAX_Mbps_REQ_FOR_AN_HOUR ,

AVG(RB8/36000000001.3) AVG_Mbps_REQ_FOR_AN_HOUR

FROM

(

SELECT TRUNC (COMPLETION_TIME) DT,

TO_CHAR (COMPLETION_TIME,'HH24') HH,

SUM(BLOCKS*BLOCK_SIZE) RB

FROM

V$ARCHIVED_LOG

WHERE COMPLETION_TIME > SYSDATE-5

AND DEST_ID=1

GROUP BY TRUNC(COMPLETION_TIME),

TO_CHAR (COMPLETION_TIME, 'HH24')

)

GROUP BY DT;

查看进程的活动状况—v$managed_standby

select process,client_process,sequence#,status from v$managed_standby;

PROCESS列显示进程信息

CLIENT_PROCESS列显示对应的主数据库中的进程

SEQUENCE#列显示归档redo的序列号

STATUS列显示的进程状态

检查归档文件路径及创建信息—v$archived_log

select name,creator,sequence#,applied,completion_time from v$archived_log;

2.确定并处理归档重做日志中的缺失(gaps)

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

注册 ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

3.查看 归档

SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

select name from v$archived_log;

4.转换standby 到primary

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

recover managed standby database finish;

ALTER DATABASE recover managed standby database using current logfile disconnect;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

5.primary 到standby

alter database commit to switchover to physical standby with session shutdown;

alter database commit to switchover to physical standby;

然后重启

alter database recover managed standby database disconnect from session;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE

select app_thread, seq_app, tm_applied,

nvl(seq_rcvd,seq_app) seq_rcvd, nvl(tm_rcvd,tm_applied) tm_rcvd

from

(select sequence# seq_app, FIRST_TIME tm_applied, thread# app_thread

from v$archived_log where applied = 'YES'

and (first_time, thread#) in (

select max(FIRST_TIME ), thread#

from v$archived_log where applied = 'YES'

group by thread# )

),

(select sequence# seq_rcvd, FIRST_TIME tm_rcvd, thread# rcvd_thread

from v$archived_log where applied = 'NO'

and (first_time, thread#) in (

select max(FIRST_TIME ), thread#

from v$archived_log where applied = 'NO'

group by thread# )

)

where rcvd_thread(+)= app_thread

/

6.查看状态

select switchover_status from v$database;

recover managed standby database cancel;

ALTER DATABASE RECOVER managed standby database cancel

recover automatic standby database ;

RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

SQL> ALTER SYSTEM SWITCH LOGFILE;

v$database Switchover_Status值的含义

NOT ALLOWED

当前的数据库不是带有备用数据库的主数据库

PREPARING DICTIONARY

该逻辑备用数据库正在向一个主数据库和其他备用数据库发送它的重做数据，以便为切换做准备

PREPARING SWITCHOVER

接受用于切换的重做数据时，逻辑备用配置会使用它

RECOVERY NEEDED

备用数据库还没有接收到切换请求

SESSIONS ACTIVE

在主数据库中存在活动的SQL会话；在继续执行之前必须断开这些会话

SWITCHOVER PENDING

适用于那些已收到主数据库切换请求但是还没有处理该请求的备用数据库

SWITCHOVER LATENT

切换没有完成并返回到主数据库

TO LOGICAL STANDBY

主数据库已经收到了来自逻辑备用数据库的完整的字典

TO PRIMARY

该备用数据库可以转换为主数据库

TO STANDBY

该主数据库可以转换为备用数据库

grep "Media Recovery Log" /oradata/efin/bdump/alert_efin.log|awk '{print $4}'|

sed -e 's/^/rm /' >/oradata/efin/rmarchlog.sh

/oradata/efin/rmarchlog.sh

cd /oradata/efin/bdump

cat alert_efin.log >>alert_efin_bak.log

alert_efin.log

run{

delete noprompt obsolete;

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

delete noprompt backup completed before 'sysdate-4';

backup database format '/databak/full%t%s';

backup archivelog all format '/databak/arch%t%s' delete all input;

release channel c1;

release channel c2;

release channel c3;

}

~

set lines 155 pages 9999

col thread# for 9999990

col sequence# for 999999990

col grp for 990

col fnm for a50 head "File Name"

col "Fisrt SCN Number" for 999999999999990

break on thread# skip 1

select a.thread#

,a.sequence#

,a.group# grp

, a.bytes/1024/1024 Size_MB

,a.status

,a.archived

,a.first_change# "Fisrt SCN Number"

,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time"

,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time"

from v$standby_log a

order by 1,2,3,4

/

select protection_mode,protection_level,DATABASE_ROLE from v$database;

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY ;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL>