Monthly Archives: 十一月 2011

深入理解LOG_ARCHIVE_DEST_n与STANDBY_ARCHIVE_DEST

Posted on by

一、案例引入
朋友的dg配置如下，问我为什么归档日志都放在了use_db_recovery_file_dest

主库
log_archive_dest_1='location=/U01/app/oracle/oradata/bfodb/arch,valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
log_archive_dest_2='service=tnsname,ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1   reopen=300  register net_timeout=180  valid_for=(online_logfile,primary_role)'
standby_archive_dest='location=use_db_recovery_file_dest'

备库
log_archive_dest_1='location=/U01/app/oracle/oradata/bfodb/arch,valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
log_archive_dest_2='location=location=use_db_recovery_file_dest,valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
standby_archive_dest='location=use_db_recovery_file_dest'

我很惯性的回答，直接传输过来的日志放到LOG_ARCHIVE_DEST_n下面，fal_*过来的归档放置在standby_archive_dest中，也没有过多的思考为什么，因为我们的库都是这样的规则，我已经认为是一种准则了。这个规则也整合符合了他们的要求(都在use_db_recovery_file_dest)中，没有仔细的去看他们的配置，当朋友说到valid_for的属性的时候，我感觉有点不对头了。
1)他们的log_archive_dest_1配置的是ONLINE_LOGFILE，这个是备库，所以肯定不会放到这里
2)难道是通过log_archive_dest_2使得直接传输过来的日志放置到了use_db_recovery_file_dest中？让朋友查询v$standby_log,发现他们的库没有使用standby redo logfile,也就是说，log_archive_dest_2不可能用来传输日志了，现在剩下来可以传输日志的，只有standby_archive_dest了。
3)问题解决了，都传输到use_db_recovery_file_dest，因为LOG_ARCHIVE_DEST_n都不能用(只是从排除法证明)

二、问题深入分析
standby_archive_dest和log_archive_dest_*到底有什么关系，在什么情况下传输到对应的目录中？

如果备库利用standby redo log在备库端自动归档，那么归档日志将会被放置到LOG_ARCHIVE_DEST_n
如果备库是利用主库的arch进程传输过来的归档，那么将会被放置到STANDBY_ARCHIVE_DEST
补充说明：
1)主库的LOG_ARCHIVE_DEST_n='service'默认的arch传输方式，primary会远程将archived log传输到standby_archive_dest下

2)fal_*是通过arch传输过来的，所以使用的是STANDBY_ARCHIVE_DEST目录，如果不存在这个目录，就使用LOG_ARCHIVE_DEST_n

3)If both parameters are specified, the STANDBY_ARCHIVE_DEST initialization parameter 
  overrides the directory location specified with the LOG_ARCHIVE_DEST_n parameter.
如果STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_DEST_n两个参数指定,STANDBY_ARCHIVE_DEST初始化覆盖LOG_ARCHIVE_DEST_n目录指定位置参数。

4)If none of the initialization parameters have been specified, then archived redo log files are stored in the default location for the STANDBY_ARCHIVE_DEST initialization parameter.
如果STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_DEST_n都没有配置，将会把归档日志放到STANDBY_ARCHIVE_DEST的默认目录

5)如果STANDBY_ARCHIVE_DEST未配置，而配置了LOG_ARCHIVE_DEST_n,那么产生的归档将放置到LOG_ARCHIVE_DEST_n

6)建议：STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_DEST_n=‘location’的配置相同

7)在11g，已经不建议使用STANDBY_ARCHIVE_DEST，也就是说建议配置dg的时候尽量使用standby redo logfile

Oracle常用用户权限视图

Posted on by

DBA_SYS_PRIVS 用户所拥有的系统权限

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 16 13:26:09 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user xff identified by xifenfei;

User created.

SQL> grant connect to xff;

Grant succeeded.

SQL> select * from DBA_SYS_PRIVS where grantee='XFF';

no rows selected

SQL> REVOKE CONNECT FROM XFF;

Revoke succeeded.

SQL> grant create session to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO

SQL> grant select  on chf.t_1 to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO
--说明只能查询系统权限，不能查询角色，不能查询用户权限

DBA_SYS_PRIVS 用户所拥有的角色

SQL> grant resource to xff;

授权成功。

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='XFF';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
XFF                            RESOURCE                       NO  YES

