Daily Archives: 2012 年 04 月 25 日

恢复备份控制文件避免resetlogs方式打开数据库

Posted on by

在很多时候,我们需要使用备份控制文件恢复数据库,在恢复完成后,准备打开库,很多人知道这个时候如果要打开这个库,需要使用resetlogs操作,虽然在oracle 10g及其以后版本中在恢复的时候可以跨越resetlogs操作,但是很多时候大家还是希望使用备份的控制文件能够正常的open一个库,而不是resetlogs.这里通过实验展示使用备份控制文件正常open库的过程,整体思路是:先使用备份控制文件正常恢复数据库,然后重建该控制文件,继而可以正常open库

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> alter database backup controlfile to '/tmp/controlfile.bak';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

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

--替换备份的控制文件

SQL> startup mount;      
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> recover database using backup controlfile;
ORA-00279: change 12286827844770 generated at 04/12/2012 00:21:54 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_4.dbf
ORA-00280: change 12286827844770 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12286827844772 generated at 04/12/2012 00:21:55 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_5.dbf
ORA-00280: change 12286827844772 for thread 1 is in sequence #5
ORA-00278: log file '/u01/oracle/oradata/xifenfei/archive/1_4.dbf' no longer
needed for this recovery


ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6
ORA-00278: log file '/u01/oracle/oradata/xifenfei/archive/1_5.dbf' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_6.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL>  recover database using backup controlfile;
ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/xifenfei/redo02.log
ORA-00310: archived log contains sequence 3; sequence 6 required
ORA-00334: archived log: '/u01/oracle/oradata/xifenfei/redo02.log'


SQL>  recover database using backup controlfile;
ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/xifenfei/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--提示需要resetlogs

SQL> alter database backup controlfile to trace as '/tmp/1.txt';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes

--重建控制文件
--自动启动到mount状态

--数据库直接open成功
SQL> alter database open;

Database altered.

9I中清除特定表相关执行计划

Posted on by

在9i中因为某个执行计划因为Oracle Peeking绑定变量的控制导致现有的执行计划不正确,需要清除掉这条sql语句的执行计划.在10g中提供了dbms_shared_pool.purge(见:清除掉shared pool中某条sql语句方法),但是在9i中未提供好的方法,一般来说可以通过对相关表的DDL操作,收集统计信息,授权操作可以实现清除对于表执行计划.注:这些操作不会只清空特定SQL执行计划,而是会清除该表相关的所有执行计划,所以操作需要慎重(影响肯定比flush shared_pool小)
模拟测试数据

SQL> create table t_xifenfei (id number,name varchar2(100));

Table created.

SQL> insert into t_xifenfei values(1,'www.orasos.com');

1 row created.

SQL> commit;

清除执行计划1:修改表结构

SQL>  alter system flush shared_pool;

System altered.

SQL> select * from t_xifenfei;

        ID NAME
---------- -------------------
         1 www.orasos.com

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS

SQL> alter table t_xifenfei  add fei varchar2(10);

Table altered.

SQL> alter table t_xifenfei drop COLUMN fei;

Table altered.

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL>  select count(*) from v$sql_plan where hash_value=1067507827;

  COUNT(*)
----------
         0

清除执行计划2:重新收集统计信息

--DBMS_STATS收集统计信息
SQL> alter system flush shared_pool;

System altered.

SQL> select * from t_xifenfei;

        ID NAME
---------- -------------------
         1 www.orasos.com

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS

SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

no rows selected


--analyze收集统计信息(不推荐)
SQL> alter system flush shared_pool;

System altered.

SQL> select * from t_xifenfei;

        ID NAME
---------- -------------------
         1 www.orasos.com

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS

SQL> analyze table  t_xifenfei compute statistics;

Table analyzed.

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

no rows selected

清除执行计划3:创建INDEX

SQL> alter system flush shared_pool;

System altered.

SQL> select * from t_xifenfei;

        ID NAME
---------- -------------------
         1 www.orasos.com

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL>  select OPERATION from v$sql_plan where hash_value=1067507827;

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS

SQL> create index i_txifenfei on t_xifenfei(id) online;

Index created.

SQL> drop index i_txifenfei ;

Index dropped.

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

no rows selected

清除执行计划3:GRANT/REVOKE操作

SQL> alter system flush shared_pool;

System altered.

SQL> select * from t_xifenfei;

        ID NAME
---------- -------------------
         1 www.orasos.com

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS

SQL> GRANT SELECT ON T_XIFENFEI TO SYSTEM;

Grant succeeded.

SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';

SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827

SQL> select OPERATION from v$sql_plan where hash_value=1067507827;

no rows selected