ASM OMF 本机恢复重大故障 online log 被清空

2023-12-14 17:57:46

1) Take appropriate RMAN backup of the production RAC database. Note that you should turn on the CONTROLFILE AUTOBACKUP configuration so that we have the controlfile backed up after the database backup. When we restore the controlfile on new host from this autobackup piece, it will have the information of the latest backup.

RMAN> run{
2> allocate channel c1 type disk format '/<PATH>/%U';
3> backup database;
4> backup archivelog all;
5> }

allocated channel: c1
channel c1: sid=133 instance=<oracle_sid> devtype=DISK

Starting backup at 12-FEB-07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/<PATH>/system01.dbf
input datafile fno=00002 name=/<PATH>/undotbs01.dbf
input datafile fno=00005 name=/<PATH>/undotbs02.dbf
input datafile fno=00003 name=/<PATH>/sysaux01.dbf
input datafile fno=00004 name=/<PATH>/users01.dbf
channel c1: starting piece 1 at 12-FEB-07
channel c1: finished piece 1 at 12-FEB-07
piece handle=/<PATH>/09i9sruq_1_1 tag=TAG20070212T162458 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:05
Finished backup at 12-FEB-07

Starting backup at 12-FEB-07
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=56 recid=3 stamp=613417105
input archive log thread=1 sequence=57 recid=4 stamp=613417106
input archive log thread=1 sequence=58 recid=5 stamp=614363168
input archive log thread=2 sequence=1 recid=1 stamp=613417090
input archive log thread=2 sequence=2 recid=2 stamp=613417093
input archive log thread=2 sequence=3 recid=6 stamp=614363170
channel c1: starting piece 1 at 12-FEB-07
channel c1: finished piece 1 at 12-FEB-07
piece handle=/<PATH>/0ai9ss14_1_1 tag=TAG20070212T162610 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
Finished backup at 12-FEB-07

Starting Control File and SPFILE Autobackup at 12-FEB-07
piece handle=/<PATH>/c-610677177-20070212-00 comment=NONE
Finished Control File and SPFILE Autobackup at 12-FEB-07
released channel: c1

2) Create a PFILE for the single instance database using the production RAC parameter file.? Copy a pfile from source database and make necessary modifications.??
a) don't forget to modify the following parameters depending on the directory structure of the new host: audit_file_dest, background_dump_dest, control_files, core_dump_dest, log_archive_dest_1, user_dump_dest etc
b) remove RAC specific parameters such as cluster_database_instances, cluster_database etc
c) for the parameter undo_tablespace, mention any one undo tablespace name

Known issue which may require an additional parameter to be set :
???Note 334899.1?RMAN Duplicate from RAC backup fails ORA-38856

3) Move the backup pieces and the modified INIT.ORA file to the new host. Starting from 10g it is NO longer compulsory to copy the RMAN backup pieces to exactly the same location on the new host as the production location.

4) Use the pfile created above to STARTUP NOMOUNT the database on the new host

oracle@test-br ractest]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 16 03:14:23 2007

Copyright (c) 1982, 2005, Oracle. ?All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area ?167772160 bytes
Fixed Size ? ? ? ? ? ? ? ? ?1218292 bytes
Variable Size ? ? ? ? ? ? ?62916876 bytes
Database Buffers ? ? ? ? ? 96468992 bytes
Redo Buffers ? ? ? ? ? ? ? ?7168000 bytes

5) Now invoke RMAN and restore the controlfile specifying the location where the controlfile autobackup piece is restored on this new server. You can mount the database once the controlfile is restored successfully.

[oracle@test-br ractest]$ rman target / nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Feb 16 03:16:31 2007

Copyright (c) 1982, 2005, Oracle. ?All rights reserved.

connected to target database: <db_name> (not mounted)
using target database control file instead of recovery catalog

RMAN> restore controlfile from '/<PATH>/c-610677177-20070212-00';

Starting restore at 16-FEB-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/<PATH>/control01.ctl
Finished restore at 16-FEB-07

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

6) You can skip this step if you have restored the RMAN backup pieces to exactly the same location they were backed up on production. If this is not the case then you need to catalog the RMAN backup pieces to make RMAN aware of their new location on the new host. Note that CATALOG BACKUPPIECE command is available only starting from 10g.

RMAN>? catalog start with '/<location of copied backups>' noprompt;

OR

RMAN> catalog backuppiece '/<PATH>/09i9sruq_1_1';

cataloged backuppiece
backup piece handle=/<PATH>/09i9sruq_1_1 recid=10 stamp=614661579

RMAN> catalog backuppiece '/<PATH>/0ai9ss14_1_1';

