分类目录归档：Oracle备份恢复

11.1.0.7版本也会出现access$表丢失导致数据库无法启动

发表于 2015 年 1 月 27 日 由 惜分飞

有网友咨询数据库启动报ora-01092：ORACLE 实例终止。强制断开连接，请求帮忙处理
数据库版本

Trace file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5648.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 1 - type 8664, 1 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:7605M/10239M, Ph+PgF:11979M/20477M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 5648, image: ORACLE.EXE (SHAD)

open数据库报ORA-01092: ORACLE 实例终止。强制断开连接

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

alert日志

Thread 1 opened at log sequence 1008
  Current log# 3 seq# 1008 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3964.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在
Error 704 happened during db open, shutting down database
USER (ospid: 3964): terminating the instance due to error 704
Instance terminated by USER, pid = 3964
ORA-1092 signalled during: ALTER DATABASE OPEN...
ORA-1092 : opiodr aborting process unknown ospid (3384_3964)

做10046分析日志

PARSE ERROR #1:len=56 dep=1 uid=0 oct=3 lid=0 tim=1796038335 err=942
select order#,columns,types from access$ where d_obj#=:1

*** 2015-01-27 21:24:50.794
----- Error Stack Dump -----
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在

通过这里可以知道数据库在启动的过程中由于无法访问access$表从而出现ORA-00942错误,又是由于该sql是数据库内部调用因为出现ORA-00604错误.
出现该错误的原因是由于:BUG:12733463 – ORA-704, ORA-604 AND ORA-942 ON TABLE ACCESS$ DURING STARTUP
官方提供方法

1. Shutdown (abort) the instance and clean up any OS structures used by the instance. 
    Eg: Ensure there is no shared memory, semaphores etc.. left lying around

2. Retry the startup.

3. If the error persists try and recover the database or recover from a backup.

惜分飞处理方法

startup  upgrade

 create table access$
 ( d_obj#        number not null,
   order#        number not null,
   columns       raw(126),
   types         number not null)
   storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

