转如何检查数据库是否处于一致性的状态 以及 如果在DG 库上备份，恢复成一个主库
##sample 0 不完全恢复之后,open resetlogs之前,怎么快速的检查数据库是否处于一致性的状态?
https://blog.csdn.net/msdnchina/article/details/55515313
How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (Doc ID 1354256.1)
（主要思想是检查数据库是否同步，文件是否是fuzzy）
通过从物理datafile中读取 datafile header来查询datafile的当前状态和PIT(Point In Time up to which the datafiles have been recovered)
SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*)
from v$datafile_header
group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;
For Unix:
% export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
% rman target /
For Windows:
> set nls_date_format=dd-mon-rr:hh24:mi:ss
> rman target /
找到备份的命令:
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER '<date>';
or
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER 'sysdate -n';
设置<date> 来限制backup命令的输出,以便定位到你想要的backup的输出,注意完成时间,
对于一个multi-piece的backup,请注意最后一个被创建的backuppiece的完成时间.
在本文中,运行SQL查询语句时,你应该在session级别设置NLS_DATE_FORMAT,如下:
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
检查项 1:Checkpoint Time and Fuzziness
目标:验证被恢复(recovered)到想要的时间点(point in time--PIT)的datafiles,这些数据文件是一致的(FUZZY=NO)
通过从物理datafile中读取 datafile header来查询datafile的当前状态和PIT(Point In Time up to which the datafiles have been recovered)
SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*)
from v$datafile_header
group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;
FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO ONLINE 5311260 31-AUG-2011 23:10:14 6
YES ONLINE 5311260 31-AUG-2011 23:10:14 1
a)验证 checkpoint_time/checkpoint_change# 符合你想要的 UNTIL TIME/SCN,如果不符合,继续recover database,若是你有更多的archived log的话
b)如果有些datafile的FUZZY=YES,这意味着需要更多的recovery.
如果这些归档日志被丢失了,定位这些datafile并决定我们是否可以将这些datafile置于offline(译者注:当然可以用bbed进行修改,只不过风险自当,责任自负)
警告:如果把datafile置为offline,我们会丢失这些datafile中的数据.
如果这些datafile属于system or undo 表空间,没有合适的分析,我们决不能把这些文件置为offline状态.请联系Oracle Support以获取进一步的action.
SQL> select file#, substr(name, 1, 50), substr(tablespace_name, 1, 15), undo_opt_current_change# from v$datafile_header where fuzzy='YES' ;
FILE# SUBSTR(NAME,1,50) SUBSTR(TABLESPA UNDO_OPT_CURRENT_CHANGE#
---------- -------------------------------------------------- --------------- ------------------------
3 /u01/app/oracle/oradata/prod111/undotbs01.dbf
###参考Step by Step method to create Primary/Standby Database from Standby Backup (文档 ID 1604251.1)
Note:
Only from 11g, backup of controlfile which is taken as " Backup current controlfile" can be restored at standby database as
(a) current controlfile ( restore controlfile from '<>.BKP'; )
(b) standby controlfie ( restore standby controlfile from <>.bkp')
Step a1:
init ( minimum required parameter )
>>>
DB_NAME=MASTERDB # Database name of standby/primary
DB_UNIQUE_NAME="MASTERDBSNGS" # can be set for differentiation
control_files='/<path><>.CTL'
log_file_name_convert = '<your current standby REDO location >,< your new host location >' ( # optional )
<<<
Step a2:
$ export ORACLE_SID=MASTERDBSNGS
$ sqlplus / as sysdba
sql> startup nomount;
Step a3:
In another session
$ export ORACLE_SID=MASTERDBSNGS
$ rman target /
RMAN> restore controlfile from '<backup location/ctl.BKP'; for ( case a)
RMAN> alter database mount;
RMAN> catalog start with '< backup piece location>';
RMAN
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
set newname for datafile 1 to '< new location/ DF name> ';
set newname for datafile 2 to '< new location/ DF name> ';
...
set until sequence 212; # this is to set as per your requirement
restore database;
switch datafile all;
recover database;
}
Step a4: alter database open resetlogs.
@ mount stage
SQL> alter database rename file '<old file location and name>' to '<new location and name>';
a4.1 Option (1) alter database open resetlogs;
a4.2 Option (b) Activate the Standby
SQL> Alter database activate physical standby database ;
SQL> Alter database open;
n 11.2.0.4 , you can follow below workaround if patch 18455956 NOT applied.
Since controlfile type would be 'STANDBY step (a4) will fail at 'Alter database open resetlogs'
With any of below option you can open the database as Primary role
Option (a) Create trace file of controlfile
recreate the controlfile from trace
recover database using backup controlfile until cancel # Cancel the recovery
alter database open resetlogs
Option (b) Activate the Standby
SQL> Alter database activate physical standby database ;
SQL> Alter database open;
##sample 1 如何对10g容灾库进行一个一致性备份
How to take consistent backups at standby site (文档 ID 1292126.1)
-》1.备库上停下MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
备库上执行
RMAN> backup database plus archivelog ;
##10G 主库上执行
RMAN> backup current controlfile;
（Note: In 10g, you will need to backup the controlfile from the primary site.
The above will result in a consistent, self-contained backup.）
3) 备份重启MRP restart managed recovery:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNETED FROM SESSION;
3) 使用备份恢复 restart managed recovery:
To restore from this backup:
RMAN> startup nomount;
RMAN> restore controlfile from 'controlfile backuppiece name and location';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database noredo;
##sample 2 如何在11g ADG环境备份一个一致性的备份；
Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode (文档 ID 1419923.1)
1.如果在11.2.0.4 主库上备份，只要执行这条命令即可完成一致性备份。
Consistent means, that the backup is having the datafiles and the related archived redologs, so that it can be opened with this backup. This done on the Primary database by using :
RMAN> backup database plus archivelog;
This will execute the following :
Backup the existing archived redologs
Backup the datafiles
Switch a logfile
Backup all the archives again, but this time it will include all the archived redologs created during and just after the datafile backup. This are the archives which are required to make the datafile backup consistent.
2.如果在11.2.0.4 上standby db ADG上执行,就需要显性在主库上执行如下命令，对当前日志进行归档，
因为在恢复模式下对备用数据库进行的备份也不会得到所有归档日志，除非在主日志上执行切换并进行第二次存档日志备份, 否则需要第二次备份存档日志。
-》1.备库上准备远程切换主库的归档日志的脚本
shell script : /usr/local/bin/logswitch.sh
#!/bin/ksh
#
# Change <passwd>
# <primary_db>
#
sqlplus -s "sys/<passwd>@<primary_db> as sysdba" <<EOF
alter system archive log current;
exit
EOF
% chmod 755 /usr/local/bin/logswitch.sh
-》2 备库上执行如下命令，归档进行二次备份。
Example of RMAN backup script on the Active Dataguard Standby database
% rman target / catalog <un/pw@catalog_db>
Rman> Configure controlfile autobackup on ;--------------> This would ensure Controlfile auto backup
RMAN> backup database plus archivelog delete input;
host "/usr/local/bin/logswitch.sh";
backup archivelog all delete input;
注意：
（A backup taken of a MOUNTED standby database in recovery mode will also not get all of the archived logs required unless a switch is performed on the primary and a second archived log backup is taken.
）
（The missing 'Log Switch' by making an explicit 'Log Switch' on the Primary.
also does apply to the BASIC DATA GUARD case as well where the database is MOUNTED and in
managed recovery mode (MRP).
A backup taken of a MOUNTED standby database in recovery mode will also not get all of the archived logs required unless a switch is performed on the primary and a second archived log backup is taken.
）
通过在主服务器上创建显式 "日志切换" 来丢失 "日志切换"。
也适用于基本数据保护表的情况下, 以及在其中的数据库是移动和在托管恢复模式 (MRP)。
在恢复模式下对备用数据库进行的备份也不会得到所有归档日志，除非在主日志上执行切换并进行第二次存档日志备份, 否则需要存档日志。