cataloged backuppiece
backup piece handle=/<PATH>/0ai9ss14_1_1 recid=11 stamp=614661599

7) Now we'll determine the point upto which media recovery should run on the restored database.

RMAN> list backup of archivelog all;

List of Archived Logs in backup set 9
Thrd Seq ? ? Low SCN ? ?Low Time ?Next SCN ? Next Time
---- ------- ---------- --------- ---------- ---------
1 ? ?56 ? ? ?214541 ? ? 01-FEB-07 226238 ? ? 01-FEB-07
1 ? ?57 ? ? ?226238 ? ? 01-FEB-07 226240 ? ? 01-FEB-07
1 ? ?58 ? ? ?226240 ? ? 01-FEB-07 233107 ? ? 12-FEB-07
2 ? ?1 ? ? ? 186185 ? ? 28-JAN-07 225714 ? ? 01-FEB-07
2 ? ?2 ? ? ? 225714 ? ? 01-FEB-07 226037 ? ? 01-FEB-07
2 ? ?3 ? ? ? 226037 ? ? 01-FEB-07 233110 ? ? 12-FEB-07

Check the last archive sequence for all redo threads and select the archive sequence having LEAST "Next SCN" among them. In our case sequence 58 of thread 1 has Next SCN of 233107 while sequence 3 of thread 2 has Next SCN of 233110. Since squence 58 of thread 1 has least Next SCN we will recover upto this point. (If you are keen to have recovery run until some specific time you can always give SET UNTIL TIME)

8) Having determined the point upto which media recovery should run, start the restore/recovery using:
??? Please note in below example we choose Until sequence as 59(one more then the value selected in step? 7 that is sequence 58)? because When we specify a sequence number, it is considered as an?UPPER limit, RMAN selects only?
??? those? files which are needed for the recovery and uses 59 as an upper log sequence number while recovering database and would recovery upt log sequence number upto 58? .

RMAN> run {
2> set until sequence 59 thread 1;
3> set newname for datafile 1 to '/<PATH>/system01.dbf';
4> set newname for datafile 2 to '/<PATH>/undotbs01.dbf';
5> set newname for datafile 3 to '/<PATH>/sysaux01.dbf';
6> set newname for datafile 4 to '/<PATH>/users01.dbf';
7> set newname for datafile 5 to '/<PATH>/undotbs02.dbf';
8> restore database;
9> switch datafile all;
10> recover database;
11> }

NOTE:? to determine the datafiles needed for 'set newname' use:

RMAN>? report schema

OR

RMAN>? select file#, name from v$datafile;?

In the case where the datafiles reside on a ASM diskgroup for the clone/destination database, you may use SET NEWNAME as below:

set newname for datafile 1 to '+<disk_group>';
set newname for datafile 2 to '+<disk_group>';
....

Known issue in Oracle10g:
???Note 1146703.1?Rman Restore From RAC ASM To Single Instance Non ASM Fails With ORA-00600 [kgeade_is_0]

CAUTION !!!!:

刚刚查看了一下 alert，应该是 test1 database启动时打开了PRD的online log
?
Errors in file /u01/app/grid/diag/rdbms/test/test1/trace/test1_mz01_3948203.trc:
ORA-01110: data file 978: '+RECO02/TEST/ED7E391A06975C85E0530B15D70A3BC1/DATAFILE/tsindx_22.2462.1154454635'
archivelog header validation failure for file +DATA01/PRD/ONLINELOG/group_11.997.1135014133
***
Corrupt block seq: 17334 blocknum=1.
Bad header found during accessing archived log
Data in bad block - flag:1. format:34. bno:1. seq:17338
beg:0 cks:54438
calculated check value: 54438
Reread of seq=17334, blocknum=1, file=+DATA01/PRD/ONLINELOG/group_11.997.1135014133, found same corrupt data
Reread of seq=17334, blocknum=1, file=+DATA01/PRD/ONLINELOG/group_11.997.1135014133, found same corrupt data
Reread of seq=17334, blocknum=1, file=+DATA01/PRD/ONLINELOG/group_11.997.1135014133, found same corrupt data
Reread of seq=17334, blocknum=1, file=+DATA01/PRD/ONLINELOG/group_11.997.1135014133, found same corrupt data
Reread of seq=17334, blocknum=1, file=+DATA01/PRD/ONLINELOG/group_11.997.1135014133, found same corrupt data
archivelog header validation failure for file +RECO01/PRD/ONLINELOG/group_11.951.1135014135

If Online Redolog's (ORL's) are Oracle Managed Files (OM), and the Clone is being done on any of Source RAC Node (where ORLs are accessible from), renaming ORL will try to remove Source ORL files!

Please review:?Using Oracle Managed Files

The following statements are used to rename files:

