oracle 查询被删除,ORACLE 11g TSPITR恢复被删除的表空间

最新推荐文章于 2022-04-08 17:37:06 发布
最新推荐文章于 2022-04-08 17:37:06 发布
阅读量153 收藏
点赞数
文章标签： oracle 查询被删除

在Oracle11g中可以使用TSPITR来对被删除的表空间执行表空间按时间点恢复，下面通过一个实例来演示这个功能。

1.创建测试表空间test

SQL> create tablespace test datafile '/u03/app/oracle/oradata/db/test01.dbf' size 50M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u03/app/oracle/oradata/db/system01.dbf

/u03/app/oracle/oradata/db/sysaux01.dbf

/u03/app/oracle/oradata/db/undotbs01.dbf

/u03/app/oracle/oradata/db/users01.dbf

/u03/app/oracle/oradata/db/example01.dbf

/u03/app/oracle/oradata/db/test01.dbf

6 rows selected.

2.创建测试用户test与测试表t1

SQL> create user test identified by "test" default tablespace test temporary tablespace temp;

User created.

SQL> create table test.t1 as select * from dba_objects;

Table created.

SQL> select count(*) from test.t1;

COUNT(*)

----------

86031

3.对整个数据库的执行完全备份

RMAN> backup as backupset database plus archivelog;

Starting backup at 2015-06-01 22:05:12

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=1 STAMP=880996327

input archived log thread=1 sequence=6 RECID=2 STAMP=880996438

input archived log thread=1 sequence=7 RECID=3 STAMP=881014383

input archived log thread=1 sequence=8 RECID=4 STAMP=881014612

input archived log thread=1 sequence=9 RECID=5 STAMP=881015165

input archived log thread=1 sequence=10 RECID=13 STAMP=881233508

input archived log thread=1 sequence=11 RECID=14 STAMP=881233508

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:14

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:21

piece handle=/u02/backup/0kq8ea8q_1_1 tag=TAG20150601T220514 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=12 STAMP=881233507

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:21

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:22

piece handle=/u02/backup/0lq8ea91_1_1 tag=TAG20150601T220514 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=12 RECID=15 STAMP=881233508

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:23

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:26

piece handle=/u02/backup/0mq8ea92_1_1 tag=TAG20150601T220514 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=2 RECID=21 STAMP=881233663

input archived log thread=1 sequence=3 RECID=22 STAMP=881233941

input archived log thread=1 sequence=4 RECID=23 STAMP=881234587

input archived log thread=1 sequence=5 RECID=24 STAMP=881235045

input archived log thread=1 sequence=6 RECID=25 STAMP=881235180

input archived log thread=1 sequence=7 RECID=26 STAMP=881272559

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:26

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:27

piece handle=/u02/backup/0nq8ea96_1_1 tag=TAG20150601T220514 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=13 RECID=16 STAMP=881233508

input archived log thread=1 sequence=14 RECID=17 STAMP=881233508

input archived log thread=1 sequence=15 RECID=18 STAMP=881233508

input archived log thread=1 sequence=16 RECID=19 STAMP=881233508

input archived log thread=1 sequence=17 RECID=20 STAMP=881233508

input archived log thread=1 sequence=18 RECID=11 STAMP=881232587

input archived log thread=1 sequence=19 RECID=9 STAMP=881232587

input archived log thread=1 sequence=20 RECID=10 STAMP=881232587

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:27

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:28

piece handle=/u02/backup/0oq8ea97_1_1 tag=TAG20150601T220514 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=8 RECID=27 STAMP=881273112

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:28

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:29

piece handle=/u02/backup/0pq8ea98_1_1 tag=TAG20150601T220514 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2015-06-01 22:05:29

Starting backup at 2015-06-01 22:05:29

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf

input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf

input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf

input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf

input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:31

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:16

piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:20

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:21

piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2015-06-01 22:09:21

Starting backup at 2015-06-01 22:09:21

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=9 RECID=28 STAMP=881273363

channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:27

channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:30

piece handle=/u02/backup/0sq8eagn_1_1 tag=TAG20150601T220926 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 2015-06-01 22:09:30

4.删除表空间test，并记录删除操作执行之前的系统SCN与时间

SQL> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;

TO_CHAR(SCN_TO_TIME CURRENT_SCN

------------------- -----------

2015-06-01 22:11:45 751203

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

SQL> host ls -lrt /u03/app/oracle/oradata/db/

total 1548000

-rw-r----- 1 oracle11 oinstall 30416896 Jun 1 22:01 temp01.dbf

-rw-r----- 1 oracle11 oinstall 52429312 Jun 1 22:05 redo02.log

-rw-r----- 1 oracle11 oinstall 52429312 Jun 1 22:09 redo03.log

-rw-r----- 1 oracle11 oinstall 5251072 Jun 1 22:14 users01.dbf

-rw-r----- 1 oracle11 oinstall 775954432 Jun 1 22:14 system01.dbf

-rw-r----- 1 oracle11 oinstall 73408512 Jun 1 22:14 undotbs01.dbf

-rw-r----- 1 oracle11 oinstall 545267712 Jun 1 22:14 sysaux01.dbf

-rw-r----- 1 oracle11 oinstall 52429312 Jun 1 22:14 redo01.log

-rw-r----- 1 oracle11 oinstall 9748480 Jun 1 22:14 control01.ctl

从上面的查询可以看到表空间test已经被删除了。

5.现在执行TSPITR将表空间test恢复到被删除之前的时间点

RMAN> recover tablespace "TEST" until time '2015-06-01 22:11:45' auxiliary destination '/u02/auxiliary';

Starting recover at 2015-06-01 22:22:25

starting full resync of recovery catalog

full resync complete

using channel ORA_DISK_1

Creating automatic instance, with SID='jAvb'

initialization parameters used for automatic instance:

db_name=DB

db_unique_name=jAvb_tspitr_DB

compatible=11.2.0.4.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

db_create_file_dest=/u02/auxiliary

log_archive_dest_1='location=/u02/auxiliary'

#No auxiliary parameter file used

starting up automatic instance DB

Oracle instance started

Total System Global Area 1071333376 bytes

Fixed Size 1369420 bytes

Variable Size 281021108 bytes

Database Buffers 784334848 bytes

Redo Buffers 4608000 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until time "2015-06-01 22:11:45";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

# resync catalog

resync catalog;

}

executing Memory Script

executing command: SET until clause

Starting restore at 2015-06-01 22:22:40

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0rq8eagd_1_1

channel ORA_AUX_DISK_1: piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl

Finished restore at 2015-06-01 22:22:42

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog

full resync complete

contents of Memory Script:

{

# set requested point in time

set until time "2015-06-01 22:11:45";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 5 to

"/u03/app/oracle/oradata/db/test01.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 2, 5;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u02/auxiliary/DB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-06-01 22:22:51

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u02/auxiliary/DB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/auxiliary/DB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u02/auxiliary/DB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/db/test01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0qq8ea9b_1_1

channel ORA_AUX_DISK_1: piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15

Finished restore at 2015-06-01 22:25:07

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "2015-06-01 22:11:45";

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 5 online";

# recover and open resetlogs

recover clone database tablespace "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 5 online

Starting recover at 2015-06-01 22:25:08

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /u03/archivelog/1_9_881232587.dbf

archived log for thread 1 with sequence 10 is already on disk as file /u03/archivelog/1_10_881232587.dbf

archived log file name=/u03/archivelog/1_9_881232587.dbf thread=1 sequence=9

archived log file name=/u03/archivelog/1_10_881232587.dbf thread=1 sequence=10

media recovery complete, elapsed time: 00:00:16

Finished recover at 2015-06-01 22:25:25

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace "TEST" read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u02/auxiliary''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u02/auxiliary''";

}

executing Memory Script

sql statement: alter tablespace "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_jAvb":

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table "SYS"."TSPITR_EXP_jAvb" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_jAvb is:

EXPDP> /u02/auxiliary/tspitr_jAvb_29236.dmp

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace TEST:

EXPDP> /u03/app/oracle/oradata/db/test01.dbf

EXPDP> Job "SYS"."TSPITR_EXP_jAvb" successfully completed at Mon Jun 1 22:27:28 2015 elapsed 0 00:01:12

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

Performing import of metadata...

IMPDP> Master table "SYS"."TSPITR_IMP_jAvb" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_jAvb":

IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

IMPDP> Job "SYS"."TSPITR_IMP_jAvb" successfully completed at Mon Jun 1 22:28:23 2015 elapsed 0 00:00:14

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace "TEST" read write';

sql 'alter tablespace "TEST" offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

# resync catalog

resync catalog;

}

executing Memory Script

sql statement: alter tablespace "TEST" read write

sql statement: alter tablespace "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog

full resync complete

Removing automatic instance

Automatic instance removed

auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_temp_bprtv5bo_.tmp deleted

auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_3_bprttwvr_.log deleted

auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_2_bprttt0k_.log deleted

auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_1_bprttp4g_.log deleted

auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf deleted

auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf deleted

auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf deleted

auxiliary instance file /u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl deleted

Finished recover at 2015-06-01 22:28:45

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

