Shulou(Shulou.com)06/01 Report--

Recovery scenarios with corrupted SPFILE files:

Scene

Restore operation

SPFILE file is corrupted

There is a backup

SQL > startup nomount

RMAN > restore spfile from'/ backup/ctl_XXXX'

SQL > shutdown immediate

Oracle instance shut down

SQL > startup

No backup

Manually create pfile files and generate spfile files

Generally speaking, only a few key parameters are needed to pull up the library, but considering the performance problem, you have to restore the parameter settings before you lost them. You can find them in the following places:

The spfile in the 1.awr report, at the end of the awr report

In 2.alert log, there will be a lot of initial parameter output when the library starts.

Control file recovery scene and demonstration: https://blog.51cto.com/wyzwl/1978252

Scene

Recovery method

Recovery condition

One of the control files is corrupted

1.1 copy redundant control files

1. Multi-channel redundant control file mirroring

2. Other redundant control files are not damaged.

1.2 modify control_files parameters to remove corrupted files

Same as above, but this method is not recommended for recovery.

All control files are corrupted

There is a backup

2.1 full recovery of control files through rman backup

1. Back up the control files through rman

2. After backing up the control files, there are continuous archiving and redo files.

2.2 incomplete recovery of control files through rman backup

1. Back up the control files through rman

2. The archive or redo file is lost after backing up the control file.

2.3 full recovery of control files through trace backup

1. Back up the control files through trace

2. After backing up the control files, there are continuous archiving and redo files.

2.4 incomplete recovery of control files through trace backup

1. Back up the control files through trace

2. The archive or redo file is lost after backing up the control file.

No backup

2.5 restore through manual reconstruction of control files (noresetlogs)

1. No valid backup control file

2. No loss or damage to redo files.

2.6Restoration by manually rebuilding control files (resetlogs)

1. No valid backup control file

2. Missing or corrupted redo files

2.7Restoration through SNAPSHOT CONTROLFILE files

Here is a method for recording a recovery control file (not often used)

Log file (redo log) recovery scenario and demo (regardless of non-archive mode):

Scene

Whether to file or not

Restore operation

STATUS=INACTIVE

Log information does not need to be written to a data file

1. Damage while online

two。 Damage after normal shutdown

ARC=YES

Both open and mount status can be executed without data loss

SQL > alter database clear logfile group 1

ARC=NO

Both open and mount status can be executed without data loss

SQL > alter database clear unarchived logfile group 1

STATUS=ACTIVE

Log information needs to be written to a data file

1. Damage while online

two。 Damaged after abnormal closing

ARC=YES

1. If the instance is damaged when it is online, it will be executed online without losing data (never close the library)

SQL > alter database clear unarchived logfile group 1

two。 The instance is damaged after abnormal shutdown, and data is lost.

SQL > startup nomount

SQL > alter system set "_ allow_resetlogs_corruption" = ture scope=spfile

SQL > shutdown immediate

SQL > startup mount

SQL > recover database until cancel

SQL > alter database open resetlogs

ARC=NO

1. When the instance is damaged online, it can be executed directly online without losing data.

SQL > alter database clear unarchived logfile group 1

two。 The instance is damaged after abnormal shutdown, and no data is lost.

SQL > startup nomount

SQL > alter system set "_ allow_resetlogs_corruption" = ture scope=spfile

SQL > shutdown immediate

SQL > startup mount

SQL > recover database until cancel

SQL > alter database open resetlogs

STATUS=CURRENT

Log information does not need to be written to a data file

Damage after normal shutdown

ARC=NO

The instance is damaged after normal shutdown without losing data.

SQL > startup mount

SQL > alter database clear unarchived logfile group 1

Log information needs to be written to a data file

1. Damage while online

two。 Damaged after abnormal closing

ARC=NO

1. If the instance is damaged when it is online, it will be executed online without losing data (never close the library)

SQL > alter system switch logfile

SQL > alter database clear unarchived logfile group 1

