Oracle Tutorial: full recovery of user management

Adump is generally audit dump
Bdump is a background dump.
Udump is user dump.
Cdump is core dump.

Recovery steps
1. Restore data files
2. Apply redo
3. The database contains committed and uncommitted transactions.
4. Application UNDO
5. Recovered Database

Restore: Use the operating system command to restore the file, and use the SQL * PLUS recover command to restore the file.

Non-archive mode recovery
All datafile and control files must be restored. You can also restore the parameter files, redo log files, and password files.
Advantages: easy to operate, low error risk, and recovery time is the copy time.
Disadvantage: when data is lost, it must be manually re-applied. The whole database can only be restored to the last cold backup time point.

Disk damage and data recovery in Cold Standby linux
[Oracle @ ora10g ubackup] $ cp/u01/oradate/orcl/*. // use the operating system command to back up the file
Create a virtual disk sdb using a virtual machine
Fdisk sdb format Disk
Command (m for help): n adds a new partition
Command (m for help): p 1 1 1044 w // Add the primary partition operation code. p is the primary partition, 1 is the number of partitions, and 1 is the number of first cylinders, 1044: w Number of cylinders in the partition is the number of partitions written to the disk.
Mkfs. ext3 sdb1 // Add the ext3 File System to the partition
[Oracle @ ora10g oradata] $ mount/dev/sdb1 md // mount to the md folder
[Oracle @ ora10g oradata] $ chown-R oracle: oinstall md // grant permissions
[Oracle @ ora10g md] $ cp/u01/ubackup/*. // copy the backup to the md folder

SQL> create pfile from spfile // create pfile
Modify the control file path in pfile
SQL> create spfile from pfile;
SQL> startup mount
Modify the datafile directory in the control file.
SQL> alter database rename file '/u01/oradata/morewood/system01.dbf' to '/u01/oradata/md/system01.dbf ';
... (Omit other modification operations)
Modify the tempfile directory in the control file.
SQL> alter database rename file '/u01/oradata/morewood/temp01.dbf' to '/u01/oradata/md/temp01.dbf ';
Modify the redo log file directory in the control file.
SQL> alter database rename file '/u01/oradata/morewood/redo01.dbf' to '/u01/oradata/md/redo01.dbf ';
... Omit other log directory modifications

Automatic Recovery of redo log file (No redo log backup)
1. shutdown instance
2. Restore the datafile and Control File
3. Execute the recoverable recovery
4. open Database with resetlogs Option
Operation Code:
Startup mount
Recover database until cancel using backup controlfile; // simulate Incomplete recovery
Cancel
Alter database open resetlogs;

Restore Archive Mode
1. Full recovery
Make sure that the data file to be restored is offline.
Only the lost or damaged datafile is restored.
Do not restore other files
Restore datafile
Advantage: you only need to restore the lost files, restore all data to the error time, and restore the time to restore the lost files and apply all archived log files.
Disadvantage: all archived log files must be included.
V $ recover_file: the data file to be restored. This information is the information of the control file.
V $ recovery_log: archive log files required for recovery
Database MOUNT status
SQL> recover database or recover datafile '/oradata/orcl/xxwz_data.dbf'
Database in OPEN state
SQL> recover tablespace xxwz_data; or recover datafile '/oradata/orcl/xxwz_data.dbf'

1.1 Cold Standby (directly copy files to other disks) + hot standby (first use alter tablespace xxwz_data begin backup)
Restore without opening the database (the system tablespace and UNDO tablespace can be restored)
Shutdown abort; // when the database is opened, datafile is suddenly lost. Only shutdown abort can shut down the database.
Copy the cold backup datafile to the original data file directory;
Startup mount;
Select * from v $ recover_file; // view the data file to be restored
Set autorecovery off // Disable Automatic Recovery
SQL> recover database or recover datafile 1;

Recover a sudden loss of data when the database is opened (the database is not aware of the loss)
Tip: the database can use select * from v $ recover_file to find the file to be restored only after alter system checkpoint is suddenly lost.
Alter database datafile 1 offline;
Copy the backup file;
Recover datafile 1;
Alter database datafile 1 online;

The data file is lost when it is closed, but it must be restored when the database is opened (the database is aware of the loss)
Startup mount;
Alter database datafile 1 offline;
Alter database open;
Copy the backup file;
Recover datafile 1;
Alter database datafile 1 online;

1.2 data files are not backed up (they cannot be SYSTEM tablespace data files, and control files cannot be rebuilt)
Premise: all archived log files created from the data file are available. The control file contains the name of the lost file.
Alter database datafile 7 offline;
SQL> alter database create datafile 'd: \ oracle \ oradata \ xxwz_data.dbf 'or alter database create datafile 'd: \ oracle \ oradata \ xxwz_data.dbf' AS 'e: \ oracle \ oradata \ xxwz_data.dbf ';
Recovery datafile 7;
Alter database datafile 7 online;

If the checkpoint SCN of the control file is smaller than the SCN of the checkpoint of the data file, an old control file error is reported. On the contrary, media recovery is required.
Query the SCN of the checkpoint of the data file
Select file #, checkpoint_change # from v $ datafile_header;
Query the SCN of the checkpoint of the Control File
Select file #, checkpoint_change # from v $ datafile;

Control file recovery
1. Not all control files are lost
Create pfile from spfile;
Delete the path of the lost control file in pfile
Create spfile from pfile;
Or
Cp control02.ctl control01.ctl

2. All control files are lost.
Back up the control file as a trace script before it is lost
Alter database backup controlfile to trace as 'd: \ c1.trc ';
Cp c1.trc c1. SQL
Modify an SQL script
Execute an SQL script

Or
Back up the control file before the loss
Alter database backup controlfile to 'd: \ c1.trc ';
Then I create a new tablespace and insert data;
After loss
Copy the backup control file to the specified directory and rename it.
Cp d: \ c1.trc control01.ctl
Cp d: \ c1.trc control02.ctl
Cp d: \ c1.trc control03.ctl
Restore data with control files
Recover database using backup controlfile;
Data may be in one of the log files. Try it one by one.
'D: \ oracle \ oradata \ orcl \ redo01.log // specify to use the redo01 log file for data recovery
Because the backup control file does not record the path of the new data file, after the preceding command is executed, the database automatically specifies the path of the data file corresponding to the tablespace to other places, this will prompt you when executing the following command.
Recover database using backup controlfile;
In this case, we need to change the path automatically specified in the control file to the actual path of our data file.
Alter database rename file 'xxxx' to 'd: \ oracle \ oradata \ orcl \ xxwz_data1.dbf' // xxx is the path specified by the database, the following real data file path prompts when the first recover error is reported. It can also be changed from alter _ {sid }. log File.
Run the recovery command after the change.
Recover database using backup controlfile until cancel;
Then try redo files one by one. Enter
Alter database open resetlogs;

Resetlogs:
The resetlogs option must be used for Incomplete recovery or backup control files.
What resetlogs does:
1. archive the current redo log file (if they can be accessed), then clear the content of the redo log file, and reset the log sequence number to 1. (For example, the current log file sequence is 1000 and 1001, reset to 1 and 2)
2. If the redo log file does not exist, recreate it.
3. reinitialize the metadata of online redo logs and redo thread in the control file.
4. Update all current data files and online redo log files and all subsequent redo log files archived with a new resetlogs SCN and time stamp.

Read-Only tablespace recovery:
The trace script of the control file of the read-only tablespace does not load the tablespace during reconstruction. Instead, it is implemented through rename file and alter online during restoration. You can compare the trace script of the control file between read-only and common tablespace.

Incarnation
The archive log naming format % s is log sequence number, % t is thread number, and % r is incarnation.
Cross-resetlogs recovery through reset incarnation