create index i_access1 on
  access$(d_obj#, order#)
  storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

以前类似文章:Oracle 异常恢复案例汇总

发表在 Oracle备份恢复 | 标签为 , , , , | 一条评论

windows rman自动备份并传输到远程服务器处理方法

发表于 2015 年 1 月 3 日 由 惜分飞

在linux中，要使用rman备份后传输到远程服务器上,可以选择ftp,scp,nfs等方式实现,在win主机上可以配置ftp或者共享实现.linux的解决方法已经很多,这里重点提供win上面实现rman备份且传输到远程服务器的解决方法,简单实现异地备份方法:
1.win配置共享目录，而且设置远程服务器有写权限,如果省事可以配置everyone有读写权限
2.创建相关备份目录,这里主要是rmanfile,rmanscript,rmanlog
3.编写rman备份脚本

CONFIGURE RETENTION POLICY TO REDUNDANCY = 7;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup  as compressed backupset database format 'E:\backup_db\rmanfile\full_%T_%U.rman';
sql 'alter system archive log current';
backup  as compressed backupset archivelog  all format 'E:\backup_db\rmanfile\arch_%T_%U.rman'  delete input;
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;
backup  format 'E:\backup_db\rmanfile\ctl_%T_%U.rman' current controlfile;
backup spfile format 'E:\backup_db\rmanfile\spfile_%T_%U.rman' ;
exit;

4.调用rman备份脚本

rman target / cmdfile=E:\backup_db\scriptfile\backup_db.rman 
log=E:\backup_db\logfile\rmanlog_%date:~0,4%%date:~5,2%%date:~8,2%.log

5.拷贝到远程脚本
需要注意是按照备份集中的日期作为标记来删除的，也就是说，一次备份最好不要跨天

copy /y e:\backup_db\rmanfile\*_%date:~0,4%%date:~5,2%%date:~8,2%_*.RMAN \\192.168.13.40\oracle_backup

6.删除远程服务器N天前备份脚本
需要注意是按照备份集中的日期作为标记来删除的，也就是说，一次备份最好不要跨天

@echo off
set DaysAgo=5
call :DateToDays %date:~0,4% %date:~5,2% %date:~8,2% PassDays
set /a PassDays-=%DaysAgo%
call :DaysToDate %PassDays% DstYear DstMonth DstDay
del \\192.168.13.40\oracle_backup\*_%DstYear%%DstMonth%%DstDay%_*.RMAN
goto :eof

:DateToDays %yy% %mm% %dd% days
setlocal ENABLEEXTENSIONS
set yy=%1&set mm=%2&set dd=%3
if 1%yy% LSS 200 if 1%yy% LSS 170 (set yy=20%yy%) else (set yy=19%yy%)
set /a dd=100%dd%%%100,mm=100%mm%%%100
set /a z=14-mm,z/=12,y=yy+4800-z,m=mm+12*z-3,j=153*m+2
set /a j=j/5+dd+y*365+y/4-y/100+y/400-2472633
endlocal&set %4=%j%&goto :EOF

:DaysToDate %days% yy mm dd
setlocal ENABLEEXTENSIONS
set /a a=%1+2472632,b=4*a+3,b/=146097,c=-b*146097,c/=4,c+=a
set /a d=4*c+3,d/=1461,e=-1461*d,e/=4,e+=c,m=5*e+2,m/=153,dd=153*m+2,dd/=5
set /a dd=-dd+e+1,mm=-m/10,mm*=12,mm+=m+3,yy=b*100+d-4800+m/10
(if %mm% LSS 10 set mm=0%mm%)&(if %dd% LSS 10 set dd=0%dd%)
endlocal&set %2=%yy%&set %3=%mm%&set %4=%dd%&goto :EOF

7.配置计划任务,让定时执行相关脚本

发表在 rman备份/恢复 | 标签为 , | 评论关闭

主机断电系统回到N年前数据库报ORA-600 kcm_headroom_warn_1错误

发表于 2014 年 12 月 31 日 由 惜分飞

主机断电导致系统时间回退到14年前,数据库启动报ORA-600[kcm_headroom_warn_1]错误

Sat Jun 21 17:49:12 2014   ---正常系统时间
Instance shutdown complete
Mon Aug 07 06:13:28 2000   ---重启后系统时间
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 64
Effective number of CPU for internal database sizing is 32
Number of processor cores in the system is 8
Number of processor sockets in the system is 1
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
    NUMA status: non-NUMA system
    cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
    Grp 0: 
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =264
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /ora1/prod/db/tech_st/11.2.0
System name:	SunOS
Node name:	erpdb1-boot
Release:	5.10
Version:	Generic_147147-26
Machine:	sun4v
Using parameter settings in server-side spfile /ora1/prod/db/tech_st/11.2.0/dbs/spfileprod.ora
System parameters with non-default values:
  processes                = 1200
  sessions                 = 2400
  timed_statistics         = TRUE
  event                    = ""
  shared_pool_size         = 448M
  shared_pool_reserved_size= 322122547
  nls_language             = "american"
  nls_territory            = "america"
  nls_sort                 = "binary"
  nls_date_format          = "DD-MON-RR"
  nls_numeric_characters   = ".,"
  nls_comp                 = "binary"
  nls_length_semantics     = "BYTE"
  sga_target               = 10G
  control_files            = "/data1/prod/db/apps_st/data/cntrl01.dbf"
  control_files            = "/data1/prod/db/apps_st/data/cntrl02.dbf"
  control_files            = "/data1/prod/db/apps_st/data/cntrl03.dbf"
  db_block_checksum        = "TRUE"
  db_block_size            = 8192
  compatible               = "11.1.0"
  log_archive_dest_1       = "location=/arch1/prod/arch"
  log_archive_format       = "prod_%t_%s_%r.arc"
  log_buffer               = 10485760
  log_checkpoint_interval  = 100000
  log_checkpoint_timeout   = 1200
  db_files                 = 512
  log_checkpoints_to_alert = TRUE
  dml_locks                = 10000
  undo_management          = "AUTO"
  undo_tablespace          = "APPS_UNDOTS1"
  db_block_checking        = "FALSE"
  _disable_fast_validate   = TRUE
  sec_case_sensitive_logon = FALSE
  session_cached_cursors   = 500
  utl_file_dir             = "/usr/tmp"
  plsql_code_type          = "INTERPRETED"
  plsql_optimize_level     = 2
  job_queue_processes      = 10
  _system_trig_enabled     = TRUE
  cursor_sharing           = "EXACT"
  parallel_min_servers     = 0
  parallel_max_servers     = 8
  audit_file_dest          = "/ora1/prod/db/tech_st/admin/prod/adump"
  db_name                  = "prod"
  open_cursors             = 3600
  _sort_elimination_cost_ratio= 5
  _b_tree_bitmap_plans     = FALSE
  _fast_full_scan_enabled  = FALSE
  query_rewrite_enabled    = "true"
  _like_with_bind_as_equality= TRUE
  pga_aggregate_target     = 2G
  workarea_size_policy     = "AUTO"
  _optimizer_autostats_job = FALSE
  optimizer_secure_view_merging= FALSE
  aq_tm_processes          = 4
  olap_page_pool_size      = 4M
  diagnostic_dest          = "/ora1/prod/db/tech_st/11.2.0/admin/prod_erpdb1"
  _trace_files_public      = TRUE
  max_dump_file_size       = "20480"
Mon Aug 07 06:13:30 2000
PMON started with pid=2, OS id=3556 
Mon Aug 07 06:13:30 2000
PSP0 started with pid=3, OS id=3557 
Mon Aug 07 06:13:31 2000
VKTM started with pid=4, OS id=3558 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Mon Aug 07 06:13:31 2000
GEN0 started with pid=5, OS id=3562 
Mon Aug 07 06:13:32 2000
DIAG started with pid=6, OS id=3564 
Mon Aug 07 06:13:32 2000
DBRM started with pid=7, OS id=3565 
Mon Aug 07 06:13:32 2000
DIA0 started with pid=8, OS id=3566 
Mon Aug 07 06:13:32 2000
MMAN started with pid=9, OS id=3567 
Mon Aug 07 06:13:32 2000
DBW0 started with pid=10, OS id=3568 
Mon Aug 07 06:13:32 2000
DBW1 started with pid=11, OS id=3569 
Mon Aug 07 06:13:32 2000
DBW2 started with pid=12, OS id=3570 
Mon Aug 07 06:13:32 2000
DBW3 started with pid=13, OS id=3571 
Mon Aug 07 06:13:32 2000
LGWR started with pid=14, OS id=3572 at elevated priority
Mon Aug 07 06:13:32 2000
CKPT started with pid=15, OS id=3575 
Mon Aug 07 06:13:32 2000
SMON started with pid=16, OS id=3576 
Mon Aug 07 06:13:32 2000
RECO started with pid=17, OS id=3577 
Mon Aug 07 06:13:32 2000
MMON started with pid=18, OS id=3578 
Mon Aug 07 06:13:32 2000
MMNL started with pid=19, OS id=3579 
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /ora1/prod/db/tech_st
Mon Aug 07 06:13:32 2000
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 4111810188
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Aug 07 06:13:36 2000
ALTER DATABASE OPEN
************************************************************
Warning: The SCN headroom for this database is only -51464 hours!
************************************************************
Errors in file /ora1/prod/db/tech_st/11.2.0/admin/prod_erpdb1/diag/rdbms/prod/prod/trace/prod_ora_3583.trc  (incident=441878):
ORA-00600: internal error code, arguments: [kcm_headroom_warn_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /ora1/prod/db/tech_st/11.2.0/admin/prod_erpdb1/diag/rdbms/prod/prod/incident/incdir_441878/prod_ora_3583_i441878.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /ora1/prod/db/tech_st/11.2.0/admin/prod_erpdb1/diag/rdbms/prod/prod/trace/prod_ora_3583.trc:
ORA-00600: internal error code, arguments: [kcm_headroom_warn_1], [], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
Dumping diagnostic data in directory=[cdmp_20000807061339], requested by (instance=1, osid=3583), summary=[incident=441878].
Mon Aug 07 06:14:35 2000
Sweep [inc][441878]: completed
Sweep [inc2][441878]: completed

在数据库出现莫名其妙问题不能启动之时,请注意主机时间,另外还有一例:记录一次ORA-00600[2252]故障解决

发表在 Oracle备份恢复 | 标签为 , | 评论关闭