??? ALTER DATABASE RENAME FILE

??? ALTER TABLESPACE ... RENAME DATAFILE

These statements do not actually rename the files on the operating system, but rather, the names in the control file are changed. If the old file is an Oracle managed file and it exists, then it is deleted. You must specify each filename using the conventions for filenames on your operating system when you issue this statement."

Instead of renaming, we can recreate the controlfile with desired name for ORLs:

SQL> alter database backup controlfile to trace as '/tmp/control.sql' resetlogs ;

Edit /tmp/control.sql and modify desired ORL names.

Since we determined previously that media recovery should run until sequence 58 hence we use? SET UNTIL SEQUENCE 59 (+1) above. You also need to use SET NEWNAME clause to restore datafiles to a location on the new host which is different from the production path. Finally, SWITCH DATAFILE ALL clause updates these new datafile locations in the controlfile.

9) Once RMAN restore/recovery finishes, you will want to rename the online redolog files before opening the database in case the production path of redo log files is not available on the new host. After renaming the redolog files, the database can be opened with RESETLOGS

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/<old_path>/redo01.log
/<old_path>/redo02.log
/<old_path>/redo03.log
/<old_path>/redo04.log
/<old_path>/redo05.log
/<old_path>/redo06.log

6 rows selected.

SQL> alter database rename file '/<old_path>/redo01.log' to '/<new_path>/redo01.log';

Database altered.

SQL> alter database rename file '/<old_path>/redo02.log' to '/<new_path>/redo02.log';

Database altered.

SQL> alter database rename file '/<old_path>/redo03.log' to '/<new_path>/redo03.log';

Database altered.

SQL> alter database rename file '/<old_path>/redo04.log' to '/<new_path>/redo04.log';

Database altered.

SQL> alter database rename file '/<old_path>/redo05.log' to '/<new_path>/redo05.log';

Database altered.

SQL> alter database rename file '/<old_path>/redo06.log' to '/<new_path>/redo06.log';

Database altered.

SQL> alter database open resetlogs;

Database altered.

In case you want to create the online redo logs on a ASM diskgroup (+DATA for eg) for the clone/destination database, you may use below commands:

alter database rename file '/<old_path>/redo01.log' to '+<disk_group>';
alter database rename file '/<old_path>/redo02.log' to '+<disk_group>';
...

10) Once the database is opened successfully, you may remove the redolog groups for redo threads of other instances

SQL> select THREAD#, STATUS, ENABLED
??2 ?from v$thread;

?? THREAD# STATUS ENABLED
---------- ------ --------
?? ? ? ? 1 OPEN ? PUBLIC
?? ? ? ? 2 CLOSED PRIVATE

SQL> select group# from v$log where THREAD#=2;

?? ?GROUP#
----------
?? ? ? ? 4
?? ? ? ? 5
?? ? ? ? 6

SQL> alter database disable thread 2;

Database altered.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance racdb2 (thread 2) needs to be archived
ORA-00312: online log 4 thread 2: '/<PATH>/redo04.log'

SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> select THREAD#, STATUS, ENABLED from v$thread;

?? THREAD# STATUS ENABLED
---------- ------ --------
?? ? ? ? 1 OPEN ? PUBLIC

11) Now you can remove the undo tablespaces of other instances and create a new temporary tablespace to complete the activity.

SQL> sho parameter undo;

NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE ? ? ? ?VALUE
------------------------------------ ----------- ------------------------------
undo_management ? ? ? ? ? ? ? ? ? ? ?string ? ? ?AUTO
undo_retention ? ? ? ? ? ? ? ? ? ? ? integer ? ? 900
undo_tablespace ? ? ? ? ? ? ? ? ? ? ?string ? ? ?UNDOTBS1
SQL>
SQL>
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/<PATH>/temp01.dbf

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME
------------------------------
TEMP

SQL> create temporary tablespace TEMP1
??2 ?tempfile '/<PATH>/temp01.dbf'
??3 ?size 50M;

Tablespace created.

SQL> alter database default temporary tablespace TEMP1;

Database altered.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SYMPTOMS

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the
Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual
environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Database Name: ORCL
Diskgroup Names: +FRA

*************

This issue can occur for databases both in ARCHIVELOG and NOARCHIVELOG mode.

After an instance crash or SHUTDOWN ABORT a simple restart is all that is needed to allow instance recovery to take place and recover from the current online redolog.

If however the instance is MOUNTED and recovery is done manual you could find , depending on how the recovery is done , that Oracle requests an archivelog that does not yet exist.

The alert log shows the following:

