54.RMAN执行恢复在企业中的应用
1.系统关键数据文件丢失后的处理方案
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 14:32:18 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 14:32:36> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.13
sys@ORCL10G 2023-03-06 14:32:54> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 14:33:38> alter system flush global context;
System altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 14:33:47> shutdown abort;
ORACLE instance shut down.
sys@ORCL10G 2023-03-06 14:33:57> startup mount;
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
sys@ORCL10G 2023-03-06 14:34:07> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 14:34:15 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744, not open)
RMAN> restore datafile 2;
Starting restore at 2023-03-06 14:34:48
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /oradata/orcl10g/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T111352_l0bpmjvm_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T111352_l0bpmjvm_.bkp tag=TAG20230306T111352
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2023-03-06 14:34:49
RMAN> recover database;
Starting recover at 2023-03-06 14:35:11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2023-03-06 14:35:11
RMAN> alter database open;
database opened
RMAN>
-------------------------------------------------------------------------------------------------------------------------------------------------
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 14:50:33 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> backup database;
Starting backup at 2023-03-06 14:50:39
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl10g/system01.dbf
input datafile fno=00003 name=/oradata/orcl10g/sysaux01.dbf
input datafile fno=00005 name=/oradata/orcl10g/example01.dbf
input datafile fno=00002 name=/oradata/orcl10g/undotbs01.dbf
input datafile fno=00004 name=/oradata/orcl10g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2023-03-06 14:50:39
channel ORA_DISK_1: finished piece 1 at 2023-03-06 14:50:54
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp tag=TAG20230306T145039 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2023-03-06 14:50:55
channel ORA_DISK_1: finished piece 1 at 2023-03-06 14:50:56
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_ncsnf_TAG20230306T145039_l0c3bhwj_.bkp tag=TAG20230306T145039 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2023-03-06 14:50:56
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 14:51:09 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 14:51:09> col file_name for a40;
sys@ORCL10G 2023-03-06 14:51:19> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ----------------------------------------
4 /oradata/orcl10g/users01.dbf
3 /oradata/orcl10g/sysaux01.dbf
2 /oradata/orcl10g/undotbs01.dbf
1 /oradata/orcl10g/system01.dbf
5 /oradata/orcl10g/example01.dbf
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 14:51:31> ho rm /oradata/orcl10g/undotbs01.dbf
sys@ORCL10G 2023-03-06 14:51:46> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 14:51:49 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 14:51:53> col file_name for a40;
sys@ORCL10G 2023-03-06 14:52:01> r
1* select file_id,file_name from dba_data_files
FILE_ID FILE_NAME
---------- ----------------------------------------
4 /oradata/orcl10g/users01.dbf
3 /oradata/orcl10g/sysaux01.dbf
2 /oradata/orcl10g/undotbs01.dbf
1 /oradata/orcl10g/system01.dbf
5 /oradata/orcl10g/example01.dbf
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 14:52:03> ho ls -lh /oradata/orcl10g/
total 1.4G
drwxr-xr-x 2 oracle oinstall 4.0K Mar 6 14:35 archivelog
-rw-r----- 1 oracle oinstall 7.1M Mar 6 14:52 control01.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 6 14:52 control02.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 6 14:52 control03.ctl
-rw-r----- 1 oracle oinstall 101M Mar 6 14:50 example01.dbf
-rw-r----- 1 oracle oinstall 51M Mar 6 14:35 redo01.log
-rw-r----- 1 oracle oinstall 51M Mar 6 14:35 redo02.log
-rw-r----- 1 oracle oinstall 51M Mar 6 14:52 redo03.log
-rw-r----- 1 oracle oinstall 251M Mar 6 14:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 661M Mar 6 14:50 system01.dbf
-rw-r----- 1 oracle oinstall 218M Mar 6 08:55 temp.dbf
-rw-r----- 1 oracle oinstall 5.1M Mar 6 14:50 users01.dbf
sys@ORCL10G 2023-03-06 14:52:13> create undo tablespace undotbs2 datafile '/oradata/orcl10g/undotbs2.dbf' size 1024m autoextend on maxsize 30720m;
Tablespace created.
Elapsed: 00:00:04.31
sys@ORCL10G 2023-03-06 14:53:23> alter system set undo_tablespace = undotbs2;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 14:53:45> alter database datafile 2 offline ;
Database altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 14:54:15> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 14:54:22 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> restore datafile 2;
Starting restore at 2023-03-06 14:54:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /oradata/orcl10g/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp tag=TAG20230306T145039
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2023-03-06 14:54:32
RMAN> recover datafile 2;
Starting recover at 2023-03-06 14:54:40
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 2023-03-06 14:54:42
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 14:54:48 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 14:54:48> alter database datafile 2 online ;
Database altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 14:55:08> alter system set undo_tablespace = undotbs1;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 14:55:13> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:00.03
sys@ORCL10G 2023-03-06 14:55:54> col file_name for a40;
sys@ORCL10G 2023-03-06 14:56:04> r
1* select file_id,file_name from dba_data_files
FILE_ID FILE_NAME
---------- ----------------------------------------
4 /oradata/orcl10g/users01.dbf
3 /oradata/orcl10g/sysaux01.dbf
2 /oradata/orcl10g/undotbs01.dbf
1 /oradata/orcl10g/system01.dbf
5 /oradata/orcl10g/example01.dbf
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 14:56:04> ho ls -lh /oradata/orcl10g/
total 1.5G
drwxr-xr-x 2 oracle oinstall 4.0K Mar 6 14:35 archivelog
-rw-r----- 1 oracle oinstall 7.1M Mar 6 14:56 control01.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 6 14:56 control02.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 6 14:56 control03.ctl
-rw-r----- 1 oracle oinstall 101M Mar 6 14:50 example01.dbf
-rw-r----- 1 oracle oinstall 51M Mar 6 14:35 redo01.log
-rw-r----- 1 oracle oinstall 51M Mar 6 14:35 redo02.log
-rw-r----- 1 oracle oinstall 51M Mar 6 14:56 redo03.log
-rw-r----- 1 oracle oinstall 251M Mar 6 14:56 sysaux01.dbf
-rw-r----- 1 oracle oinstall 661M Mar 6 14:56 system01.dbf
-rw-r----- 1 oracle oinstall 218M Mar 6 08:55 temp.dbf
-rw-r----- 1 oracle oinstall 36M Mar 6 14:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Mar 6 14:50 users01.dbf2.系统非关键数据文件丢失后的处理方案
[oracle@yuanzj.com:/home/oracle]$ ls -lh /oradata/orcl10g/
total 1.5G
drwxr-xr-x 2 oracle oinstall 4.0K Mar 6 14:35 archivelog
-rw-r----- 1 oracle oinstall 7.1M Mar 6 15:05 control01.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 6 15:05 control02.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 6 15:05 control03.ctl
-rw-r----- 1 oracle oinstall 101M Mar 6 14:50 example01.dbf
-rw-r----- 1 oracle oinstall 51M Mar 6 14:35 redo01.log
-rw-r----- 1 oracle oinstall 51M Mar 6 14:35 redo02.log
-rw-r----- 1 oracle oinstall 51M Mar 6 15:05 redo03.log
-rw-r----- 1 oracle oinstall 251M Mar 6 15:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 661M Mar 6 15:05 system01.dbf
-rw-r----- 1 oracle oinstall 218M Mar 6 08:55 temp.dbf
-rw-r----- 1 oracle oinstall 36M Mar 6 15:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Mar 6 14:50 users01.dbf
[oracle@yuanzj.com:/home/oracle]$ rm /oradata/orcl10g/example01.dbf
sys@ORCL10G 2023-03-06 15:06:28> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.09
sys@ORCL10G 2023-03-06 15:07:23> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 15:07:35> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/oradata/orcl10g/example01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 15:07:39> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 15:08:30 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> sql 'alter database datafile 5 offline';
using target database control file instead of recovery catalog
sql statement: alter database datafile 5 offline
RMAN> restore datafile 5;
Starting restore at 2023-03-06 15:09:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /oradata/orcl10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp tag=TAG20230306T145039
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2023-03-06 15:09:05
RMAN> recover datafile 5;
Starting recover at 2023-03-06 15:09:15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023-03-06 15:09:16
RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 15:09:28 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 15:09:28> select count(*) from hr.employees;
COUNT(*)
----------
107
Elapsed: 00:00:00.01迁移到新的路径
rman targer / cdmfile=/home/oracle/a.rman
3.执行不完全恢复
# set until scn <>
# set until time|timestamp <>
# set until logseq <>
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 20:16:51 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 7.08M DISK 00:00:02 2023-03-06 11:14:09
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20230306T111352
Piece Name: /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_ncsnf_TAG20230306T111352_l0bpn118_.bkp
Control File Included: Ckp SCN: 992365 Ckp time: 2023-03-06 11:14:07
SPFILE Included: Modification time: 2023-03-06 10:43:43
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 822.69M DISK 00:00:08 2023-03-06 14:50:47
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20230306T145039
Piece Name: /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1020458 2023-03-06 14:50:39 /oradata/orcl10g/system01.dbf
2 Full 1020458 2023-03-06 14:50:39 /oradata/orcl10g/undotbs01.dbf
3 Full 1020458 2023-03-06 14:50:39 /oradata/orcl10g/sysaux01.dbf
4 Full 1020458 2023-03-06 14:50:39 /oradata/orcl10g/users01.dbf
5 Full 1020458 2023-03-06 14:50:39 /oradata/orcl10g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4 Full 7.08M DISK 00:00:01 2023-03-06 14:50:55
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20230306T145039
Piece Name: /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_ncsnf_TAG20230306T145039_l0c3bhwj_.bkp
Control File Included: Ckp SCN: 1020466 Ckp time: 2023-03-06 14:50:54
SPFILE Included: Modification time: 2023-03-06 14:48:34
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 20:16:57 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 20:16:57> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY'
-------------------
2023-03-06 20:16:59
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 20:16:59> conn scott/tiger
Connected.
scott@ORCL10G 2023-03-06 20:17:21> drop table emp purge;
Table dropped.
Elapsed: 00:00:00.15
scott@ORCL10G 2023-03-06 20:17:38> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 20:17:43 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 20:17:43> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 20:17:54> desc dbms_logmnr
PROCEDURE ADD_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
OPTIONS BINARY_INTEGER IN DEFAULT
FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
PROCEDURE START_LOGMNR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
STARTSCN NUMBER IN DEFAULT
ENDSCN NUMBER IN DEFAULT
STARTTIME DATE IN DEFAULT
ENDTIME DATE IN DEFAULT
DICTFILENAME VARCHAR2 IN DEFAULT
OPTIONS BINARY_INTEGER IN DEFAULT
sys@ORCL10G 2023-03-06 20:19:31> select member from v$logfile;
MEMBER
---------------------------------------------------------------
/oradata/orcl10g/redo03.log
/oradata/orcl10g/redo02.log
/oradata/orcl10g/redo01.log
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 20:19:56> exec dbms_logmnr.ADD_LOGFILE('/oradata/orcl10g/redo03.log');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
sys@ORCL10G 2023-03-06 20:20:35> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
sys@ORCL10G 2023-03-06 20:21:47> desc v$logmnr_contents;
Name Null? Type
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCN NUMBER
CSCN NUMBER
TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
THREAD# NUMBER
LOG_ID NUMBER
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJD# NUMBER
SEG_OWNER VARCHAR2(32)
SEG_NAME VARCHAR2(256)
TABLE_NAME VARCHAR2(32)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(18)
SESSION# NUMBER
SERIAL# NUMBER
USERNAME VARCHAR2(30)
SESSION_INFO VARCHAR2(4000)
TX_NAME VARCHAR2(256)
ROLLBACK NUMBER
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SEQUENCE# NUMBER
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
REDO_VALUE NUMBER
UNDO_VALUE NUMBER
SQL_COLUMN_TYPE VARCHAR2(30)
SQL_COLUMN_NAME VARCHAR2(30)
REDO_LENGTH NUMBER
REDO_OFFSET NUMBER
UNDO_LENGTH NUMBER
UNDO_OFFSET NUMBER
DATA_OBJV# NUMBER
SAFE_RESUME_SCN NUMBER
XID RAW(8)
PXID RAW(8)
AUDIT_SESSIONID NUMBER
sys@ORCL10G 2023-03-06 20:23:29> select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') time,sql_redo from v$logmnr_contents where table_name = 'EMP';
SCN TIME SQL_REDO
---------- -------------------------------------------
1034304 2023-03-06 20:17:38 drop table emp purge;
Elapsed: 00:00:00.59
sys@ORCL10G 2023-03-06 20:26:04> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
[oracle@yuanzj.com:/home/oracle]$ cat inc.rman
shu immediate
startup mount
run {
set until scn 1034304;
restor database;
recover database;
alter database open resetlogs;
}
[oracle@yuanzj.com:/home/oracle]$ rman target / cmdfile=/home/oracle/inc.rman
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 21:15:25 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> shutdown immediate
2> startup mount
3> run {
4> set until scn 1034304;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }
9>
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
executing command: SET until clause
Starting restore at 2023-03-06 21:15:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/orcl10g/system01.dbf
restoring datafile 00002 to /oradata/orcl10g/undotbs01.dbf
restoring datafile 00003 to /oradata/orcl10g/sysaux01.dbf
restoring datafile 00004 to /oradata/orcl10g/users01.dbf
restoring datafile 00005 to /oradata/orcl10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T145039_l0c39zr4_.bkp tag=TAG20230306T145039
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2023-03-06 21:16:00
Starting recover at 2023-03-06 21:16:00
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023-03-06 21:16:01
database opened
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ scott
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 21:16:08 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
scott@ORCL10G 2023-03-06 21:16:08> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.004.在新的主机上还原恢复数据库
-----重点细节------
--1.备份文件不要有多余得备份集
--2.spfile里面对应得目录都需要手动建立出来
--3.归档日志一定要拷贝全
[oracle@yuanzj.com:/home/oracle]$ 10g
[oracle@yuanzj.com:/home/oracle]$ export ORACLE_SID=orcl10g
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 22:20:47 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/ora10g/product/10.2.0/db_1/dbs/initorcl10g.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2094736 bytes
Variable Size 67111280 bytes
Database Buffers 83886080 bytes
Redo Buffers 6291456 bytes
RMAN> restore spfile to '/home/oracle/spfile0306.ora' from '/backup/o1_mf_ncsnf_TAG20230306T212440_l0ctf0d7_.bkp';
Starting restore at 2023-03-06 22:21:05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /backup/o1_mf_ncsnf_TAG20230306T212440_l0ctf0d7_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 2023-03-06 22:21:07
RMAN> shutdown abort;
Oracle instance shut down
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ ls -lh
total 12K
drwxr-xr-x 3 oracle oinstall 4.0K Feb 12 22:04 oradiag_oracle
drwxr-xr-x 2 oracle oinstall 4.0K Feb 13 05:44 soft
-rw-r----- 1 oracle oinstall 3.5K Mar 6 22:21 spfile0306.ora
[oracle@yuanzj.com:/home/oracle]$ cp spfile0306.ora $ORACLE_HOME/dbs/spfileorcl10g.ora
[oracle@yuanzj.com:/home/oracle]$ strings spfile0306.ora | grep /u01
*.audit_file_dest='/u01/app/ora10g/admin/orcl10g/adump'
*.background_dump_dest='/u01/app/ora10g/admin/orcl10g/bdump'
*.core_dump_dest='/u01/app/ora10g/admin/orcl10g/cdump'
*.db_recovery_file_dest='/u01/app/ora10g/flash_recovery_area'
*.user_dump_dest='/u01/app/ora10g/admin/orcl10g/udump'
[oracle@yuanzj.com:/home/oracle]$ mkdir -p /u01/app/ora10g/admin/orcl10g/adump
[oracle@yuanzj.com:/home/oracle]$ mkdir -p /u01/app/ora10g/admin/orcl10g/bdump
[oracle@yuanzj.com:/home/oracle]$ mkdir -p /u01/app/ora10g/admin/orcl10g/cdump
[oracle@yuanzj.com:/home/oracle]$ mkdir -p /u01/app/ora10g/flash_recovery_area
[oracle@yuanzj.com:/home/oracle]$ mkdir -p /u01/app/ora10g/admin/orcl10g/udump
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 22:35:13 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
idle 2023-03-06 22:35:13> shutdown abort;
ORACLE instance shut down.
idle 2023-03-06 22:35:20> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 22:35:27 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
RMAN> restore controlfile from '/backup/o1_mf_ncsnf_TAG20230306T212440_l0ctf0d7_.bkp';
Starting restore at 2023-03-06 22:35:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata/orcl10g/control01.ctl
output filename=/oradata/orcl10g/control02.ctl
output filename=/oradata/orcl10g/control03.ctl
Finished restore at 2023-03-06 22:35:58
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/backup/o1_mf_nnndf_TAG20230306T212440_l0ctdr92_.bkp';
Starting implicit crosscheck backup at 2023-03-06 22:36:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 2023-03-06 22:36:50
Starting implicit crosscheck copy at 2023-03-06 22:36:50
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2023-03-06 22:36:50
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /backup/o1_mf_nnndf_TAG20230306T212440_l0ctdr92_.bkp
List of Files Unknown to the Database
=====================================
File Name: /backup/o1_mf_nnndf_TAG20230306T212440_l0ctdr92_.bkp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /backup/o1_mf_nnndf_TAG20230306T212440_l0ctdr92_.bkp
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5 Full 825.12M DISK 00:00:05 2023-03-06 21:24:45
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20230306T212440
Piece Name: /backup/o1_mf_nnndf_TAG20230306T212440_l0ctdr92_.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1034742 2023-03-06 21:24:40 /oradata/orcl10g/system01.dbf
2 Full 1034742 2023-03-06 21:24:40 /oradata/orcl10g/undotbs01.dbf
3 Full 1034742 2023-03-06 21:24:40 /oradata/orcl10g/sysaux01.dbf
4 Full 1034742 2023-03-06 21:24:40 /oradata/orcl10g/users01.dbf
5 Full 1034742 2023-03-06 21:24:40 /oradata/orcl10g/example01.dbf
RMAN> restore database;
Starting restore at 2023-03-06 22:37:20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/orcl10g/system01.dbf
restoring datafile 00002 to /oradata/orcl10g/undotbs01.dbf
restoring datafile 00003 to /oradata/orcl10g/sysaux01.dbf
restoring datafile 00004 to /oradata/orcl10g/users01.dbf
restoring datafile 00005 to /oradata/orcl10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /backup/o1_mf_nnndf_TAG20230306T212440_l0ctdr92_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/backup/o1_mf_nnndf_TAG20230306T212440_l0ctdr92_.bkp tag=TAG20230306T212440
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2023-03-06 22:37:28
RMAN> recover database;
Starting recover at 2023-03-06 22:37:58
using channel ORA_DISK_1
starting media recovery
unable to find archive log
archive log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/06/2023 22:38:00
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 1034742
RMAN> recover database until scn 1034742;
Starting recover at 2023-03-06 22:38:21
using channel ORA_DISK_1
starting media recovery
unable to find archive log
archive log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/06/2023 22:38:21
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 1034742
RMAN> alter database open resetlogs;
database opened
RMAN> 5.执行灾难恢复
参考第4步
6.非归档模式下数据库文件丢失后的处理方案
--1.使用rman备份去恢复
--2.使用os备份去恢复
----rman备份可以增量备份，需要在mount状态下
----system丢失，关闭数据库，
----还原备份文件，控制文件，数据文件，联机日志文件【没有不管】
----恢复数据库，使用noredo
----如果有连接日志备份需要恢复
----打开数据库--整个数据库恢复到备份时间点，备份后需要人为输入丢失数据
