=======================================================
恢复表空间:
第一种方法:数据库在闪回模式下
1.首先要把数据库配置成闪回模式。
2.做一次完整的备份
3.drop tablespace
DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
4.shutdown immediate;
5.startup mount;
6.flashback database to timestamp to_timestamp('2011-12-18 14:43:29','yyyy-mm-dd hh24:mi:ss')
7.alter database rename file '/home/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to '/home/oracle/mustang/dbf/app01.dbf';
8.restor from coldbackup
9.recover database until time '2010-12-18 14:45:04';
10.alter database open resetlogs;
恢复表空间:
第一种方法:数据库在闪回模式下
1.首先要把数据库配置成闪回模式。
2.做一次完整的备份
3.drop tablespace
DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
4.shutdown immediate;
5.startup mount;
6.flashback database to timestamp to_timestamp('2011-12-18 14:43:29','yyyy-mm-dd hh24:mi:ss')
7.alter database rename file '/home/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to '/home/oracle/mustang/dbf/app01.dbf';
8.restor from coldbackup
9.recover database until time '2010-12-18 14:45:04';
10.alter database open resetlogs;
第二种方法:非闪回模式下:
1.关闭闪回模式
2.创建一个新的表空间来测试
3.关闭数据库
1)备份所有的数据文件
2)控制文件
4.删除表空
5.恢复备份文件和控制文件
6.recover database using backup controlfile until time '*****'
7.alter database open resetlogs;
具体操作记录如下:
SQL> conn / as sysdba
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
------------------
NO
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL>
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10M;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL>
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10M;
Tablespace created.
SQL> alter user dlt quota unlimited on test;
SQL> alter user dlt quota unlimited on test;
User altered.
SQL> conn dlt/dlt;
Connected.
SQL>
Connected.
SQL>
SQL> create table test (id number,name varchar2(100)) tablespace test;
Table created.
SQL> insert into test values(1,'denglt');
1 row created.
SQL> insert into test values(2,'denglt');
1 row created.
SQL> insert into test values(3,'denglt');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 denglt
2 denglt
3 denglt
---------- ----------------------------------------------------------------------------------------------------
1 denglt
2 denglt
3 denglt
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
--冷备份数据文件和控制文件
[oracle@orcl tmpsoft]$ mkdir coolbackup
[oracle@orcl tmpsoft]$ cd /u01/app/oracle/oradata/orcl
[oracle@orcl orcl]$ ls
control01.ctl control03.ctl redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf
control02.ctl redo01.log redo03.log system01.dbf test01.dbf users01.dbf
[oracle@orcl orcl]$ cp *.dbf /tmpsoft/coolbackup
[oracle@orcl orcl]$ cp *.ctl /tmpsoft/coolbackup
[oracle@orcl orcl]$
[oracle@orcl tmpsoft]$ mkdir coolbackup
[oracle@orcl tmpsoft]$ cd /u01/app/oracle/oradata/orcl
[oracle@orcl orcl]$ ls
control01.ctl control03.ctl redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf
control02.ctl redo01.log redo03.log system01.dbf test01.dbf users01.dbf
[oracle@orcl orcl]$ cp *.dbf /tmpsoft/coolbackup
[oracle@orcl orcl]$ cp *.ctl /tmpsoft/coolbackup
[oracle@orcl orcl]$
--启动数据库删除test表空间
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 243269632 bytes
Fixed Size 1266824 bytes
Variable Size 171969400 bytes
Database Buffers 67108864 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
Fixed Size 1266824 bytes
Variable Size 171969400 bytes
Database Buffers 67108864 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> conn dlt/dlt
SQL> select * from test;
SQL> select * from test;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 denglt
2 denglt
3 denglt
---------- ----------------------------------------------------------------------------------------------------
1 denglt
2 denglt
3 denglt
SQL> insert into test values(4,'denglt');
1 row created.
SQL> insert into test values(5,'denglt');
1 row created.
SQL> insert into test values(6,'denglt');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
Connected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-12-21 15:49:20
-------------------
2011-12-21 15:49:20
SQL> create table dlt.test2 tablespace test as select * from dlt.test;
Table created.
SQL> drop tablespace test INCLUDING CONTENTS;
Tablespace dropped.
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
--恢复备份的数据文件和控制文件
[oracle@orcl orcl]$ cp /tmpsoft/coolbackup/* ./
[oracle@orcl orcl]$ cp /tmpsoft/coolbackup/* ./
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 243269632 bytes
Fixed Size 1266824 bytes
Variable Size 155192184 bytes
Database Buffers 83886080 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL>
Fixed Size 1266824 bytes
Variable Size 155192184 bytes
Database Buffers 83886080 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/test01.dbf --可以看到
--------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/test01.dbf --可以看到
SQL> select * from v$recover_file;
no rows selected
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> recover database using backup controlfile until time '2011-12-21 15:49:20';
SQL> recover database using backup controlfile until time '2011-12-21 15:49:20';
--注意:一定要使用using backup controlfile子句,否侧无法恢复3后面的数据。
SQL> alter database open resetlogs;
SQL> alter database open resetlogs;
Database altered.
SQL> select * from dlt.test;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 denglt
2 denglt
3 denglt
4 denglt
5 denglt
6 denglt
---------- ----------------------------------------------------------------------------------------------------
1 denglt
2 denglt
3 denglt
4 denglt
5 denglt
6 denglt
6 rows selected.
恢复成功
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/195110/viewspace-713719/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/195110/viewspace-713719/