Mongolerman simulates data file loss and a simple restoration method

1 Oracle create a simple project environment 1) create a tablespace SQLgt; createtablespacedreamtry_tbsdatafile

1. create a simple project environment in Oracle 1) create a tablespace SQLgt; create tablespace dreamtry_tbs datafile

1. Create a simple project environment in Oracle
1) create a tablespace
SQL> create tablespace dreamtry_tbs datafile 'd: \ oracledate \ dreamtry_tbs01.dbf 'size 100 M;
The tablespace has been created.
2) create a user
SQL> create user dreamtry identified by dreamtry;
The user has been created.
3) Allocate table space to users
SQL> alter user dreamtry default tablespace dreamtry_tbs quota unlimited on dreamtry_tbs;
The user has changed.
4) cancel the write permission on all tables (this is not required. We recommend that you execute this operation)
SQL> alter user dreamtry default tablespace dreamtry_tbs quota unlimited on dreamtry_tbs;
The user has changed.
5) Assign basic permissions to users (with admin option can be added)
SQL> grant connect, resource to dreamtry;
Authorization successful.
6) Switch users
SQL> conn dreamtry/dreamtry;
Connected.
2. Simulate restoration of data file loss (provided that a backup has been performed and logs exist)
1) Back up data files
RMAN> backup datafile 'd: \ oracledate \ dreamtry_tbs01.dbf' format 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ dreamtry_tbs01.dbf ';
Start backup from month 1 to Month 12
Use the target database control file to replace the recovery directory
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 145 devtype = DISK
Channel ORA_DISK_1: Start all data file backup Sets
Channel ORA_DISK_1: Specifies the data file in the backup set.
Enter the data file fno = 00007 name = D: \ ORACLEDATE \ DREAMTRY_TBS01.DBF
Channel ORA_DISK_1: Starting segment 1 from March 1 to March 12
Channel ORA_DISK_1: Completed segment 1 from month 1 to Month 12
Segment handle = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ DREAMTRY_TBS01.DBF mark = TAG20120301T223142 comment = NONE
Channel ORA_DISK_1: Backup set completed. elapsed time: 00: 00: 03
Backup completed in month 1-3-12
2) Switch users' dreamtry to simulate Data Operations
SQL> conn dreamtry/dreamtry;
Connected.
SQL> create table test (id number (10), name varchar2 (20 ));
The table has been created.
SQL> insert into test (id, name) values (1, 'hangsan ');
One row has been created.
SQL> select * from test;
ID NAME
------------------------------
1 zhangsan
3) the simulation data file dreamtry_tbs01.dfb is lost.
Manually delete the OS file dreamtry_tbs01.dbf (move it elsewhere, Just in case)
SQL> startup
The ORACLE routine has been started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 251661188 bytes
Database Buffers 352321536 bytes
Redo Buffers 7135232 bytes
The database has been loaded.
ORA-01157: unable to identify/lock data file 7-see DBWR trace file
ORA-01110: data file 7: 'd: \ ORACLEDATE \ DREAMTRY_TBS01.DBF'
SQL> select status from v $ instance;
STATUS
------------
MOUNTED
The above database can only start the mount status.
4) use the backup data file for recovery (of course, it can be recovered without a backup in the case of logs)
RMAN> restore datafile 7;
Start restore from to 12
Use channel ORA_DISK_1
Channel ORA_DISK_1: Recovering data file backup set
Channel ORA_DISK_1: Specifies the data file to be recovered from the backup set.
Restoring data file 00007 to D: \ ORACLEDATE \ DREAMTRY_TBS01.DBF
Channel ORA_DISK_1: Reading backup segment D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ DREAMTRY_TBS01.DBF
Channel ORA_DISK_1: recovered backup segment 1
Segment handle = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ DREAMTRY_TBS01.DBF mark = TAG20120301T223142
Channel ORA_DISK_1: Recovery completed, time: 00:00:04
Restore completed from month 1 to Month 12
5) use logs to Restore database operations after backup
RMAN> recover datafile 7;
Start recover from month 1 to Month 12
Use channel ORA_DISK_1
Restoring media...
Media Recovery completed. Time: 00:00:03
The recover is completed from month 1 to Month 12.
6) Open the database
RMAN> alter database open;
Database opened
7) verify data
SQL> select * from dreamtry. test;
ID NAME
------------------------------
1 zhangsan
8) because it is a simulation, I still Delete the backup to avoid occupying space.
RMAN> list backup;
Backup set list
==============================
BS keyword Type LV Size Device type time completed
--------------------------------------------------------
3 Full 88.00 k disk 00:00:02-12
BP Keyword: 3 state: AVAILABLE compressed: NO mark: TAG20120301T223142
Segment name: D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ DREAMTRY_TBS01.DBF
List of data files in backup set 3
File LV type Ckp SCN Ckp time name
----------------------------------
7 Full 1068184 month-12 D: \ ORACLEDATE \ DREAMTRY_TBS01.DBF
RMAN> delete backupset 3;
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 145 devtype = DISK
Backup segment list
BP keyword BS keyword Pc # Cp # status device type segment name
----------------------------------------------------
3 3 1 1 available disk d: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ DREAMTRY_TBS01.DBF
Are you sure you want to delete the above objects (enter YES or NO )? YES
Deleted backup segment
Backup segment handle = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ DREAMTRY_TBS01.DBF recid = 3 stamp = 776817102
1. The object has been deleted.
RMAN> crosscheck backup;
Use channel ORA_DISK_1
RMAN> delete expired backup;
Use channel ORA_DISK_1
9) Conclusion: the log is the last line of defense, so it is best to back up the log first, it is best to back up the archive log, or just in case.

,