ROLE_SYS_PRIVS 角色所拥有的系统权限

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='CONNECT';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

no rows selected

SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT

SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.
--很多时候只能使用sysdba(或者具体特定权限)才能够查询角色有哪些系统权限

ROLE_ROLE_PRIVS: 角色被赋予的角色

SQL> SELECT *FROM ROLE_ROLE_PRIVS WHERE ROLE='DBA';

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            OLAP_DBA                       NO
DBA                            SCHEDULER_ADMIN                YES
DBA                            DELETE_CATALOG_ROLE            YES
DBA                            EXECUTE_CATALOG_ROLE           YES
DBA                            WM_ADMIN_ROLE                  NO
DBA                            EXP_FULL_DATABASE              NO
DBA                            SELECT_CATALOG_ROLE            YES
DBA                            JAVA_DEPLOY                    NO
DBA                            GATHER_SYSTEM_STATISTICS       NO
DBA                            JAVA_ADMIN                     NO
DBA                            XDBADMIN                       NO

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            IMP_FULL_DATABASE              NO
DBA                            XDBWEBSERVICES                 NO

13 rows selected.

SESSION_PRIVS 当前用户所拥有的全部权限

SQL> conn xff/xifenfei
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> conn / as sysdba
Connected.
SQL> revoke create session from xff;

Revoke succeeded.

SQL> grant connect to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
--只能查看系统权限或者角色中包含的系统权限，不能查看用户权限

SESSION_ROLES: 当前用户被激活的角色

SQL> SELECT * from SESSION_ROLES;

no rows selected

SQL> show user;
USER is "SYS"
SQL> conn xff/xifenfei
Connected.
SQL> SELECT *FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT
--sysdba查询无role选项，全部是由系统权限构成

查询某用户的所有系统权限

SQL> SELECT PRIVILEGE, ADMIN_OPTION
  2    FROM DBA_SYS_PRIVS
  3   WHERE GRANTEE = &USERNAME
  4  UNION
  5  --角色转换为权限
  6  SELECT PRIVILEGE, ADMIN_OPTION
  7    FROM ROLE_SYS_PRIVS
  8   WHERE ROLE IN
  9         (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
 10  UNION
 11  --角色的角色转为权限
 12  SELECT PRIVILEGE, ADMIN_OPTION
 13    FROM ROLE_SYS_PRIVS
 14   WHERE ROLE IN (SELECT GRANTED_ROLE
 15                    FROM ROLE_ROLE_PRIVS
 16                   WHERE ROLE IN (SELECT GRANTED_ROLE
 17                                    FROM DBA_ROLE_PRIVS
 18                                   WHERE GRANTEE = &USERNAME));
输入 username 的值:  'XFF'
原值    3:  WHERE GRANTEE = &USERNAME
新值    3:  WHERE GRANTEE = 'XFF'
输入 username 的值:  'XFF'
原值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
新值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'XFF')
输入 username 的值:  'XFF'
原值   18:                                  WHERE GRANTEE = &USERNAME))
新值   18:                                  WHERE GRANTEE = 'XFF'))

PRIVILEGE                                ADM
---------------------------------------- ---
CREATE CLUSTER                           NO
CREATE INDEXTYPE                         NO
CREATE OPERATOR                          NO
CREATE PROCEDURE                         NO
CREATE SEQUENCE                          NO
CREATE SESSION                           NO
CREATE TABLE                             NO
CREATE TRIGGER                           NO
CREATE TYPE                              NO
UNLIMITED TABLESPACE                     NO

已选择10行。

表相关权限视图

SELECT *FROM TABLE_PRIVILEGES;
SELECT * FROM dba_TAB_PRIVS;
SELECT * FROM ROLE_TAB_PRIVS;

drop database操作

Posted on by

一、sql操作

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 15:00:15 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             285216048 bytes
Database Buffers          121634816 bytes
Redo Buffers                8466432 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

二、alert日志内容