TEST OFFLINE

6 rows selected.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u03/app/oracle/oradata/db/system01.dbf

/u03/app/oracle/oradata/db/sysaux01.dbf

/u03/app/oracle/oradata/db/undotbs01.dbf

/u03/app/oracle/oradata/db/users01.dbf

/u03/app/oracle/oradata/db/test01.dbf

SQL> host ls -lrt /u03/app/oracle/oradata/db/

total 1599328

-rw-r----- 1 oracle11 oinstall 52429312 Jun 1 22:09 redo03.log

-rw-r----- 1 oracle11 oinstall 5251072 Jun 1 22:22 users01.dbf

-rw-r----- 1 oracle11 oinstall 52429312 Jun 1 22:22 redo01.log

-rw-r----- 1 oracle11 oinstall 30416896 Jun 1 22:28 temp01.dbf

-rw-r----- 1 oracle11 oinstall 52436992 Jun 1 22:28 test01.dbf

-rw-r----- 1 oracle11 oinstall 775954432 Jun 1 22:28 system01.dbf

-rw-r----- 1 oracle11 oinstall 73408512 Jun 1 22:28 undotbs01.dbf

-rw-r----- 1 oracle11 oinstall 545267712 Jun 1 22:28 sysaux01.dbf

-rw-r----- 1 oracle11 oinstall 52429312 Jun 1 22:29 redo02.log

-rw-r----- 1 oracle11 oinstall 9814016 Jun 1 22:30 control01.ctl

从上面的查询结果可以看到表空间test已经恢复，但现在test表空间是脱机状态。

6.将表空间test联机，并查询表test.t1来验证恢复是否真正成功.

RMAN> sql 'alter tablespace test online';

sql statement: alter tablespace test online

starting full resync of recovery catalog

full resync complete

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

TEST ONLINE

6 rows selected.

SQL> select count(*) from test.t1;

COUNT(*)

----------

86031

从上面的查询结果可以看到被删除的表空间test已经恢复到被删除之前的时间点，表test.t1的记录已经恢复回来。