two。 The instance is damaged after abnormal shutdown, and the data will be lost.

SQL > startup nomount

SQL > alter system set "_ allow_resetlogs_corruption" = ture scope=spfile

SQL > shutdown immediate

SQL > startup mount

SQL > recover database until cancel

SQL > alter database open resetlogs

Tablespace and data file recovery scenarios and demonstrations:

Scene

Whether or not to fully recover

Restore operation

Data file

System tablespace data file (system,sysaux,undo)

Full backup (archived, rman)

Yes

SQL > startup mount

SQL > alter database recover datafile

SQL > alter database open

No full backup (lack of archives, etc.)

No

RMAN incomplete recovery

1. Basic time recovery

C:\ set nls_date_format=yyyy-dd-mm hh34:mi:ss

C:\ rman target sys/oracle@test nocatalog

RMAN > run {

Startup force mount

Set until time='2010-08-22 1214 000014 08'

Restore database

Recover database

Sql 'alter database open resetlogs

}

2. Restore based on SCN

RMAN > run {

Startup force mount

Set until scn=123456

Restore database

Recover database

Sql 'alter database open resetlogs'

}

3. Restore based on log sequence number

RMAN > run {

Startup force mount

Set until seqence=10

Restore database

Recover database

Sql 'alter database open resetlogs'

}

4. Control file recovery based on backup

C:\ set nls_date_format=yyyy-dd-mm hh34:mi:ss

C:\ rman target sys/oracle@test nocatalog

RMAN > startup force nomount

RMAN > set dbid=1113606269

RMAN > restore controlfile from autobackup maxseq 6

RMAN > alter database mount

RMAN > run {

Set until time='2010-08-22 1214 000014 08'

Restore database

Recover database

Sql 'alter database open resetlogs

}

Common tablespace data file

Full backup (archived, rman)

Yes

SQL > alter database datafile offlie

SQL > alter database recover datafile

SQL > alter tablespace datafile online

No full backup (lack of archives, etc.)

No

If archiving is missing: incomplete recovery of the library based on point in time

If there is no backup:

Alter database datafile 'xxx' offline drop

Or rebuild the control file at the cost of losing the data in the data file

Tablespace

System tablespace

Full backup (archived, rman)

Yes

SQL > shutdown immediate-if you cannot use immediate to shut down the database, use shutdown abort

RMAN > run

{

Startup mount

Restore tablespace system

Recover tablespace system

Alter database open

}

No full backup (lack of archives, etc.)

No

Just as a system tablespace data file is restored without a full backup, an incomplete restore is required:

1. Basic time recovery

2. Restore based on SCN

3. Restore based on log sequence number

4. Control file recovery based on backup

Ordinary tablespace

Full backup (archived, rman)

Yes

SQL > alter tablespace users offlie

SQL > alter database recover tablespace

SQL > alter tablespace users online

No full backup (lack of archives, etc.)

No

If archiving is missing: incomplete recovery of the library based on point in time

If there is no backup:

Alter database datafile 'xxx' offline drop

Full library recovery

All tablespaces

Full backup (archived, rman)

Yes

RMAN > run {

Startup mount

Restore database

Recover database

Sql 'alter database open resetlogs

}

No full backup (lack of archives, etc.)

No

Just as a system tablespace data file is restored without a full backup, an incomplete restore is required:

1. Basic time recovery

2. Restore based on SCN

3. Restore based on log sequence number

4. Control file recovery based on backup

Recovery scenarios for various mistakenly deleted table data operations:

Scene

Restore operation

Erroneous deletion of table data recovery (delete)

Flashback query

Select * from emp as of timestamp to_timestamp ('2004-04-04 09 yyyy-mm-dd hh:mi:ss')

If 11g turns on the flash return file, it can be restored by using this new feature.

The new rman feature of 12c can restore the table separately.

Erroneous deletion of table data recovery (truncate)

Recovery of offline data files using fy_recover_data

Erroneous deletion table recovery (drop)

