November 25 2013

oracle备份恢复简单练习1

如果是非关键性表空间损坏
解决方案1:
SQL> alter database datafile ‘/u01/app/oracle/oradata/orcl/TSPITR.dbf’ offline;

Database altered.

SQL> alter database open;

Database altered.
解决方案2:
rman target /
RMAN> restore datafile 6;
RMAN> recover datafile 6;
如果是undo表空间损坏。
SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> create undo tablespace undotbs02 datafile ‘/u01/app/oracle/oradata/orcl/undotbs02.dbf’ size 100m;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs02 scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             599788888 bytes
Database Buffers          234881024 bytes
Redo Buffers                2379776 bytes
Database mounted.
Database opened.
SQL>

flashback database to timestamp to_timestamp(‘2014-09-23 21:54:00′,’yyyy-mm-dd hh24:mi:ss’);
run{
set until time “to_date(‘2014-09-23 04:19:00′,’yyyy-mm-dd hh:mi:ss’)”;
restore database;
recover database;
}
SQL> alter database open RESETLOGS;
——————————–
RMAN> startup nomount

RMAN> restore controlfile from ‘/oracle/backup/control_after_rman.ctl’;

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> run {
2> set until time “to_date(‘2010-10-18 13:14:25′,’yyyy-mm-dd hh24:mi:ss’)”;
3> restore database;
4> recover database;
5> }
resetlogs打开,看来只要归档还在,利用备份的控制文件,来来回回就可以反复恢复了.
backup current controlfile;
2014/09/23 04:18:56

select oldest_flashback_scn,
to_char(oldest_flashback_time,’YYYY-MM-DD HH24:MI:SS’)
from v$flashback_database_log;
run{
set until time “to_date(‘2014-09-23 04:19:00′,’yyyy-mm-dd hh:mi:ss’)”;
restore database;
recover database;
}

但是对于关键性表空间 比如system sysaux 这样子如果没有备份的话 只能呵呵了。
删除了控件文件
方法一 如果有一份存在 那么cp 就可以
方法二 如果有备份存在 那么在rman里 restore controlfile from auto backup就可以  控制文件自动备份一定要打开
方法三 如果没有备份 那么只能create controlfile了
CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’  SIZE 50M
— STANDBY LOGFILE
DATAFILE
‘/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/example01.dbf’
CHARACTER SET ZHS16GBK
;

 
——————————————-
redo

SQL> SELECT group#,status FROM v$log;

GROUP# STATUS
———- —————-
1 INACTIVE
2 ACTIVE
3 CURRENT

SQL> insert into t values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values(11);

1 row created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction – commit or rollback first
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  817889280 bytes
Fixed Size                  2024272 bytes
Variable Size             268438704 bytes
Database Buffers          541065216 bytes
Redo Buffers                6361088 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘/u01/app/oracle/oradata/orcl/redo02.log’
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: ‘/u01/app/oracle/oradata/orcl/redo02.log’
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: ‘/u01/app/oracle/oradata/orcl/redo02.log’
归档模式下,对于未归档的日志文件组是不能删除的

SQL> alter database noarchivelog;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  817889280 bytes
Fixed Size                  2024272 bytes
Variable Size             268438704 bytes
Database Buffers          541065216 bytes
Redo Buffers                6361088 bytes
Database mounted.
SQL>  alter database drop logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

——————–或者
起到mount
在rman里 restore database;
在sql里  recover database until cancel;
cancel
alter database open resetlogs
—————————-丢数据了

如果是非归档的模式下:
alter database clear logfile group 2;



Copyright 2019. All rights reserved.

Posted 2013年11月25日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *