数据库软件安装过程
dbca运行过程
sqlplus执行
rman从多份备份中还原操作
1、现象重现
RMAN> shutdown immediate database dismounted Oracle instance shut down RMAN> startup connected to target database (not started) Oracle instance started database mounted RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 11/18/2011 14:40:40 ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: '/opt/oracle/oradata/test/xifenfei03.dbf' RMAN> restore datafile 11; Starting restore at 2011-11-18 14:41:04 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK channel ORA_DISK_1: restoring datafile 00011 input datafile copy recid=13 stamp=767543949 filename=/tmp/11.dbf destination for restore of datafile 00011: /opt/oracle/oradata/test/xifenfei03.dbf ORA-19505: failed to identify file "/tmp/11.dbf" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-19600: input file is datafile-copy 13 (/tmp/11.dbf) ORA-19601: output file is datafile 11 (/opt/oracle/oradata/test/xifenfei03.dbf) failover to previous backup channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00011 to /opt/oracle/oradata/test/xifenfei03.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:00:02 Finished restore at 2011-11-18 14:41:08
虽然整个过程datafile 11恢复成功了，但是在恢复过程中，先是去读取/tmp/11.dbf的copy文件，读取这个文件失败，然后继续使用/tmp/test_full_01mrkqdh_1_1来恢复数据文件，为什么会出现这样的情况呢？
2、原因分析
RMAN> list copy of datafile 11;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - ------------------- ---------- ------------------- ----
13 11 A 2011-11-18 14:39:09 11517136 2011-11-14 12:44:33 /tmp/11.dbf
--确实存在datafile 11的copy文件/tmp/11.dbf,并且有效
--checkpoint time为:2011-11-14 12:44:33，创建时间为:2011-11-18 14:39:09
RMAN> list backup of datafile 11;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 9.32G DISK 00:04:24 2011-11-14 12:48:57
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20111114T124433
Piece Name: /tmp/test_full_01mrkqdh_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
11 Full 11517136 2011-11-14 12:44:33 /opt/oracle/oradata/test/xifenfei03.dbf
--backupset中也有datafile 11
--checkpoint time为:2011-11-14 12:44:33，创建时间为:2011-11-14 12:48:57
--通过比较这两个关于datafile 11的备份时间，rman自动选择了创建时间比较新的备份恢复
RMAN> crosscheck copy of datafile 11;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
validation failed for datafile copy
datafile copy filename=/tmp/11.dbf recid=13 stamp=767543949
Crosschecked 1 objects
RMAN> list copy of datafile 11;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - ------------------- ---------- ------------------- ----
13 11 X 2011-11-18 14:39:09 11517136 2011-11-14 12:44:33 /tmp/11.dbf
--通过检测发现/tmp/11.dbf是无效的，所以rman继续使用backupset进行恢复datafile 11
在有些时候，当rman对中关于一个对象的备份有多个(备份时间不一致),rman会从最新备份的开始还原，如果第一个失败，使用下一个备份，但是在有些时候可能第一个失败后，rman不自动使用下一个，这个时候的处理思路是:使用crosscheck backup/copy检测无效的备份，删除掉，继续执行
imp乱码分析–解决建议
最近有位朋友一直在为exp/imp操作的乱码问题纠结，总是搞不清楚为什么，而且经常莫名其妙的出现乱码，为此我做了一个实验，来说明这个问题的处理思路
一、准备工作
C:\Users\XIFENFEI>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 11月 17 18:43:00 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: 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 SQL> create table TEST_EXP 2 ( 3 A1 NUMBER, 4 A2 VARCHAR2(10 CHAR), 5 A3 VARCHAR2(10), 6 A4 NVARCHAR2(10), 7 A5 CHAR(10), 8 A6 NCHAR(10) 9 ); 表已创建。 SQL> comment on column TEST_EXP.A1 2 is '数字类型----惜分飞'; 注释已创建。 SQL> comment on column TEST_EXP.A2 2 is 'varchar类型1----惜分飞'; 注释已创建。 SQL> comment on column TEST_EXP.A3 2 is 'varchar类型2----惜分飞'; 注释已创建。 SQL> comment on column TEST_EXP.A4 2 is 'nvarchar类型----惜分飞'; 注释已创建。 SQL> comment on column TEST_EXP.A5 2 is 'char类型----惜分飞'; 注释已创建。 SQL> comment on column TEST_EXP.A6 2 is 'nchar类型----惜分飞'; 注释已创建。 SQL> insert into test_exp values(1,'xifenfeicf','xifenfeicf','xff','xifenfei','xifenfei'); 已创建 1 行。 SQL> insert into test_exp values(1,'惜分飞来向大家问好啦', 2 '杭州惜分飞','杭州惜分飞','杭州惜分飞','杭州惜分飞'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> col parameter for a30 SQL> col value for a20 SQL> select * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%'; PARAMETER VALUE ------------------------------ -------------------- NLS_CHARACTERSET ZHS16GBK NLS_NCHAR_CHARACTERSET AL16UTF16 SQL> exit 从 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 断开 C:\Users\XIFENFEI>exp chf/xifenfei tables=test_exp file=d:\test_exp.dmp log=d:\test_exp.log Export: Release 11.2.0.1.0 - Production on 星期四 11月 17 18:46:10 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 连接到: 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 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 即将导出指定的表通过常规路径... . . 正在导出表 TEST_EXP导出了 2 行 成功终止导出, 没有出现警告。
二、使用AL32UTF8编码导入
C:\Users\XIFENFEI>set NLS_LANG=american_america.AL32UTF8
C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf
Import: Release 11.2.0.1.0 - Production on Thu Nov 17 19:24:58 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing CHF's objects into CHF
. . importing table "TEST_EXP" 2 rows imported
Import terminated successfully without warnings.
--注意此处提示，编码发生了转换
--导出来文件编码为：ZHS16GBK
--现在客户端编码为：AL32UTF8
--导入服务器编码为：ZHS16GBK
--现在的转换是ZHS16GBK-->AL32UTF8 -->ZHS16GBK
--其中ZHS16GBK-->AL32UTF8说成转换也许不太合适
--(因为ZHS16GBK是已经生产的dmp文件中数据的编码，而AL32UTF8是导入客户端的编码，这个到底是否转换待定)
C:\Users\XIFENFEI>sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 17 19:25:58 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
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
SQL> col comments for a30
SQL> SELECT COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';
COLUMN_NAME COMMENTS
------------------------------ ------------------------------
A1 数字类型----惜分飞
A2 varchar类型1----惜分飞
A3 varchar类型2----惜分飞
A4 nvarchar类型----惜分飞
A5 char类型----惜分飞
A6 nchar类型----惜分飞
6 rows selected.
SQL>select * from test_exp;
A1 A2 A3 A4 A5 A6
---------- -------------------- ---------- -------------------- ---------- --------------------
1 xifenfeicf xifenfeicf xff xifenfei xifenfei
1 惜分飞来向大家问好啦 杭州惜分飞 杭州惜分飞 杭州惜分飞 杭州惜分飞
--在新窗口查询，编码修改客户端编码造成影响
三、使用US7ASCII编码导入
C:\Users\XIFENFEI>set NLS_LANG=american_america.US7ASCII
C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf
Import: Release 11.2.0.1.0 - Production on Thu Nov 17 19:35:10 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing CHF's objects into CHF
. . importing table "TEST_EXP" 2 rows imported
Import terminated successfully without warnings.
SQL> col comments for a30
SQL> SELECT COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';
COLUMN_NAM COMMENTS
---------- ------------------------------
A1 ????----???
A2 varchar??1----???
A3 varchar??2----???
A4 nvarchar??----???
A5 char??----???
A6 nchar??----???
6 rows selected.
--sqlplus和plsql dev中均为乱码
SQL> select * from test_exp;
A1 A2 A3 A4 A5 A6
---------- ---------- ---------- ---------- ---------- ----------
1 xifenfeicf xifenfeicf xff xifenfei xifenfei
1 ?????????? ????? ????? ????? ?????
--在plsql dev中查询是正常，sqlplus中不正常
--这里为什么plsql dev中能够显示正常，而comment在plsql dev中显示不正常，还有待研究
--说明：这里由于ZHS16GBK转换为US7ASCII的过程不能识别汉字，所以会导致汉字变成了问号
四、使用ZHS16GBK编码
C:\Users\XIFENFEI>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf
Import: Release 11.2.0.1.0 - Production on 星期四 11月 17 20:26:39 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: 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
经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 CHF 的对象导入到 CHF
. . 正在导入表 "TEST_EXP"导入了 2 行
成功终止导入, 没有出现警告。
--注意提示，没有发生任何的编码转换
QL> col comments for a30
SQL> SELECT COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';
COLUMN_NAME COMMENTS
------------------------------ ------------------------------
A1 数字类型----惜分飞
A2 varchar类型1----惜分飞
A3 varchar类型2----惜分飞
A4 nvarchar类型----惜分飞
A5 char类型----惜分飞
A6 nchar类型----惜分飞
6 rows selected.
SQL>select * from test_exp;
A1 A2 A3 A4 A5 A6
---------- -------------------- ---------- -------------------- ---------- --------------------
1 xifenfeicf xifenfeicf xff xifenfei xifenfei
1 惜分飞来向大家问好啦 杭州惜分飞 杭州惜分飞 杭州惜分飞 杭州惜分飞
五、原因分析,解决建议
在导入过程中，最多会发生三次编码转换:
1、执行exp时，数据库中数据的编码会转换为导出客户端编码
2、执行imp时，dmp文件的编码转换为导入客户端编码
3、导入客户端编码转换为目标端数据库的数据库编码
在exp/imp操作的过程中，经常出现乱码的原因就是编码的相互转换的过程中出现了丢失或者相互不能转换导致。要解决这个问题，最好的办法就是通过NLS_LANG的灵活设置，减少编码转换的次数(如果相邻的转换操作编码一致，那么不会发生编码转换，如试验中的ZHS16GBK编码测试，就没有转换发生)，或者使得相互的转换能够兼容，可以最大程度的减少乱码的出现。
如果已经有了exp导出的dmp文件，然后在导入的过程中，出现乱码，一般的处理建议是nls_lang的编码设置和dmp文件的一致，让转换发生在导入客户端和数据库服务器间(要求:编码可以相互转换)
深入理解LOG_ARCHIVE_DEST_n与STANDBY_ARCHIVE_DEST
一、案例引入
朋友的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常用用户权限视图
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操作
一、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最快捷方式
一、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找回被误删除表空间
一、案例说明
利用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
ORA-19693: backup piece %s already included
一、问题现象
RMAN> restore database; 启动 restore 于 13-11月-11 使用通道 ORA_DISK_1 通道 ORA_DISK_1: 正在开始还原数据文件备份集 通道 ORA_DISK_1: 正在指定从备份集还原的数据文件 通道 ORA_DISK_1: 将数据文件 00001 还原到 E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF 通道 ORA_DISK_1: 将数据文件 00002 还原到 E:\ORACLE\ORADATA\XFF\SYSAUX01.DBF 通道 ORA_DISK_1: 将数据文件 00003 还原到 E:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF 通道 ORA_DISK_1: 将数据文件 00004 还原到 E:\ORACLE\ORADATA\XFF\USERS01.DBF 通道 ORA_DISK_1: 将数据文件 00005 还原到 E:\ORACLE\ORADATA\XFF\EXAMPLE01.DBF 通道 ORA_DISK_1: 将数据文件 00006 还原到 E:\ORACLE\ORADATA\XFF\O_ORACLE.DBF 通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\ORACLE\ORADATA\XFF\XIFENFEI01.DBF 通道 ORA_DISK_1: 将数据文件 00008 还原到 E:\ORACLE\ORADATA\XFF\P_TEST01.DBF 通道 ORA_DISK_1: 将数据文件 00009 还原到 E:\ORACLE\ORADATA\XFF\SYS_MG01.DBF 通道 ORA_DISK_1: 将数据文件 00010 还原到 E:\ORACLE\ORADATA\XFF\P101.DBF 通道 ORA_DISK_1: 将数据文件 00011 还原到 E:\ORACLE\ORADATA\XFF\P201.DBF 通道 ORA_DISK_1: 将数据文件 00012 还原到 E:\ORACLE\ORADATA\XFF\P301.DBF 通道 ORA_DISK_1: 将数据文件 00015 还原到 E:\ORACLE\ORADATA\XFF\OGG01.DBF 通道 ORA_DISK_1: 将数据文件 00016 还原到 E:\ORACLE\ORADATA\XFF\SPOT01.DBF RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: restore 命令 (在 11/13/2011 18:19:31 上) 失败 ORA-19693: 已包括备份片段 F:\RMANBACKUP\9_12_0GMMD2KI_1_1 RMAN> list backup summary; 备份列表 =============== 关键字 TY LV S 设备类型 完成时间 段数 副本数 压缩标记 ------- -- -- - ----------- ---------- ------- ------- ---------- --- 6 B F A DISK 12-9月 -11 1 2 NO TAG20110912T215425 7 B F A DISK 12-9月 -11 1 1 NO TAG20110912T215425 8 B F A DISK 12-9月 -11 1 1 NO TAG20110912T220120 19693, 00000, "backup piece %s already included" // *Cause: This backup piece was already specified for inclusion in the // restore conversation. A restore conversation may process only // a single instance of a backup piece. // *Action: Remove the specified duplicate backup piece in restore steps // and restart the conversation.
二、解决办法
1、查找出副本数大于1的备份集
2、重命名对应的备份集
3、使用crosscheck backup检测出无效的备份集
4、使用delete noprompt expired backup删除无效备份集
5、使用CATALOG START WITH重新添加刚刚重命名的备份集
三、问题原因
Bug 4483368: SEVERAL RMAN CATALOG START WITH CREATES DUPLICATE CATALOG ENTRIES
After several "RMAN catalog start with "we have several copies of the same backuppiece in our catalog which we can see with "list backup;" and also "list backup summary;". However, when I now try to restore with that controlfile instead of a catalog I receive ORA-19693 backup piece "/opt/oracle/admin/mnt/HERMES2/bkp/..." is already included.
使用flashback database找回被误删除表空间
我个人不怎么推荐使用flashback database功能，因为在生产库中，谁能够承受得起整个库的回退(也许特别极端的情况下可能需要使用到)，今天帮网友恢复了一个案例:删除表空间，然后尝试着flashback database功能把这个删除的表空间找回来，但是他在整个操作过程中思路比较混乱，最后导致数据库不能正常起来。因为网友有这个方面的需求，我做了一个flashback database 找回表空间操作的试验(官方建议:flashback database在不改变数据文件的情况下使用)
1、恢复过程
SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> create tablespace xff_test datafile 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
size 10m autoextend on next 1m maxsize 20m;
表空间已创建。
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-11-12 21:05:07
SQL> create table chf.t_flashback tablespace xff_test
2 as
3 select * from dba_objects;
表已创建。
SQL> select count(*) from chf.t_flashback;
COUNT(*)
----------
73211
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-11-12 21:06:35
SQL> host dir E:\ORACLE\ORADATA\XFF\xff_test01.dbf
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\ORADATA\XFF 的目录
2011/11/12 21:04 10,493,952 XFF_TEST01.DBF
1 个文件 10,493,952 字节
0 个目录 14,644,822,016 可用字节
SQL> drop tablespace xff_test including contents and datafiles;
表空间已删除。
SQL> host dir E:\ORACLE\ORADATA\XFF\xff_test01.dbf
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\ORADATA\XFF 的目录
找不到文件
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 368263168 bytes
Fixed Size 1374668 bytes
Variable Size 276825652 bytes
Database Buffers 83886080 bytes
Redo Buffers 6176768 bytes
数据库装载完毕。
SQL> flashback database to timestamp
2 to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss');
闪回完成。
SQL> alter database open resetlogs;
数据库已更改。
SQL> select name from v$datafile;
NAME
-------------------------------------------------------------
E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF
E:\ORACLE\ORADATA\XFF\SYSAUX01.DBF
E:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF
E:\ORACLE\ORADATA\XFF\USERS01.DBF
E:\ORACLE\ORADATA\XFF\EXAMPLE01.DBF
E:\ORACLE\ORADATA\XFF\O_ORACLE.DBF
E:\ORACLE\ORADATA\XFF\XIFENFEI01.DBF
E:\ORACLE\ORADATA\XFF\P_TEST01.DBF
E:\ORACLE\ORADATA\XFF\SYS_MG01.DBF
E:\ORACLE\ORADATA\XFF\P101.DBF
E:\ORACLE\ORADATA\XFF\P201.DBF
NAME
---------------------------------------------------------------
E:\ORACLE\ORADATA\XFF\P301.DBF
E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
E:\ORACLE\ORADATA\XFF\OGG01.DBF
E:\ORACLE\ORADATA\XFF\SPOT01.DBF
已选择15行。
SQL> desc chf.t_flashback;
ERROR:
ORA-04043: 对象 chf.t_flashback 不存在
SQL> COL ERROR FOR A20
SQL> SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE;
FILE# ONLINE_ ERROR
---------- ------- --------------------
13 OFFLINE FILE NOT FOUND
SQL> SELECT NAME FROM V$DATAFILE WHERE FILE#=13;
NAME
-----------------------------------------------------------------
E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
SQL> HOST DIR E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\11_2_0\DATABASE 的目录
找不到文件
SQL> ALTER DATABASE CREATE DATAFILE 13
AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf';
数据库已更改。
SQL> HOST DIR E:\ORACLE\ORADATA\XFF\xff_test01.dbf
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\ORADATA\XFF 的目录
2011/11/12 21:25 10,493,952 XFF_TEST01.DBF
1 个文件 10,493,952 字节
0 个目录 14,640,848,896 可用字节
SQL> recover datafile 13;
ORA-00279: 更改 10903431152368 (在 11/12/2011 21:04:40 生成) 对于线程 1
是必需的
ORA-00289: 建议: E:\ORACLE\ARCHIVELOG\ARC0000000241_0753489409.0001
ORA-00280: 更改 10903431152368 (用于线程 1) 在序列 #241 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
已应用的日志。
完成介质恢复。
SQL> alter database datafile 13 online;
数据库已更改。
SQL> select count(*) from chf.t_flashback;
COUNT(*)
----------
73211
2、alert中关键信息
Sat Nov 12 21:12:30 2011
flashback database to timestamp to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss')
Flashback Restore Start
Flashback: created tablespace #18: 'XFF_TEST' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00013' for tablespace #18 in the controlfile.
Filename was:
'E:\ORACLE\ORADATA\XFF\XFF_TEST01.DBF' when dropped.
File will have to be restored from a backup and recovered.
Flashback: deleted datafile #13 in tablespace #18 from control file.
Flashback: dropped tablespace #18: 'XFF_TEST' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
Sat Nov 12 21:12:38 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 241 Reading mem 0
Mem# 0: E:\ORACLE\ORADATA\XFF\REDO01
Flashback recovery: Added file #13 to control file as OFFLINE and 'UNNAMED00013'
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
'E:\ORACLE\ORADATA\XFF\XFF_TEST01.DBF'
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
Incomplete Recovery applied until change 10903431152644 time 11/12/2011 21:05:11
Sat Nov 12 21:12:44 2011
Flashback Media Recovery Complete
Completed: flashback database to timestamp to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss')
--flashback database 操作过程
……
Sat Nov 12 21:25:29 2011
ALTER DATABASE CREATE DATAFILE 13 AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
Completed: ALTER DATABASE CREATE DATAFILE 13 AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
Sat Nov 12 21:25:59 2011
ALTER DATABASE RECOVER datafile 13
Media Recovery Start
Serial Media Recovery started
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 10903431152644
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 13 ...
Sat Nov 12 21:28:27 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE\ARCHIVELOG\ARC0000000241_0753489409.0001
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: E:\ORACLE\ORADATA\XFF\REDO01
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
Sat Nov 12 21:29:04 2011
alter database datafile 13 online
Completed: alter database datafile 13 online
--恢复被删除数据文件过程,flashbackup database会创建表空间，但是数据文件需要人工干预