Simulate database recovery for file Loss Control

For more information, see Database SYS @ LEO1showuserUSERisSYSSYS @ LEO1select * fromv $ version. BANNER --------------------------------------------

For more information, see Database SYS @ LEO1showuserUSERisSYSSYS @ LEO1select * fromv $ version. BANNER --------------------------------------------

More highlights available

1. database version

SYS @ LEO1> showuser

USER is "SYS"

SYS @ LEO1> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database11g Enterprise Edition Release 11.2.0.1.0-64bit Production

PL/SQL Release11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version11.2.0.1.0-Production

1. Simulate database recovery after the control file is lost (full recovery ).

Today's topic is backup and recovery, which aims to protect data security. As we all know, Oracle accounts for 50% of the market, which is inseparable from its powerful data protection measures, the following is a brief introduction.

1) physical backup

Cold backup: This is the most original backup method and the simplest and most feasible method. Just like copying a file, you can copy the database shutdown directly, which is easy to operate and fast to restore. In the absence of professionals, it is simple and feasible to tell them that this operation is not impossible, but you have not met it. Everything is possible!

Hot Backup: Oracle professional backup tool RMAN, which is available in 8i and is very powerful. It can be backed up and restored in many dimensions, RMAN can be used for online backup and recovery.

2) logical backup

Exp/Imp: Table-level user-level database-level logical backup, the logic is for the business level, for example, I just want to back up the content of the person employee address table, this method will be very simple, it highlights the fact that the backed-up files are well migrated and compatible with different versions.

Expdp/Impdp: This is the advanced version of the above two tools. It can be compressed to transfer tablespaces faster, but it can only be used on the server side.

3) instance recovery

What is an instance, that is, a memory zone + background process? INSTANCE recovery means memory data recovery, such as sudden shutdown and forced database shutdown, the instance is automatically restored in the background when you start the startup. SMON Process Execution

4) Media recovery

Recover hard disk data. For example, if a file is accidentally deleted from a bad block, it must be manually restored.

After introducing several backup and recovery methods, let's go to the topic and take a look at how to control file loss recovery.

5) SYS @ LEO1> the archivelog list database is not archived.

Database logmode No Archive Mode

Automaticarchival Disabled

Archivedestination/u02/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online logsequence 71

Current logsequence 73

We need to first make an RMAN full backup, first start the archiving function

[Oracle @ leonarding1oracle] $ pwd

/U02/app/oracle

[Oracle @ leonarding1oracle] $ mkdir archdata create an archive log directory

In ORACLE10g and 11g versions, the default log archiving path of ORACLE is the flash recovery area, but we can also change it to the directory path specified by ourselves.

SYS @ LEO1> altersystem set log_archive_dest_1 = 'location =/u02/app/oracle/archdata' scope = both;

System altered.

SYS @ LEO1> setlinesize 300 pagesize 999

The archived log storage path has taken effect.

SYS @ LEO1> selectdest_name, destination, status, error from v $ archive_dest where dest_name = 'Log _ ARCHIVE_DEST_1 ';

DEST_NAME DESTINATION STATUS ERROR

Certificate ------------------------------------------------------------------------------------------------------------------------------------------

LOG_ARCHIVE_DEST_1/u02/app/oracle/archdata VALID

Start to mount, and start archive Mode

SYS @ LEO1> shutdownimmediate

Database closed.

Databasedismounted.

ORACLE instanceshut down.

SYS @ LEO1> startupmount mount status

ORACLE instancestarted.

Total SystemGlobal Area 471830528 bytes

Fixed Size 2214456 bytes

Variable Size 150996424 bytes

DatabaseBuffers 310378496 bytes

Redo Buffers 8241152 bytes

Database mounted.

SYS @ LEO1> alterdatabase archivelog; Start archive Mode

Database altered.

SYS @ LEO1> alterdatabase open; open the database

Database altered.

Note: Any alter database operation modifies the control file.

All alter system operations modify the parameter file.

SYS @ LEO1> altersystem switch logfile; manually switch logs (checkpoints are not triggered and automatic switching is performed)

System altered.

SYS @ LEO1> selectsequence #, name, archived, applied from v $ archived_log; view archived log information

SEQUENCE # NAME ARC APPLIED

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------

73/u02/app/oracle/archdata/201773_813654649.dbf YES NO

View at the operating system level.

[Oracle @ leonarding1archdata] $ ll

Total 5624

-Rw-r ----- 1 oracle asmadmin 5757952 Apr 25 21:28 41773_813654649.dbf

