Oracle Advanced Training Course 3rd learn the pages in the notes of the notebook and Oracle9i Database Management Basics II ed 1.1 vol.1.pdf correspondence guide: http://blog.csdn.net/magus_yang/archive/2006/10/10/1328283.aspx Author: jackyang (JackYang.sh@gmail.com) Date: 2006-10-10 this course contains multiple labs and has not yet passed the computer verification. P295 user-managed backup RMAN recovery Manager Backup recovery methods: 1. User-managed Backup recovery can describe the principles of backup and recovery, and cannot implement Incremental backup. 2. RMAN supports Incremental backup. P297 terminology 1. Overall Database Backup (overall backup): backs up all data files and control files in the database. The overall backup can be performed whether the database is enabled or disabled. 2. Partial backup: only part of the data file, part of the control file, or part of the tablespace are backed up. 3. Consistent backup: The overall backup after the database is safely closed (disabled using the normal, immediate, or transactional options.
You do not need to restore it after restoration.
Features: All data files are the same as the SCN of the control file.
Consistent backup is useful in both archive and non-archive modes. 4. Inconsistent backup. If the database is opened and operable, the data file header (SCN) is inconsistent with the control file unless the database is opened in read-only mode. If you use the abort option to close the database, such inconsistency will always exist. Database Backup in these two States is called inconsistent backup.
After restoration, the database must be restored to a consistent state.
Features: the SCN of all control files and data files is inconsistent.
In archive mode, inconsistent backups are useful.
In non-archive mode, inconsistent backup is useless and cannot be used for restoration. P299 user-managed backup and recovery the user's recovery should use the OS command to use rman, regardless of the backup and recovery use the Command provided by RMAN. P300obtain the database file information to back up each database. query the following view to find out where the data file and control file are: V $ datafilev $ controlfilev $ logfiledba_data_files p303 the database works in non-archive mode, backup after database security is disabled (except shutdown abort) is useful. Databases work in archive mode, and the backup made when the database is turned on or off is useful. When the p304 database is opened, it cannot obtain a consistent backup (also known as a consistent overall database backup or a backup of a closed database ). Only when the database is closed. It is best to back up the parameter files and password files. Advantages of P305 consistent overall database backup: 1. concept is easy to understand; 2. easy to execute; 3. almost no interactive operation is required; p306 consistency overall database backup disadvantages: 1. after the database is damaged, it can only be restored to the status of the last fully consistent overall database backup. The p307 consistent overall database backup method backup files include: data files, control files, redo log files, password files, and parameter files. P309 the backup conditions for an Open Database: 1. be sure to back up data in archive mode. 2. all archived and redone log files must exist. online redo log files cannot be backed up. Note: Do not back up online redo log files. Online redo log files cannot be backed up. The advantages of an Open Database Backup for p310: 1. maintain high Database Availability; 2. backup can be performed at the tablespace level or data file level; 3. business activities are not interrupted. 4. data can be backed up to the time when a fault occurs without losing any data. In non-archive mode, all files must be backed up. In archive mode, you can back up a tablespace or data file; two Backup commands for online tablespace backup in p313: 1. SQL> alter tablespace users begin backup; enables the users of the tablespace to enter the backup state. All modifications to this tablespace do not go to the data file, but to the redo log file. It is also the entry of a block.
Therefore, the backup should be completed as soon as possible. Otherwise, the data in the redo log will grow rapidly.
In normal times, data files are written into one block and one block. retransmission of log files is a write operation. 2. SQL>! CP /... /users01.dbf/backup/users01.dbf Oracle 9.2 later than alter database begin backup; command p314 to end online tablespace backup SQL> alter tablespace users end backup; p315 uses the following dynamic view to query the backup status information: V $ backup check whether the data file is in the backup status v $ datafile_header p318 end online backup command query v $ backup to check the backup status Oracle9i using the following command: SQL> alter Database End backup;
Lab 1:Objective: To back up a consistent overall database in the off state. 1. start the oracleservicedb0 service; 2. d:/lab10g> set oracle_sid = db0 3. d:/lab10g> sqlplus/nolog 4. SQL> connect sys/ora123 as sysdba 5. SQL> startup 6. SQL> archive log list: Check whether the database is in archive mode. The Archive mode is enabled by default. 7. SQL> column name format A35 8. SQL> column member format A35 9. SQL> select name from V $ controlfile; 10. SQL> select name from V $ datafile; 11. SQL> select name from V $ tempfile; 12. SQL> select name from V $ logfile; execute these select queries to check where the file is located and ensure that no backup error occurs. 13. SQL> shutdown immediate 14. SQL> host copy E:/ora01g/oradata/db0 /*. dbf e:/backup/db0/cold backup in the closed state is called cold backup 15. SQL> host copy E:/ora01g/oradata/db0 /*. ctl e:/backup/db0/cold 16. SQL> host copy E:/ora01g/oradata/db0 /*. log E:/backup/db0/cold
Lab 2:Objective: To back up a database in archive mode. 1. SQL> startup mount 2. SQL> archive log list 3. SQL> alter database archivelog; enter Archive mode; 4. SQL> alter database open; database enters open state from Mount state 5. SQL> alter system achive log current; manually archive, generate archive file under db0/arc path. 6. SQL> alter database begin backup; 7. SQL> select * from V $ backup; check whether the data file is in the backup status. 8. SQL> select name. File #. status from V $ datafile; the numbers 1, 2, and 3 are different data files. 9. SQL> host copy E:/ora01g/oradata/db0 /*. dbf e:/backup/db0/hot 10. SQL> alter Database End backup; 11. SQL> select * from V $ backup; 12. SQL> alter system archive log current; 13. SQL> alter Database Backup controlfile to 'e:/backup/db0/hot/con1.vtl 'reuse; back up the control file to a binary file. Note: after each backup is completed, a forced archiving should be performed immediately. Archive is required for the next Backup recovery. You can also back up the control file to a script file. In/db0/udump/db0_ora_3680.trc, the create controlfile statement can be used to recreate the control file. The two backup methods of controlfile are very different. 14. SQL> alter Database Backup controlfile to trace; Note: Do not back up (online redo log file) redo log file P320 read-only tablespace backup the SCN of the read-only tablespace remains unchanged. Read-Only tablespace does not participate in collective backup. When the tablespace enters the read-only status, a backup is immediately performed for the read-only tablespace. When the tablespace is in the read-only status, no backup is performed. Once the tablespace returns to the read/write status, it is re-added to the collective backup; the p321 control file also needs to be backed up. The control file is large but small. If the data structure of the database is changed, the control file is immediately backed up. The control file reflects the latest database changes. A physical or logical change in the database may change the data structure of the database. Such as adding tablespaces, adding data files, moving data files, and creating tablespaces. P322 record and non-record option comparison record, logging mode, default non-record, nologging mode, fast, redo log only records the minimum amount of content, suitable for writing a large amount of data. The command for manually backing up the control file to create a binary image in p323 is as follows: SQL> alter Database Backup controlfile to 'control1. the command for creating a text tracking file (script file) is as follows: SQL> alter Database Backup controlfile to trace; p324 changes the database configuration and leads to the command list for changing the control file. After executing any command in the list, you must back up the control file. P325 backup initialization parameter file p326 use the dbverify utility to verify the two functions of the backup dbverify utility: 1. ensure that the backup database (or data files) is valid before restoration; 2. the p327deverify command is used to check whether the backup is valid or not before the backup to determine whether the backup file is damaged. The following page numbers correspond to Oracle9i Database Management Basics II ed 1.1 vol.2.pdf guide: http://blog.csdn.net/magus_yang/archive/2006/10/10/1328283.aspx P17 user-managed full recovery backup is designed to restore P19 medium recovery for major recovery, files are damaged due to disk damage or misoperations. Media recovery is divided into two steps. 1. restore the original backup file and copy it back to the original location to replace the lost or damaged file. 2. File recovery uses online redo log files and archived redo log files to restore data from the backup time to the current time. P20 recovery steps: 1. restore 2. recovery is divided into two steps. After rollback and rollback, data files may contain uncommitted transactions; the rollback operation discards such uncommitted transactions, which is equivalent to Step 1 in the figure. After the two steps, a large amount of submitted data does not contain uncommitted data. Run the operating system command to restore the file. P22 recovery has two modes: archivelog mode (archive mode) and noarchivelog mode (non-archive mode). It is easiest to restore all data files and control files in non-archive mode. If only one data file is corrupted, all data files and control files must be copied back to maintain consistency. Disadvantages: if many files are copied, data will be lost. Advantages: simple operation and low risk; whether to use non-archive mode should be determined by the boss. If the data cannot be lost at all, this mode is not available unless you manually insert data to complete. In non-archive mode, recovery is simple in two cases: 1. Restore a backup that contains online redo log files; 2. Restore a backup that does not contain online redo log files;
Lab 3:In noarchivelog mode (non-archive mode), follow these steps to back up online redo log files: 1. SQL> shutdown immediate; 2. SQL> host copy E:/backup/db0/cold/* E:/ora01g/oradata/db0 online redo log files are also copied back to simulate errors. You can delete e first: /ora01g/oradata/db0/all files in the path. 3. SQL> connect sys/ora123 as sysdba 4. SQL> startup
Experiment 4:In noarchivelog mode (non-archive mode), the backup does not include the restoration steps for online redo log files: 1. SQL> shutdown immediate; 2. copy files not starting with redo in the cold directory back to E:/ora01g/oradata/db0 3. SQL> connect sys/ora123 as sysdba 4. SQL> startup mount can only be mounted and cannot be open, because there is no online redo log file (redologfile) 5. SQL> recover database until cancel using backup controlfile; cancel is issued immediately during database recovery. There is no archived log file, not a real recovery, but a false recovery. Redo log file is rebuilt by simulating a recovery operation. 6. SQL> alter database open resetlogs; the redo log file is generated under oradata/db0/path. The resetlog causes the database to generate a replacement, the sequence number is reset to 1, and the redo log file starts from the blank. After using backup controlfile, you must resetlogs. The two match. In the P2 archive mode, only RMAN can perform Incremental Backup not only completely, but also not completely or completely, not completely, rather than the concept of space, but not completely the concept of time. In a previous version, you must resetlog, clears the sequence number of the redo log file to 0. P28 restoration control files and redo log files cannot be completely restored, but cannot be completely recovered. P29 has two archiving targets because of its disadvantages, so ensure that no archive file is missing. P30 the second view should contain all the files of 3rd views. P32recover tablespace is different from recover datafile because one tablespace contains multiple data files online backup and recovery advantages: one data file in the tablespace is lost, and another data file in the same tablespace can still be accessed. P33oracle 9i requires automatic re-upload of log files.
Lab 5:Objective: To perform full recovery in archive mode. Data Files that can be offline are lost. 1. SQL> select name from V $ datafile; 2. SQL> select name from V $ tablespace; one data1 contains two data files, one data1.dbf and the other data2.dbf 3. SQL> select * from Dept; 4. SQL> select * From dept2; 5. SQL> SELECT FILE #. name from V $ datafile; check the data file number 6. SQL> alter database datafile 5 offline; offline data files with file number 5. SQL> select * From dept2; in this case, you cannot access the dept2 table to delete the data2.dbf file in the oradata/db1/path to simulate a fault. Because the database is in archive mode, only one file can be recovered. 8. SQL> DESC v $ recover_file; 9. SQL> select * from V $ recover_file; check which files need to be restored and the offline files need to be restored. 10. SQL> host copy E:/backup/db1/hot/data2.dbf E:/ora01g/oradata/db1 copy the data file back. This is a restoration operation 11. SQL> select * from V $ recover_file; error is absent, but the status of the data file is still offline 12. SQL> select * from Dept; 13. SQL> select * From dept2; Table dept2 is still inaccessible 14. SQL> recover datafile 5; 15. SQL> alter database datafile 5 online; 16. SQL> select * From dept2; Table dept2 can be accessed. Experiment Summary: in this way, the system tablespace cannot be restored. A data file in the system tablespace is broken and the database cannot be started. You need to shut down the database and restore it again. The control file and online redo log files cannot be restored. Back up and archive after each recovery operation.
Lab 6:Objective: to restore the system tablespace failure in archive mode (Case 2. 1. SQL> shutdown immediate; 2. Delete system01.dbf to simulate system tablespace faults. 3. Copy the backed up system01.dbf file back. 4. SQL> startup mount 5. SQL> recover database 6. SQL> alter database open; 7. SQL> select * From dept2; Table dept2 can be accessed
