标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,771)
- DB2 (22)
- MySQL (77)
- Oracle (1,612)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (166)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (69)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (593)
- Oracle安装升级 (98)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (86)
- PostgreSQL (30)
- pdu工具 (6)
- PostgreSQL恢复 (9)
- SQL Server (32)
- SQL Server恢复 (13)
- TimesTen (7)
- 达梦数据库 (3)
- 达梦恢复 (1)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (39)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (22)
-
最近发表
- 一次非常幸运的ORA-600 16703(tab$被清空)故障恢复
- Oracle 19c 202507补丁(RUs+OJVM)-19.28
- 2025年的Oracle 8.0.5数据库恢复
- ORA-600 kokiasg1故障分析(obj$中核心字典序列全部被恶意删除)
- ORA-00756 ORA-10567故障数据0丢失恢复
- 数据库文件变成32k故障恢复
- tcp连接过多导致监听TNS-12532 TNS-12560 TNS-00502错误
- 文件系统格式化MySQL数据库恢复
- .sstop勒索加密数据库恢复
- 解决一次硬件恢复之后数据文件0kb的故障恢复case
- Error in invoking target ‘libasmclntsh19.ohso libasmperl19.ohso client_sharedlib’问题处理
- ORA-01171: datafile N going offline due to error advancing checkpoint
- linux环境oracle数据库被文件系统勒索加密为.babyk扩展名溯源
- ORA-600 ksvworkmsgalloc: bad reaper
- ORA-600 krccfl_chunk故障处理
- Oracle Recovery Tools恢复案例总结—202505
- ORA-600 kddummy_blkchk 数据库循环重启
- 记录一次asm disk加入到vg通过恢复直接open库的案例
- CHECKDB 发现了 N 个分配错误和 M 个一致性错误
- 达梦数据库dm.ctl文件异常恢复
分类目录归档:Oracle
ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated
IMPDP导入数据发现ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated错误
ORA-39083: Object type JOB failed to create with error: ORA-00001: unique constraint (SYS.I_JOB_JOB) violated Failing sql is: BEGIN DBMS_JOB.ISUBMIT( JOB=> 63, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', NO_PARSE=> TRUE); END; Job "GBEAS3"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 16:05:58
错误原因(该job=63已经存在数据库中)
select job, what from dba_jobs where job=63; JOB WHAT ----- -------- 63 proc_xifenfei
注意:如果该job正在运行,可能需要查询DBA_JOBS_RUNNING
解决办法
1.手工创建job,指定一个不存在的job 号 declare m_job number; begin select max (job) + 1 into m_job from dba_jobs; BEGIN DBMS_JOB.ISUBMIT( JOB=> m_job, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', NO_PARSE=> TRUE); END; end; / 2.删除原存在job exec dbms_job.remove (63);
这样的情况,一般发生在expdp导出数据包含了job(如:全库导出,用户导出),然后导入到目标库,而该job号已经存在导致
awr导出/导入/分析
很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等.这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析.
导出awr数据
SQL> @?/rdbms/admin/awrextr.sql ~~~~~~~~~~~~~ AWR EXTRACT ~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ This script will extract the AWR data for a range of snapshots ~ ~ into a dump file. The script will prompt users for the ~ ~ following information: ~ ~ (1) database id ~ ~ (2) snapshot range to extract ~ ~ (3) name of directory object ~ ~ (4) name of dump file ~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Databases in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id DB Name Host ------------ ------------ ------------ * 1393262699 XIFENFEI XIFENFEI-PC 3753332923 FDJDB ora1 3753332923 FDJDB ora2 The default database id is the local one: '1393262699'. To use this database id, press <return> to continue, otherwise enter an alternative. 输入 dbid 的值: 3753332923 <--需要输入 Using 3753332923 for Database ID Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. 输入 num_days 的值: 1 <--需要输入 Listing the last day's Completed Snapshots DB Name Snap Id Snap Started ------------ --------- ------------------ FDJDB 906 23 4月 2012 00:00 907 23 4月 2012 01:00 908 23 4月 2012 02:00 909 23 4月 2012 03:00 910 23 4月 2012 04:00 911 23 4月 2012 05:00 912 23 4月 2012 06:00 913 23 4月 2012 07:00 914 23 4月 2012 08:00 915 23 4月 2012 09:00 916 23 4月 2012 10:00 917 23 4月 2012 11:00 918 23 4月 2012 12:00 919 23 4月 2012 13:00 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 begin_snap 的值: 906 <--需要输入 Begin Snapshot Id specified: 906 输入 end_snap 的值: 907 <--需要输入 End Snapshot Id specified: 907 Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- DATA_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\ DATA_PUMP_DIR E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ LOG_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\ MEDIA_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\ ORACLE_OCM_CONFIG_DIR E:\oracle\product\11.2.0\dbhome_1\ccr\state SS_OE_XMLDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\ord er_entry\ SUBDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep XMLDIR E:\oracle\product\11.2.0\dbhome_1\rdbms\xml Choose a Directory Name from the above list (case-sensitive). 输入 directory_name 的值: DATA_PUMP_DIR <--需要输入(注意大小写) Using the dump directory: DATA_PUMP_DIR Specify the Name of the Extract Dump File ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The prefix for the default dump file name is awrdat_906_907. To use this name, press <return> to continue, otherwise enter an alternative. 输入 file_name 的值: xifenfei_awr <--需要输入 Using the dump file prefix: xifenfei_awr | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file will be located | in the following directory/file: | E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ | xifenfei_awr.dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | *** AWR Extract Started ... | | This operation will take a few moments. The | progress of the AWR extract operation can be | monitored in the following directory/file: | E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ | xifenfei_awr.log | 可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\xifenfei_awr.log | 监控导出awr数据进度 End of AWR Extract
导入awr数据
SQL> @E:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrload.sql ~~~~~~~~~~ AWR LOAD ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ This script will load the AWR data from a dump file. The ~ ~ script will prompt users for the following information: ~ ~ (1) name of directory object ~ ~ (2) name of dump file ~ ~ (3) staging schema name to load AWR data into ~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- DATA_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\ DATA_PUMP_DIR E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ LOG_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\ MEDIA_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\ ORACLE_OCM_CONFIG_DIR E:\oracle\product\11.2.0\dbhome_1\ccr\state SS_OE_XMLDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\ SUBDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep XMLDIR E:\oracle\product\11.2.0\dbhome_1\rdbms\xml Choose a Directory Name from the list above (case-sensitive). 输入 directory_name 的值: DATA_PUMP_DIR <--需要输入(注意大小写) Using the dump directory: DATA_PUMP_DIR Specify the Name of the Dump File to Load ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Please specify the prefix of the dump file (.dmp) to load: 输入 file_name 的值: awrdat_751_919 <--需要输入(文件后缀名一定要是.dmp) Loading from the file name: awrdat_751_919.dmp Staging Schema to Load AWR Snapshot Data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The next step is to create the staging schema where the AWR snapshot data will be loaded. After loading the data into the staging schema, the data will be transferred into the AWR tables in the SYS schema. The default staging schema name is AWR_STAGE. To use this name, press <return> to continue, otherwise enter an alternative. 输入 schema_name 的值: XFF_AWR <--需要输入(临时创建用户) Using the staging schema name: XFF_AWR Choose the Default tablespace for the XFF_AWR user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the XFF_AWR users's default tablespace. This is the tablespace in which the AWR data will be staged. TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE ------------------------------ --------- ------------------ EXAMPLE PERMANENT SYSAUX PERMANENT * USERS PERMANENT Pressing <return> will result in the recommended default tablespace (identified by *) being used. 输入 default_tablespace 的值: EXAMPLE <--需要输入 Using tablespace EXAMPLE as the default tablespace for the XFF_AWR Choose the Temporary tablespace for the XFF_AWR user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the XFF_AWR user's temporary tablespace. TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE ------------------------------ --------- ----------------------- TEMP TEMPORARY * Pressing <return> will result in the database's default temporary tablespace (identified by *) being used. 输入 temporary_tablespace 的值: TEMP <--需要输入 Using tablespace TEMP as the temporary tablespace for XFF_AWR ... Creating XFF_AWR user (临时用户创建) | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Loading the AWR data from the following | directory/file: | E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ | awrdat_751_919.dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | *** AWR Load Started ... | | This operation will take a few moments. The | progress of the AWR load operation can be | monitored in the following directory/file: | E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ | awrdat_751_919.log | | 可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\awrdat_751_919.log | 监控导出awr数据进度 ... Dropping XFF_AWR user (临时用户被删除) End of AWR Load
查看awr报告
SQL> @?/RDBMS/admin/awrrpti.sql Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' 输入 report_type 的值: html <--需要输入 Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ 3753332923 2 FDJDB fdjdb2 ora2 3753332923 1 FDJDB fdjdb1 ora1 * 1393262699 1 XIFENFEI xff XIFENFEI-PC 输入 dbid 的值: 3753332923 <--需要输入 Using 3753332923 for database Id 输入 inst_num 的值: 1 <--需要输入 Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. 输入 num_days 的值: 1 <--需要输入 Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- fdjdb1 FDJDB 906 23 4月 2012 00:00 1 907 23 4月 2012 01:00 1 908 23 4月 2012 02:00 1 909 23 4月 2012 03:00 1 910 23 4月 2012 04:00 1 911 23 4月 2012 05:00 1 912 23 4月 2012 06:00 1 913 23 4月 2012 07:00 1 914 23 4月 2012 08:00 1 915 23 4月 2012 09:00 1 916 23 4月 2012 10:00 1 917 23 4月 2012 11:00 1 918 23 4月 2012 12:00 1 919 23 4月 2012 13:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 begin_snap 的值: 917 <--需要输入 Begin Snapshot Id specified: 917 输入 end_snap 的值: 918 <--需要输入 End Snapshot Id specified: 918 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_917_918.html. To use this name, press <return> to continue, otherwise enter an alternative. 输入 report_name 的值:xifenfei_awr.html <--需要输入
发表在 Oracle性能优化
3 条评论
恢复备份控制文件避免resetlogs方式打开数据库
在很多时候,我们需要使用备份控制文件恢复数据库,在恢复完成后,准备打开库,很多人知道这个时候如果要打开这个库,需要使用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.
发表在 Oracle备份恢复
5 条评论