SYS @ LEO1> archivelog list

Database logmode Archive Mode

Automatic archiving startup

Archivedestination/u02/app/oracle/archdata archiving log directory

Oldest online logsequence 72 old online log no.

Next log sequenceto archive 74 Next archive log no.

Current logsequence 74 Current log no.

Now we have to back up the entire RMAN database. Before that, we need to set the RMAN environment variable.

6) log on to RMAN

[Oracle @ leonarding1archdata] $ rman target sys/oracle

Recovery Manager: Release 11.2.0.1.0-Production on Fri Apr 26 06:05:24 2013

Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.

Connectedto target database: LEO1 (DBID = 1692458681) environment variables can only be displayed when they are connected to the target database. These metadata are stored in the control file.

Displays the environment variables of the current RMAN.

RMAN> show all;

Using targetdatabase control file instead of recovery catalog

RMAN configurationparameters for database with db_unique_name LEO1 are:

Assumeretention policy to redundancy 1; # default

Configure backupoptimization off; # default

Configure defaultdevice type to disk; # default

Configurecontrolfile autobackup off; # default

Configurecontrolfile autobackup format for device type disk to '% F'; # default

Configure devicetype disk parallelism 1 backup type to backupset; # default

Configure datafilebackup copies for device type disk to 1; # default

Configure archivelogbackup copies for device type disk to 1; # default

Configuremaxsetsize to unlimited; # default

Configureencryption for database off; # default

Configureencryption algorithm 'aes128 '; # default

Configurecompression algorithm 'basic 'as of release 'default' optimize for load true; # DEFAULT

Configurearchivelog deletion policy to none; # default

Configure snapshotcontrolfile name to '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_leo1.f'; # default

Create RMAN default backup media storage directory/u02/app/oracle/backup

[Oracle @ leonarding1oracle] $ mkdir backup

RMAN> configure channel device type disk format'/u02/app/oracle/backup/DB _ % U ';

New RMANconfiguration parameters:

Configure channeldevice type disk format'/u02/app/oracle/backup/DB _ % U ';

New RMANconfiguration parameters are successfully stored new man configuration parameters take effect

The configuration control file is automatically backed up and saved to the/u02/app/oracle/backup/control directory.

[Oracle @ leonarding1backup] $ mkdir control

[Oracle @ leonarding1control] $ pwd

/U02/app/oracle/backup/control

RMAN> configure controlfile autobackup on; Enable automatic backup of Control Files

New RMANconfiguration parameters:

Configurecontrolfile autobackup on;

New RMANconfiguration parameters are successfully stored

RMAN> configure controlfile autobackup format for device type diskto '/u02/app/oracle/backup/control/cf _ % F ';

New RMANconfiguration parameters: configuration control file automatic backup directory and format

Configurecontrolfile autobackup format for device type disk to '/u02/app/oracle/backup/control/cf _ % F ';

New RMANconfiguration parameters are successfully stored

Adjust the backup media retention period to 7 days

RMAN> assumeretention policy to recovery window of 7 days;

New RMANconfiguration parameters:

Assumeretention policy to recovery window of 7 DAYS;

New RMANconfiguration parameters are successfully stored

Display RMAN environment variables after Configuration

RMAN> show all;

RMAN configurationparameters for database with db_unique_name LEO1 are:

Assumeretention policy to recovery window of 7 DAYS;

Configure backupoptimization off; # default

Configure defaultdevice type to disk; # default

Configurecontrolfile autobackup on;

Configurecontrolfile autobackup format for device type disk to '/u02/app/oracle/backup/control/cf _ % F ';

Configure devicetype disk parallelism 1 backup type to backupset; # default

Configure datafilebackup copies for device type disk to 1; # default

Configurearchivelog backup copies for device type disk to 1; # default

Configurechannel device type disk format '/u02/app/oracle/backup/DB _ % U ';

Configuremaxsetsize to unlimited; # default

Configureencryption for database off; # default

Configure encryptionalgorithm 'aes128 '; # default

Configurecompression algorithm 'basic 'as of release 'default' optimize for load true; # DEFAULT

Configurearchivelog deletion policy to none; # default

Configure snapshotcontrolfile name to '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_leo1.f'; # default

The color is the variable we just modified.

7) Enable the RMAN compression and backup function for database full backup Oracle10g only compress RMAN metadata 11g truly compress data

Backup as compressed backupset full database format

'/U02/app/oracle/backup/full_bk1 _ % u % p % s. rmn' include current controlfile

Plus