Tue Nov 15 15:00:18 2011
Adjusting the default value of parameter parallel_max_servers
from 320 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
ORACLE_HOME = /opt/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      node1.srtcloud.com
Release:        2.6.18-238.19.1.el5
Version:        #1 SMP Fri Jul 15 07:31:24 EDT 2011
Machine:        x86_64
Using parameter settings in server-side spfile /opt/oracle/product/11.2.0/db_1/dbs/spfilet1.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 400M
  control_files            = "/opt/oracle/oradata/t1/control01.ctl"
  control_files            = "/opt/oracle/fast_recovery_area/t1/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/opt/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=t1XDB)"
  local_listener           = "LISTENER_T1"
  audit_file_dest          = "/opt/oracle/admin/t1/adump"
  audit_trail              = "DB"
  db_name                  = "t1"
  open_cursors             = 300
  diagnostic_dest          = "/opt/oracle"
Tue Nov 15 15:00:22 2011
PMON started with pid=2, OS id=26704 
Tue Nov 15 15:00:22 2011
PSP0 started with pid=3, OS id=26706 
Tue Nov 15 15:00:23 2011
VKTM started with pid=4, OS id=26708 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Tue Nov 15 15:00:23 2011
GEN0 started with pid=5, OS id=26712 
Tue Nov 15 15:00:23 2011
DIAG started with pid=6, OS id=26714 
Tue Nov 15 15:00:23 2011
DBRM started with pid=7, OS id=26716 
Tue Nov 15 15:00:23 2011
DIA0 started with pid=8, OS id=26718 
Tue Nov 15 15:00:23 2011
MMAN started with pid=9, OS id=26720 
Tue Nov 15 15:00:23 2011
DBW0 started with pid=10, OS id=26722 
Tue Nov 15 15:00:23 2011
LGWR started with pid=11, OS id=26724 
Tue Nov 15 15:00:23 2011
CKPT started with pid=12, OS id=26726 
Tue Nov 15 15:00:23 2011
SMON started with pid=13, OS id=26728 
Tue Nov 15 15:00:23 2011
RECO started with pid=14, OS id=26730 
Tue Nov 15 15:00:23 2011
MMON started with pid=15, OS id=26732 
Tue Nov 15 15:00:23 2011
MMNL started with pid=16, OS id=26734 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Tue Nov 15 15:00:23 2011
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 2578048199
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Tue Nov 15 15:00:33 2011
drop database
ORA-12719 signalled during: drop database...
Tue Nov 15 15:00:47 2011
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Tue Nov 15 15:00:49 2011
MMON started with pid=15, OS id=26788 
Starting background process MMNL
Tue Nov 15 15:00:49 2011
MMNL started with pid=16, OS id=26790 
ALTER SYSTEM enable restricted session;
Tue Nov 15 15:01:06 2011
drop database
Deleted file /opt/oracle/oradata/t1/system01.dbf
Deleted file /opt/oracle/oradata/t1/sysaux01.dbf
Deleted file /opt/oracle/oradata/t1/undotbs01.dbf
Deleted file /opt/oracle/oradata/t1/users01.dbf
Deleted file /opt/oracle/oradata/t1/redo01.log
Deleted file /opt/oracle/oradata/t1/redo02.log
Deleted file /opt/oracle/oradata/t1/redo03.log
Deleted file /opt/oracle/oradata/t1/temp01.dbf
Deleted file /opt/oracle/product/11.2.0/db_1/dbs/snapcf_t1.f
Shutting down archive processes
Archiving is disabled
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
USER (ospid: 26761): terminating the instance
Instance terminated by USER, pid = 26761
Tue Nov 15 15:01:18 2011
Deleted file /opt/oracle/oradata/t1/control01.ctl
Deleted file /opt/oracle/fast_recovery_area/t1/control02.ctl
Completed: drop database
Shutting down instance (abort)
License high water mark = 1
Tue Nov 15 15:01:32 2011
Instance shutdown complete

三、后续工作
1、清除相关日志trace文件$ORACLE_BASE/diag
2、删除fast_recovery_area文件$ORACLE_BASE/fast_recovery_area
3、删除归档日志(根据配置)
4、删除/etc/oratab中关于该数据库的记录(t1:/opt/oracle/product/11.2.0/db_1:N)

四、补充说明
1、在能够使用dbca删除数据库的情况下，应该选择dbca，这个删除的更加干净
2、dbca删除数据库也需要清理部分文件(如：归档日志)
3、如果对数据库的存储结构比较了解，可以人工关闭数据库后，手工删除相关文件
4、drop database使用于10g及其以上版本

rman恢复spfile最快捷方式

Posted on by

一、sqlplus nomount数据库并恢复spfile