Shsvs
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle误删除表空间后恢复
03-04
当误操作将表空间删除怎样恢复，本文简述了操作方法
Oracle 表空间时点恢复(TSPITR)
乐沙弥的世界
04-25 5243
表空间时点恢复，是Oracle在基于冷备，热备恢复以外的一种以表空间为粒度的，不完全恢复的形式来将表空间恢复到过去某个特定的时间点的一种恢复方式。它整合了RMAN以及DataPump这2个备份恢复工具来实现时点恢复。那它具体的过程和逻辑是怎样的？下文是其具体的描述。
oracle11误删了控制文件,Oracle 11g 表空间的误删除, 当前的控制文件不识别该表空间...
weixin_42493325的博客
04-05 134
--做3次日志切换--查看归档日志信息,注意resetlogs前后,incarnation的变化sys@TESTDB11>selectsequence#,namefromv$archived_log;SEQUENCE# NAME---------- ------------------------------93/archive1/1_93_813665348.dbf93/archive2/...
oracle rman 11g 恢复,Oracle 11g RMAN恢复-用户误删除表空间
weixin_42510182的博客
04-03 205
--0.场景模拟--0.1查看当前的备份，确定有控制文件和初始化参数文件的备份RMAN>list backup;usingtarget database controlfileinsteadofrecoverycatalogListofBackupSets===================BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime...
ORACLE 11g TSPITR恢复被删除的表空间
06-01 85
在Oracle11g中可以使用TSPITR来对被删除的表空间执行表空间按时间点恢复，下面通过一个实例来演示这个功能。 1.创建测试表空间test SQL> create tablespace test dat...
如何正确的删除Oracle表空间数据文件
06-12
本文将详细介绍如何正确地删除Oracle表空间数据文件，探讨OFFLINE和OFFLINE DROP的区别，以及在操作系统层面删除数据文件后的恢复策略。 首先，我们来看如何使用`DROP DATAFILE`命令删除数据文件。这个命令允许管理...
查询Oracle数据库表空间和数据文件方法
03-03
下面以oracle9i为例，详细介绍查询Oracle数据库表空间信息和数据文件信息的方法。一、查看Oracle数据库中表空间信息的方法1、查看Oracle数据库中表空间信息的工具方法：使用oracleenterprisemanagerconsole工具，这...
Linux下Oracle删除用户和表空间的方法
09-10
在Linux环境下管理Oracle数据库时，有时需要删除不再使用的用户及其关联的表空间。这通常涉及到数据库的安全性和资源优化。本文将详细介绍如何在Linux下的Oracle环境中删除用户和表空间，以及处理可能遇到的问题。 ...
《Oracle Database 11g RMAN备份与恢复》PDF版本下载.txt
07-17
《Oracle Database 11g RMAN备份与恢复》PDF版本下载
oracle11g 重装操作系统后，如何利用原有oracle表空间文件还原数据库
weixin_30505485的博客
01-04 735
最近由于系统重装，在还原dmp备份文件时，由于数据原因（用exp命令导出时表没有导出全部），导致系统不能正常运行。根据网上的信息和个人实际情况，做个记录，便于后用。 oracle 导出空表方法： 1. 执行下面语句： select table_name from user_tables where NUM_ROWS=0; 2.假设我们这里有空表TBL_1，TBL_2，TBL_3，...
Oracle 11g的Temp表空间怎么恢复？
jose_chen_net的博客
02-10 253
答案是系统会自动重建； 实验如下： 我们先查看Temp表空间对应的数据文件 SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files; FILE_NAME TABLESPACE_NAME ---------------------------------------- ----------------------------...
创建表空间，查询表空间，删除表空间（oracle)
qq_43423320的博客
07-27 173
创建，查询，删除 直接上代码，带解析。 创建 create tablespace textdemo--名字 datafile 'd:\data\textdemo01.dba'--表空间物理位置，（我自己实验的时候是只能放在一个文件夹下面，多层文件夹会出错） size 100m--初始大小 autoextend on next 10m--每次增长10m maxsize 200m--最大200m permanent--永久的 extent management local;--本地的 查询（简单的） sele
oracle备份与恢复11g,Oracle 11gR2中针对备份恢复功能的增强
weixin_35068573的博客
04-02 237
原标题：Oracle 11gR2中针对备份恢复功能的增强1.主要内容：(1)描述11g中RMAN的新特性;(2)duplicate数据库可以直接通过网络传输,不需要有备份;(3)可以把大文件分段备份;(4)创建长期保存的归档备份;(5)管理recovery catalog;(6)描述virtual private catalogs的使用;2.使用SET NEWNAME子句：(1)新增加的SET N...
oracle11g备份出错,备份恢复实验5-oracle11g自动修复
weixin_30074329的博客
04-02 105
SYS@ora11g>! rm/u01/oracle/oradata/ora11g/users01.dbfSYS@ora11g>! rm/u01/oracle/oradata/ora11g/users01.dbfSYS@ora11g>create table scott.test_emp as select * fromscott.emp;create table scott.t...
【Oracle】oracle用rman异机恢复部分表空间方案
Tinko123的博客
06-14 1820
RMAN异机恢复部分表空间 RMAN单独恢复表空间(异机)并不是简单的restore tablespace tbs;recover tablespace tbs; 会报错如下： RMAN-03002: failure of switch command at 11/12/2014 10:36:09 RMAN-20230: datafile copy not found in the repository RMAN-06015: error while looking up datafile copy nam
oracle误删除空置文件,oracle数据文件被误删除后的灾难处理方法
weixin_35025136的博客
04-05 291
oracle数据文件被误删除后的灾难处理方法key：oracle数据文件(datafile)被误删除后没有恢复的办法,只能把该数据文件offline后drop掉,并删除整个表空间。steps：当Oracle数据文件被误删除之后的处理办法！！1、关闭数据库：sqlplus "/as sysdba";sqlplus>shutdown abort;2、装载数据库sqlplus>startup...
Oracle误删除dbf文件和表空间恢复
Dreams_KS的博客
04-08 4885
Oracle物理删除dbf文件和表空间恢复 参考： https://www.iteye.com/blog/java-mans-1642954 https://blog.csdn.net/qq_42774325/article/details/82353266 https://www.cnblogs.com/l10n/p/9406052.html 今天在dmp数据时入错了；想着把表空间文件直接删了，重新入；这想当然的操作，导致后面浪费了时间； 题目说恢复并非指“恢复数据”，确切来说指的是“修复”或“复原”
oracle表空间还原（数据库还原）
cymyell的专栏
07-14 4106
1、创建临时表：create temporary tablespace mydb_temptempfile F:/oracle/product/10.2.0/oradata/mydb/mydb_temp01.dbfsize 32mautoextend onnext 32m maxsize 2048mextent management local;路径：F:/ora
Oracle RMAN TSPITR：表空间时间点恢复与Logminer应用
在Oracle数据库管理中，TSPITR是一种恢复策略，它允许管理员恢复单个表空间到某个特定的时间点，而不会影响整个数据库的状态。这在处理表空间数据损坏或误操作时非常有用。RMAN是Oracle提供的一种强大工具，用于备份...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值