Archivelog format '/u02/app/oracle/backup/arch_bk1 _ % u % p % s. rmn' delete all input; after all the backups are complete, delete the old archive logs that have been backed up.

If you want to use the default channel to back up the database once by default and delete the archived logs that have been backed up at the same time, the command is

RMAN> backup ascompressed backupset full database include current controlfile plus archivelogdelete all input;

Starting backup at26-APR-13 backup time

Current logarchived

Allocated channel: ORA_DISK_1 default channel Disk

ChannelORA_DISK_1: SID = 140 device type = DISK

ChannelORA_DISK_1: starting compressed archived log backup set first compresses the backup archive log

Channel ORA_DISK_1: specifying archived log (s) in backup set backs up 73 74 archived logs

Input archived logthread = 1 sequence = 73 RECID = 1 STAMP = 813706084

Input archived logthread = 1 sequence = 74 RECID = 2 STAMP = 813739820

ChannelORA_DISK_1: starting piece 1 at 26-APR-13

ChannelORA_DISK_1: finished piece 1 at 26-APR-13

Piece handle =/u02/app/oracle/backup/DB_01o81bpd_1_1 tag = tag2013020.t065020 comment = NONE

ChannelORA_DISK_1: backup set complete, elapsed time: 00:00:01 backup completed in 1 second

ChannelORA_DISK_1: deleting archived log (s) delete archived logs 73 74 that have been backed up

Archived log filename =/u02/app/oracle/archdata/logs 73_813654649.dbf RECID = 1 STAMP = 813706084

Archived log filename =/u02/app/oracle/archdata/login 74_813654649.dbf RECID = 2 STAMP = 813739820

Finished backup at26-APR-13.

Starting backup at26-APR-13.

Using channelORA_DISK_1

ChannelORA_DISK_1: starting compressed full datafile backup set compresses the backup data file

ChannelORA_DISK_1: specifying datafile (s) in backup set specifies to back up the following data files

Input datafilefile number = 00001 name =/u02/app/oracle/oradata/LEO1/system01.dbf

Input datafilefile number = 00002 name =/u02/app/oracle/oradata/LEO1/sysaux01.dbf

Input datafilefile number = 00003 name =/u02/app/oracle/oradata/LEO1/undotbs01.dbf

Input datafilefile number = 00005 name =/u02/app/oracle/oradata/LEO1/leo1.dbf

Input datafilefile number = 00004 name =/u02/app/oracle/oradata/LEO1/users01.dbf

ChannelORA_DISK_1: starting piece 1 at 26-APR-13

ChannelORA_DISK_1: finished piece 1 at 26-APR-13

Piece handle =/u02/app/oracle/backup/DB_02o81bpf_1_1 tag = tag2013020.t065022 comment = NONE

ChannelORA_DISK_1: backup set complete, elapsed time: 00:01:45 when the backup is completed

ChannelORA_DISK_1: starting compressed full datafile backup set

ChannelORA_DISK_1: specifying datafile (s) in backup set

Includingcurrent control file in backupset this is a backup data file that also contains a backup control file

ChannelORA_DISK_1: starting piece 1 at 26-APR-13

Channel ORA_DISK_1: finished piece 1 at 26-APR-13

Piece handle =/u02/app/oracle/backup/DB_03o81bso_1_1 tag = tag2013020.t065022 comment = NONE

ChannelORA_DISK_1: backup set complete, elapsed time: 00:00:01 backup completed in 1 second

Finished backup at26-APR-13.

Starting backup at26-APR-13.

Current logarchived

Using channelORA_DISK_1

ChannelORA_DISK_1: starting compressed archived log backup set

ChannelORA_DISK_1: specifying archived log (s) in backup set

Input archived logthread = 1 sequence = 75 RECID = 3 STAMP = 813739930 back up 75 archived logs

Channel ORA_DISK_1: starting piece 1 at 26-APR-13

ChannelORA_DISK_1: finished piece 1 at 26-APR-13

Piece handle =/u02/app/oracle/backup/DB_04o81bsq_1_1 tag = tag2013020.t065210 comment = NONE

ChannelORA_DISK_1: backup set complete, elapsed time: 00:00:01 backup completed in 1 second

ChannelORA_DISK_1: deleting archived log (s) delete archived logs that have been backed up 75

Archived log filename =/u02/app/oracle/archdata/logs 75_813654649.dbf RECID = 3 STAMP = 813739930

Finished backup at26-APR-13.

Starting ControlFile and SPFILE Autobackup at 26-APR-13 automatic backup of startup control files and parameter files