SQL> startup
ORA-01078: failure in processing system parameters                       
LRM-00109: 无法打开参数文件 'E:\ORACLE\11_2_0\DATABASE\INITXFF.ORA' 

RMAN> restore spfile to 'e:\oracle\11_2_0\database\spfilexff.ora'  
2> from 'F:\rmanbackup\20111113_0KMRIT19_1_1';                           

启动 restore 于 14-11月-11                                               
RMAN-00571: ===========================================================  
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================  
RMAN-00601: fatal error in recovery manager                              
RMAN-03004: 执行命令期间出现严重错误       
RMAN-10041: 无法在失败后重新创建轮询通道上下文。
RMAN-10024: 设置 rpc 轮询时出错                                          
RMAN-10005: 打开游标时出错                                               
RMAN-10002: ORACLE 错误 : ORA-03114: not connected to ORACLE             
RMAN-03002: restore 命令 (在 11/14/2011 22:23:24 上) 失败
ORA-03113: 通信通道的文件结尾                                            
进程 ID: 2884                                                            
会话 ID: 97 序列号: 1

1、无spfilexff.ora/initxff.ora/init.ora文件，sqlplus不能启动数据库至nomount状态
2、在数据库没有nomount状态下，不能恢复spfile

二、rman nomount数据库并恢复spfile

RMAN> startup           

已连接到目标数据库 (未启动)
启动失败: ORA-01078: failure in processing system parameters             
LRM-00109: 无法打开参数文件 'E:\ORACLE\11_2_0\DATABASE\INITXFF.ORA'      

在没有参数文件的情况下启动 Oracle 实例以检索 spfile
Oracle 实例已启动
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============  
RMAN-00571: ===========================================================  
RMAN-03002: startup 命令 (在 11/14/2011 22:00:32 上) 失败
ORA-00205: 标识控制文件时出错, 有关详细信息, 请查看预警日志

RMAN> restore spfile to 'e:\oracle\11_2_0\database\spfilexff.ora'        
2> from 'F:\rmanbackup\20111113_0KMRIT19_1_1';                           

启动 restore 于 14-11月-11                                               
分配的通道: ORA_DISK_1                                                   
通道 ORA_DISK_1: SID=10 设备类型=DISK

通道 ORA_DISK_1: 正在从 AUTOBACKUP F:\rmanbackup\20111113_0KMRIT19_1_1 还原 spfile 
通道 ORA_DISK_1: 从 AUTOBACKUP 还原 SPFILE 已完成                        
完成 restore 于 14-11月-11

1、rman会使用一个隐含(默认的参数文件启动数据库至nomount状态)
2、在nomount状态下，rman可以恢复spfile

三、rman启动数据库日志

Mon Nov 14 22:00:26 2011
Starting ORACLE instance (restrict)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as e:\oracle\11_2_0\RDBMS
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
Using parameter settings in client-side pfile C:\S5O4.1 on machine XIFENFEI-PC
System parameters with non-default values:
  sga_target               = 152M
  compatible               = "11.2.0.1.0"
  _dummy_instance          = TRUE
  remote_login_passwordfile= "EXCLUSIVE"
  db_name                  = "XFF"
Mon Nov 14 22:00:27 2011
PMON started with pid=2, OS id=2932 
Mon Nov 14 22:00:28 2011
VKTM started with pid=3, OS id=4364 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Mon Nov 14 22:00:29 2011
GEN0 started with pid=4, OS id=4524 
Mon Nov 14 22:00:30 2011
DIAG started with pid=5, OS id=5472 
Mon Nov 14 22:00:30 2011
DBRM started with pid=6, OS id=5296 
Mon Nov 14 22:00:30 2011
PSP0 started with pid=7, OS id=6120 
Mon Nov 14 22:00:30 2011
DIA0 started with pid=8, OS id=4528 
Mon Nov 14 22:00:30 2011
MMAN started with pid=9, OS id=6052 
Mon Nov 14 22:00:30 2011
DBW0 started with pid=10, OS id=5348 
Mon Nov 14 22:00:30 2011
LGWR started with pid=11, OS id=4904 
Mon Nov 14 22:00:30 2011
CKPT started with pid=12, OS id=5388 
Mon Nov 14 22:00:30 2011
SMON started with pid=13, OS id=4492 
Mon Nov 14 22:00:30 2011
RECO started with pid=14, OS id=576 
Mon Nov 14 22:00:30 2011
MMON started with pid=15, OS id=6072 
Mon Nov 14 22:00:30 2011
MMNL started with pid=16, OS id=5720 
ORACLE_BASE from environment = e:\oracle
Mon Nov 14 22:00:31 2011
alter database mount
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'E:\ORACLE\11_2_0\DATABASE\CTL1XFF.ORA'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-205 signalled during: alter database mount...
Mon Nov 14 22:00:33 2011
Checker run found 1 new persistent data failures