1. Don't have the same name.

Flashback table original table name to before drop [rename to new table name]

Or

Flashback table "Table name in Recycle Bin" to before drop [rename to New Table name]

two。 Have the same name.

Users may often create and delete the same table multiple times, restore the first version to TEST1 and the second version to TEST2

FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1

FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2

Recovery of mistakenly modified VIEW,FUNTION,PROCEDURE,PACKAGE code

1. Restore using ODU

2. Recover by flashback query

3. Restore through the base table

Flashback scenarios and presentations:

Technical

Application scenario

Steps

Limit

TSPITR

1. In the tablespace, the important data of a table is destroyed or deleted.

2. The misuse of DDL language has changed the structure of one or more tables in the tablespace, so flashback cannot be used to restore these tables.

3. The table is deleted by mistake and is no longer in the Recycle Bin. For example, the table deletion operation with purge option is used.

Set nls_date_format=yyyy-mm-dd hh34:mi:ss

Recover tablespace users until time '2018-01-15 09GV 20 auxdata' 00' auxiliary destination'

Sql 'alter tablespace users online'

1. The database must be in archive mode and there must be a corresponding backup collection.

2. The table space to be restored must be self-contained and not dependent on objects in other table spaces. For example, if a table contains indexes in other tablespaces, they can either participate in the restore together or release the dependency before it can be restored.

Flashback

In the formal production environment, it is not easy to flash back the entire library. In the test environment, you can use this feature to restore the data. For example, after the first round of UAT testing is completed, you can return to the initialization state and proceed to the next round of testing.

Enable flashback database step

Query whether flashback is enabled in the database: select flashback_on from v$database

Close database à start to mount status à open archive, set flashback, cannot set archive path, archive is stored in flashback log directory: alter system set log_archive_dest=''; à set flashback log directory: alter system set db_recovery_file_dest='+data'; à set flashback log retention time for 3 days, default 1440 minutes: alter system set flashback_retention_target=4320 à set flashback log storage size: alter system set db_recovery_file_dest_size=5000g; à open flashback alter database flashback on; à query whether to open à open DB:alter database open

How to flashback

The maximum period of time during which the query flashback can be recovered

Select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log

Restart the DB to MOUNT status à flashback database to a certain point in time: flashback database to timestamp to_timestamp ('2016-01-02 00 flashback database to timestamp to_timestamp 0001-02 00 hh34:mi:ss' Mmmerdd)

Or flashback DB to a SCN:flashback database to scn xxx

1.flashback database cannot solve the error of media failure. Rman is the only choice.

two。 If the data file is deleted or the size of the data file is reduced by user shrink, and flashback cannot be used, you need to use rman to restore the data file before deletion or before reducing the file, and then flash back.

3. If the control file is restored or rebuilt, it cannot be flashed back

4. Flashback returns to the earliest SCN, depending on the earliest SCN recorded in the flashback log

Introduction to other recovery tools:

Recovery tool

Restore referenc

The use of ODU tools

Powerful recovery tool, requires copyright, has a trial version, and has less function

Http://www.oracleodu.com/cn/

AMDU recovery

Extraction and recovery of data files after the ASM disk cannot be mounted

Http://www.eygle.com/archives/2012/03/asm_amdu_recovery.html

Logmnr

When the data deleted by delete cannot be recovered by snapshot flashback, you can recover the data by digging archives with logmnr.

Add online logs that require analysis

Exec dbms_logmnr.add_logfile (logfilename= >'/ opt4/arch/1_22560_911528823.dbf',options= > dbms_logmnr.new)

Add additional online logs

Exec dbms_logmnr.add_logfile (logfilename= >'/ opt4/arch/1_22560_911528823.dbf',options= > dbms_logmnr.addfile)

Analyze added files

Execute dbms_logmnr.start_logmnr (options = > dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only+dbms_logmnr.print_pretty_sql)

Query the corresponding content

Select * from v$logmnr_contents

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.