Piece handle =/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00 comment = NONE

Finished ControlFile and SPFILE Autobackup at 26-APR-13 complete automatic backup

On the operating system, you can find the corresponding backup set and delete the old archive logs that have been backed up.

[Oracle @ leonarding1backup] $ pwd

/U02/app/oracle/backup

[Oracle @ leonarding1backup] $ ll

Total 249468

Drwxr-xr-x 2 oracle oinstall 4096 Apr 26 06: 52 control

-Rw-r ----- 1 oracle asmadmin 2790912 Apr 26 06: 50DB_01o81bpd_1_1

-Rw-r ----- 1 oracle asmadmin 251551744 Apr 26 06:52 DB_02o81bpf_1_1

-Rw-r ----- 1 oracle asmadmin 1097728 Apr 26 06: 52DB_03o81bso_1_1

-Rw-r ----- 1 oracle asmadmin 7168 Apr 26 06: 52DB_04o81bsq_1_1

[Oracle @ leonarding1 backup] $ cd control/

[Oracle @ leonarding1control] $ ll

Total 9600

-Rw-r ----- 1 oracle asmadmin 9830400 Apr 26 cf_c-1692458681-20130426-00

[Oracle @ leonarding1archdata] $ pwd

/U02/app/oracle/archdata

[Oracle @ leonarding1archdata] $ ll no more archived logs

Total 0

The new archive logs are backed up and deleted from the 76 and before the 75

SYS @ LEO1> archivelog list

Database logmode Archive Mode

Automaticarchival Enabled

Archivedestination/u02/app/oracle/archdata

Oldest online logsequence 74

Next log sequenceto archive 76

Current logsequence 76

At this point, our backup preparation has been completed. Take a break: take a bus to work.

8) SYS @ LEO1> selectstatus from v $ instance; check the database status

STATUS

------------

OPEN

LEO1 @ LEO1> showparameter control_files check the number of control files

NAME TYPE VALUE

-----------------------------------------------------------------------------

Control_files string/u02/app/oracle/oradata/LEO1/control01.ctl,

/U02/app/oracle/oradata/LEO1/control02.ctl

[Oracle @ leonarding1LEO1] $ ll there are also two operating systems with no problems

Total 2618136

-Rw-r ----- 1 oracle asmadmin 9748480 Apr 26 09: 01control01. ctl

-Rw-r ----- 1 oracle asmadmin 9748480 Apr 26 09: 01control02. ctl

In general, most of the control files are lost by mistake. Use the rm command to delete the control01.ctl file.

[Oracle @ leonarding1trace] $ pwd

/U02/app/oracle/diag/rdbms/leo1/LEO1/trace

[Oracle @ leonarding1 trace] $ tail-10falert_LEO1.log Real-time Monitoring Alert Log to see what changes have taken place

Fri Apr 2606: 50: 20 2013

Thread 1 advancedto log sequence 75 (LGWR switch)

Current log #3 seq #75 mem #0:/u02/app/oracle/oradata/LEO1/redo03.log

Archived Log entry2 added for thread 1 sequence 74 ID 0x64e13fb9 dest 1:

Fri Apr 2606: 52: 10 2013

ALTER SYSTEMARCHIVE LOG

Fri Apr 2606: 52: 10 2013

Thread 1 advancedto log sequence 76 (LGWR switch)

Current log #1 seq #76 mem #0:/u02/app/oracle/oradata/LEO1/redo01.log

Archived Log entry3 added for thread 1 sequence 75 ID 0x64e13fb9 dest 1:

[Oracle @ leonarding1LEO1] $ rm control01.ctl simulates the scenario of control01 file loss

LEO1 @ LEO1> createtablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10 mautoextend off;

Create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10 m autoextend off

L when we create a tablespace, an error is reported.

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl' control file loss

ORA-27041: unable to open file cannot open this file

Linux-x86_64Error: 2: No such file or directory can not find this file, good terror bless, hurry to see the alert Log

Additionalinformation: 3

Alert_LEO1.log log Content

Create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10 m autoextend off

ORA-210 signalledduring: create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10 m autoextend off...

Fri Apr 2609: 14: 15 2013

Errors in file/u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_m000_7975.trc:

ORA-00210: cannotopen the specified control file

ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unableto open file

Linux-x86_64Error: 2: No such file or directory

Additionalinformation: 3

Is it the same as the error message reported above? Because it is a ghost of our own, we understand what is going on. If it is in the production database, we should first check the log information for analysis, now let's fix it.-> start on