1、查看系统没有发现 C:\S5O4.1文件
2、restrict方式nomount数据库

使用rman找回被误删除表空间

Posted on by

一、案例说明
利用rman备份数据库后，因为人工误删除表空间，现在需要使用非完全恢复来找回被误删除的表空间

二、环境准备

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 12:35:14 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
XFF
ODU
TEMP

7 rows selected.

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------
/opt/oracle/oradata/test/system01.dbf
/opt/oracle/oradata/test/undotbs01.dbf
/opt/oracle/oradata/test/sysaux01.dbf
/opt/oracle/oradata/test/users01.dbf
/opt/oracle/oradata/test/user32g.dbf
/opt/oracle/oradata/test/xifenfei01.dbf
/opt/oracle/oradata/test/user02.dbf
/opt/oracle/oradata/test/odu02.dbf
/opt/oracle/oradata/test/odu01.dbf
/opt/oracle/oradata/test/odu03.dbf
/opt/oracle/oradata/test/xifenfei02.dbf

11 rows selected.

SQL> create tablespace xifenfei datafile 
2   '/opt/oracle/oradata/test/t_xifenfei01.dbf' size 10m ;

Tablespace created.

SQL> create table chf.t_xifenfei tablespace xifenfei 
  2  as
  3  select * from dba_objects;

Table created.


SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     50476

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 14 12:43:35 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2056006906)

RMAN> list backup summary;                 

using target database control file instead of recovery catalog

RMAN> backup database format '/tmp/test_full_%U';

Starting backup at 2011-11-14 12:44:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=/opt/oracle/oradata/test/odu02.dbf
input datafile fno=00002 name=/opt/oracle/oradata/test/undotbs01.dbf
input datafile fno=00001 name=/opt/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/test/sysaux01.dbf
input datafile fno=00009 name=/opt/oracle/oradata/test/odu01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/test/user32g.dbf
input datafile fno=00007 name=/opt/oracle/oradata/test/user02.dbf
input datafile fno=00010 name=/opt/oracle/oradata/test/odu03.dbf
input datafile fno=00011 name=/opt/oracle/oradata/test/xifenfei02.dbf
input datafile fno=00012 name=/opt/oracle/oradata/test/t_xifenfei01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-14 12:44:33
channel ORA_DISK_1: finished piece 1 at 2011-11-14 12:48:59
piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG20111114T124433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:26
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 2011-11-14 12:49:02
channel ORA_DISK_1: finished piece 1 at 2011-11-14 12:49:03
piece handle=/tmp/test_full_02mrkqlr_1_1 tag=TAG20111114T124433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 2011-11-14 12:49:03

RMAN> exit


Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 12:50:53 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop tablespace xifenfei including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~] cd /opt/oradata
[oracle@ECP-UC-DB1 oradata]$ mv test test_bak
[oracle@ECP-UC-DB1 oradata]$ mkdir test
[oracle@ECP-UC-DB1 oradata]$ ll
total 16
drwxr-x--- 3 oracle oinstall 4096 Aug 12 21:50 ecp
drwxr-x--- 3 oracle oinstall 4096 Jun 25 14:23 ecp_bak
drwxr-xr-x 2 oracle oinstall 4096 Nov 14 12:53 test
drwxr-x--- 3 oracle oinstall 4096 Nov 14 12:51 test_bak

通过alert日志，查找出删除表空间xifenfei的时间:Mon Nov 14 12:49:102011

三、恢复测试

[oracle@ECP-UC-DB1 oradata]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 14 12:58:47 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/14/2011 12:58:56
ORA-00205: error in identifying control file, check alert log for more info

RMAN> restore controlfile from '/tmp/test_full_02mrkqlr_1_1';