Completed: ALTER DATABASE MOUNT
Thu Feb 07 15:59:02 2013
Thu Feb 07 15:59:40 2013
ALTER DATABASE RECOVER database until CANCEL
Media Recovery Start
?started logmerger process
Parallel Media Recovery started with 4 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER database until CANCEL ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log +FRA/orcl/archivelog/ORCL_806768403_1_18.arc
Thu Feb 07 15:59:45 2013
Errors with log +FRA/orcl/archivelog/orcl_806768403_1_18.arc
Errors in file /<path>/trace/ORCL_pr00_23841.trc:
ORA-00308: cannot open archived log '+FRA/orcl/archivelog/orcl_806768403_1_18.arc'
ORA-17503: ksfdopn:2 Failed to open file +FRA/orcl/archivelog/orcl_806768403_1_18.arc
ORA-15173: entry 'orcl_806768403_1_18.arc' does not exist in directory 'archivelog'
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...

?
The requested archived redolog does not yet exist because it is the current online redolog that is yet to be archived.

CAUSE

If the instance had simply been restarted (SQL>startup) instance recovery would have automatically applied the online log current at the time of the crash to complete recovery.

If the instance is mounted and then 'RECOVER DATABASE' issued, Oracle would have simply applied the current online redolog to complete recovery.

If however the instance is first mounted and then 'RECOVER DATABASE UNTIL CANCEL' is issued, manual media recovery is invoked and media recovery always expects to use archivelogs.? Oracle will determine the correct sequence of the current online redolog and then request this as an archivelog which doesn't exist.--什么都不干就能启动，介入了就麻烦了

SOLUTION

Determine the current online log , do recovery and then? open with resetlogs:

SQL> startup mount
ORACLE instance started.

Total System Global Area 3373858816 bytes
Fixed Size????????????????? 2257864 bytes
Variable Size??????????? 1845496888 bytes
Database Buffers???????? 1509949440 bytes
Redo Buffers?????????????? 16154624 bytes
Database mounted.
SQL> select f.member, v.sequence#, v.group#, v.status from v$log v, v$logfile f
where v.group# = f.group#;

MEMBER
--------------------------------------------------------------------------------

?SEQUENCE#???? GROUP# STATUS
---------- ---------- ----------------
C:\<path>\REDO01.LOG
???????? 4????????? 1 CURRENT

C:\<path>\REDO04.LOG
???????? 3????????? 4 INACTIVE

C:\<path>\REDO02.LOG
???????? 2? ? ? ? ?2 INACTIVE

SQL> recover database until cancel;
ORA-00279: change 12269086 generated at 02/08/2013 12:40:57 needed for thread 1
ORA-00289: suggestion :
C:\<path>\ARCHIVELOG\2013_02_08\O1_MF_1_4_%U_.ARC
ORA-00280: change 12269086 for thread 1 is in sequence #4

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\<path>\REDO01.LOG
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

?Resetlogs is necessary if? recovery used 'until cancel syntax'.

-------------------------------redo在都好办，不一定要resetlogs----

SQL> shutdown abort;
?
SQL> startup mount;?

SQL> recover database until cancel;

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/CDB/DATAFILE/system.275.1148478571'

ORA-01112: media recovery not started

SQL> recover database until cancel;
ORA-00279: change 6677588 generated at 12/14/2023 00:47:26 needed for thread 1
ORA-00289: suggestion : +DATA1
ORA-00280: change 6677588 for thread 1 is in sequence #114

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '+DATA1'
ORA-17503: ksfdopn:2 Failed to open file +DATA1
ORA-15045: ASM file name '+DATA1' is not in reference form

ORA-00308: cannot open archived log '+DATA1'
ORA-17503: ksfdopn:2 Failed to open file +DATA1
ORA-15045: ASM file name '+DATA1' is not in reference form

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/CDB/DATAFILE/system.275.1148478571'

ORA-01112: media recovery not started

SQL> startup force ?
ORACLE instance started.

Total System Global Area 2382361320 bytes
Fixed Size ? ? ? ? ? ? ? ? ?9167592 bytes
Variable Size ? ? ? ? ? ? 838860800 bytes
Database Buffers ? ? ? ? 1526726656 bytes
Redo Buffers ? ? ? ? ? ? ? ?7606272 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/CDB/DATAFILE/system.275.1148478571'

ORA-01112: media recovery not started

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log '+DATA1'
ORA-17503: ksfdopn:2 Failed to open file +DATA1
ORA-15045: ASM file name '+DATA1' is not in reference form

ORA-00308: cannot open archived log '+DATA1'
ORA-17503: ksfdopn:2 Failed to open file +DATA1
ORA-15045: ASM file name '+DATA1' is not in reference form

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

文章来源:https://blog.csdn.net/jnrjian/article/details/134998954
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！