Oracle study summary 7

The main reason is to summarize the previously encountered Backup recovery. The content is summarized several times.

1. Add control file backup

Select * from V $ controlfile;
Add Control File
Alter system set control_files = 'd: \ demo1 \ control01.ctl ', 'c: \ demo \ control02.ctl' scope = spfile;

After shutown, use host copy before startup
2. Multiple redo logs
Col member format A30
Select group #, member from V $ logfile;
Add log members
Alter database add logfile Member
'Xxxxxxx' to group 1,
'Xxxxxxxx' to group 2,
'Xxxxx' to group 3;
3
Reduce the Undo time, start multiple slave processes, and set fast_start_parallel_rollback. If it is false, the slave process is not started,
If it is set to low, the slave process is 2 times the number of CPUs, and the high is 4 times
Alter system set fast_start_parallel_rollback = high;

4. Change the archive Mode
Check
Select log_mode from V $ database;
Shutdown immediate
Startup Mount
Alter database archivelog;
Alter database open;
5. Set the archiving location
Alter system set log_archive_dest_1 = 'location = c: \ demo \ archive mandatory ';
Alter system set log_archive_dest_2 = 'location = D: \ demo \ archive mandatory ';
Mandatory indicates that the file must be forcibly archived.
If the disk at the archive location is full, you can temporarily disable it.
Alter system set log_archive_dest_state_2 = defer;
After doing a good job,
Alter system set log_archive_dest_state_2 = Enable;
6. Control the number of archiving processes
2 archiving processes by default, changed
Alter system set log_archive_max_processes = 3;
7. If you store archived logs locally, you can
The first location is alter system set log_archive_dest = 'd: \ demo \ archive1 ';
The second location is alter system set log_archive_duplex_dest = 'xxxx ';
8. Display archive information
Archive log list;
Display archived log information
Col name for A40
Select name, sequence #, first_change # from V $ archived_log;

Display its location
Col dest_name for A20
Col destination for A20
Select dest_name, destination, status from V $ archive_dest;
Display information of all archiving processes
Select * from V $ archive_processes;
9 manually switch logs
Alter system switch logfile;

10 Data Backup
A consistent backup
When the database is closed, use host copy to copy data files and control files to other places.
B inconsistent backup
Backup in open state can only be in archivelog mode,
List data files
Select name from V $ datafile;
Alter database begin backup;
Use host to copy all data files
Alter Database Backup controlfile to 'd: \ backup \ demo. CTL'
Alter Database End backup;
Alter system archive log current;
11 tablespace backup
A offline backup
View the data files contained in the tablespace
Select file_name from dba_data_files where tablespace_name = 'users ';
Select tablespace_name from dba_data_files; (view the number of tablespaces)
Then alter tablespace XXX offline;
Use hostcopy again. If you back up the tablespace, copy all the files under it.
Alter tablespace XXX online;
B online backup
Similar to step 1, except that step 2 uses alter tablespace begin backup;
Alter tablespace end backup;
12. Back up archived logs
Displays archived logs from the past day
Select name from V $ archived_log where dest_id = 1 and first_time >=sysdate-1;
Use host copy
13. Backup spfile
Create pfile = 'd: \ backup \ demo. Par 'from spfile = 'xxxx. Or ';

14. Restore data files in the Mount status
When there are backup data files in other locations, the mout status
Host copy DBF of the original location DBF destination location (here is the previously backed up DBF)
Alter database rename file 'dbf' in the original location to 'dbf' in the destination location ';

The recover database will be used later and can only be run in the Mount state.
Startup force Mount
Recover Database
Restore the tablespace.
Recover tablespace users
Recover datafile
Recover datafile 'd: \ XXXX. DBF ';
View the data file to be restored
Col error for A20
Select File #, error, change # from V $ recover_file;
You can find out which file to restore.

15 recovery from system tablespace corruption
The archive mode is definitely needed. First, back up system01.dbf to another disk.
Shutdown immediate
Host del XXXX. DBF
Recovery Time
Startup Mount
Select File #, error, change # from V $ recover_file;
Find the file to be restored, for example, 1
Host move D: \ backup \ system01.dbf D: \ demo
Recover datafile 1
Alter database open
If the disk in the original system space is damaged, you need to specify its location in this way.
Host Copy D: \ backup \ system01.dbf E: \ demo
Alter database rename file 'd: \ demo \ system01.dbf 'to 'e: \ demo \ system01.dbf'

16 restore user tablespace
　　　　　
Shutdown immediate
Host del XXXX. DBF
Recovery Time
Startup Mount
Select File #, error, change # from V $ recover_file;
Find the file to be restored, for example, 4
Alter database datafile 4 offline;
Open the database again
Alter database open;
Host Copy D: \ backup \ users01.dbf E: \ demo \ users01.dbf
Recover datafile 4
Alter database datafile 4 online;
(If there are many data files in the tablespace, you can do this)
Alter tablespace users offline for recover;
Host Copy D: \ backup \ users01.dbf E: \ demo \ users01.dbf
Recover tablespace users;
Alter tablespace users online;

17. Restore unbacked data files in open and archive Mode
Create tablespace user01 datafile 'd: \ demo \ user01.dbf 'size 5 m;

Create Table test_3 (cola int) tablespace user01;

Insert into test_3 values (1 );

Commit;

Alter system switch logfile;
Simulated Deletion
Shutdown immediate;
Host del D: \ demo \ user01.dbf

Startup Mount
Alter database datafile 'd: \ demo \ user01.dbf' offline;
Alter database open;
Alter database create datafile 'd: \ demo \ user01.dbf ';
Recover datafile 'd: \ demo \ user01.dbf'
Alter database datafile 'd: \ demo \ user01.dbf' online;

18. Restore Control File
When a control file is corrupted
Alter system set control_files = 'backup \ control1.ctl 'scope = spfile
Startup force
19. restoration of damaged logs in a log Group
Alter system switch logfile;
Alter database drop logfile member 'corrupted XXX. log ';
Then add
Alter database add logfile member 'xxxx. log' to group 1;

20 all log members in the inactive log group are damaged.
A open status
Alter database clear Unarchived logfile group 1;
When B is not open
Alter database add logfile ('xxxx \ XX. log', 'xxxx \ XXX. log') Size 10 m;
Alter database drop logfile group 1;
Alter database open;

21. All members of the current active log group are damaged.
A: an error occurs when the database is closed.
Recover database until cancel
Alter database resetlogs;
B open state

After startup mount is required, use host copy to copy all the data files backed up. DBF back
Then use recover database until cancel
Alter database open resetlogs;