Starting restore at 2011-11-14 12:59:15
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:03
output filename=/opt/oracle/oradata/test/control01.ctl
output filename=/opt/oracle/oradata/test/control02.ctl
output filename=/opt/oracle/oradata/test/control03.ctl
Finished restore at 2011-11-14 12:59:19

RMAN> restore database;

Starting restore at 2011-11-14 13:00:16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/14/2011 13:00:16
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 2011-11-14 13:00:32
Starting implicit crosscheck backup at 2011-11-14 13:00:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2011-11-14 13:00:33

Starting implicit crosscheck copy at 2011-11-14 13:00:33
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2011-11-14 13:00:33

searching for all files in the recovery area
cataloging files...
no files cataloged

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 /opt/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/test/user32g.dbf
restoring datafile 00006 to /opt/oracle/oradata/test/xifenfei01.dbf
restoring datafile 00007 to /opt/oracle/oradata/test/user02.dbf
restoring datafile 00008 to /opt/oracle/oradata/test/odu02.dbf
restoring datafile 00009 to /opt/oracle/oradata/test/odu01.dbf
restoring datafile 00010 to /opt/oracle/oradata/test/odu03.dbf
restoring datafile 00011 to /opt/oracle/oradata/test/xifenfei02.dbf
restoring datafile 00012 to /opt/oracle/oradata/test/t_xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/test_full_01mrkqdh_1_1
   channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG20111114T124433
channel ORA_DISK_1: restore complete, elapsed time: 00:07:08
Finished restore at 2011-11-14 13:07:42

RMAN> run
2> {
3> sql 'alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss"';
4> set until time='2011-11-14 12:49:10';
5> recover database;
6> }

sql statement: alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting recover at 2011-11-14 13:18:09
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=248
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/14/2011 13:18:10
RMAN-06054: media recovery requesting unknown log: thread 1 seq 248 lowscn 11517136

--另外打开一个会话查询当前最大的归档日志seq#情况
[oracle@ECP-UC-DB1 archivelog]$ ll -thr|tail -10
-rw-r----- 1 oracle oinstall  45M Nov  5 19:00 1_238_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  7 10:00 1_239_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  8 02:25 1_240_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  8 22:25 1_241_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  9 22:26 1_242_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 10 22:01 1_243_757860476.dbf
-rw-r----- 1 oracle oinstall  46M Nov 11 22:01 1_244_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 12 00:00 1_245_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 13 07:26 1_246_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 14 07:27 1_247_757860476.dbf
--证明最大的seq为247，而恢复需要日志的seq为248，就是说需要应用未归档的redo log
--那么我们采用在sqlplus中恢复

RMAN> exit


Recovery Manager complete.


[oracle@ECP-UC-DB1 oradata]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 13:21:24 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss"
  2  ;

Session altered.

SQL>  recover database until time '2011-11-14 12:49:10' using backup controlfile;
ORA-00279: change 11517136 generated at 11/14/2011 12:44:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/test/archivelog1_248_757860476.dbf
ORA-00280: change 11517136 for thread 1 is in sequence #248


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/test_bak/redo01.log
ORA-00310: archived log contains sequence 247; sequence 248 required
ORA-00334: archived log: '/opt/oracle/oradata/test_bak/redo01.log'


SQL> recover database until time '2011-11-14 12:49:10' using backup controlfile;
ORA-00279: change 11517136 generated at 11/14/2011 12:44:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/test/archivelog1_248_757860476.dbf
ORA-00280: change 11517136 for thread 1 is in sequence #248


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/test_bak/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
XFF
ODU
TEMP
XIFENFEI

8 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------
/opt/oracle/oradata/test/system01.dbf
/opt/oracle/oradata/test/undotbs01.dbf
/opt/oracle/oradata/test/sysaux01.dbf
/opt/oracle/oradata/test/users01.dbf
/opt/oracle/oradata/test/user32g.dbf
/opt/oracle/oradata/test/xifenfei01.dbf
/opt/oracle/oradata/test/user02.dbf
/opt/oracle/oradata/test/odu02.dbf
/opt/oracle/oradata/test/odu01.dbf
/opt/oracle/oradata/test/odu03.dbf
/opt/oracle/oradata/test/xifenfei02.dbf
/opt/oracle/oradata/test/t_xifenfei01.dbf

12 rows selected.

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     50476

使用flashback database找回被误删除表空间