2012-02-01 学习笔记(一)

最新推荐文章于 2022-09-27 16:29:35 发布
置顶 最新推荐文章于 2022-09-27 16:29:35 发布
阅读量899 收藏
点赞数
分类专栏： ORACLE 文章标签： oracle file database archive string transformation
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/snowqoo/article/details/7224554
版权

1.   Get the control file info.

In the NOMOUNT status, you can query V$PARAMETER view, the view content from started parameter file.

In the MOUNT status, you can query V$CONTROLFILE view, the view content from control file.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> select * from v$controlfile;

no rows selected

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/oradata/denver/control01.
                                                 ctl, /u01/oradata/denver/contr
                                                 ol02.ctl, /u01/oradata/denver/
                                                 control03.ctl
SQL> alter database mount;

Database altered.

SQL> col name format a100
SQL> select * from v$controlfile;

STATUS  NAME
------- ----------------------------------------------------------------------------------------------------
        /u01/oradata/denver/control01.ctl
        /u01/oradata/denver/control02.ctl
        /u01/oradata/denver/control03.ctl

In MOUNT database process, oracle need find control file, lock control file. If the control file lose, how the database happened? Let me try ! 

[oracle@myCentOs denver]$ mv control01.ctl control01_bak.ctl
[oracle@myCentOs denver]$ mv control02.ctl control02_bak.ctl
[oracle@myCentOs denver]$ mv control03.ctl control03_bak.ctl
[oracle@myCentOs denver]$ ls -l
total 1271420
drwxr-xr-x  2 oracle oinstall      4096 Jan 29 12:56 archive
-rw-r-----  1 oracle oinstall   2023424 Jan 30 09:34 control01_bak.ctl
-rw-r-----  1 oracle oinstall   2023424 Jan 30 09:34 control02_bak.ctl
-rw-r-----  1 oracle oinstall   2023424 Jan 30 09:34 control03_bak.ctl
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 cwmlite01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 drsys01.dbf
-rw-r--r--  1 oracle oinstall 156639232 Jan 29 18:53 example01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Jan 29 18:53 indx01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Jan 29 12:56 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jan 29 12:56 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jan 29 18:53 redo03.log
-rw-r--r--  1 oracle oinstall 398467072 Jan 29 18:53 system01.dbf
-rw-r--r--  1 oracle oinstall  41951232 Jan  2 15:15 temp01.dbf
-rw-r--r--  1 oracle oinstall  10493952 Jan 29 18:53 tools01.dbf
-rw-r--r--  1 oracle oinstall 209723392 Jan 29 18:53 undotbs01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Jan 29 18:53 users01.dbf
-rw-r--r--  1 oracle oinstall  47194112 Jan 29 18:53 xdb01.dbf

SQL> startup nomount
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info

SQL> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/admin/denver/bdump
core_dump_dest                       string      /u01/admin/denver/cdump
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      /u01/admin/denver/udump

[oracle@myCentOs bdump]$ cd /u01/admin/denver/bdump
[oracle@myCentOs bdump]$ ls
alert_denver.log
[oracle@myCentOs bdump]$ vi alert_denver.log 

--------------- alert_denver.log --------------- 
alter database mount
Mon Jan 30 09:47:46 2012
ORA-00202: controlfile: '/u01/oradata/denver/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Conclusion:

In the SQL*PLUS, you can get the following error message.

ORA-00205: error in identifying controlfile, check alert log for more info

In the alert_denver.log, you can find the detail error message.

ORA-00202: controlfile: '/u01/oradata/denver/control01.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Next, We need to restore back.

[oracle@myCentOs denver]$ mv control01_bak.ctl control01.ctl
[oracle@myCentOs denver]$ mv control02_bak.ctl control02.ctl
[oracle@myCentOs denver]$ mv control03_bak.ctl control03.ctl
[oracle@myCentOs denver]$ ls -l
total 1271420
drwxr-xr-x  2 oracle oinstall      4096 Jan 29 12:56 archive
-rw-r-----  1 oracle oinstall   2023424 Jan 30 09:34 control01.ctl
-rw-r-----  1 oracle oinstall   2023424 Jan 30 09:34 control02.ctl
-rw-r-----  1 oracle oinstall   2023424 Jan 30 09:34 control03.ctl
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 cwmlite01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 drsys01.dbf
-rw-r--r--  1 oracle oinstall 156639232 Jan 29 18:53 example01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Jan 29 18:53 indx01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Jan 29 12:56 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jan 29 12:56 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jan 29 18:53 redo03.log
-rw-r--r--  1 oracle oinstall 398467072 Jan 29 18:53 system01.dbf
-rw-r--r--  1 oracle oinstall  41951232 Jan  2 15:15 temp01.dbf
-rw-r--r--  1 oracle oinstall  10493952 Jan 29 18:53 tools01.dbf
-rw-r--r--  1 oracle oinstall 209723392 Jan 29 18:53 undotbs01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Jan 29 18:53 users01.dbf
-rw-r--r--  1 oracle oinstall  47194112 Jan 29 18:53 xdb01.dbf

SQL> startup nomount
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes

SQL> show parameter control_file
control_files                        string      /u01/oradata/denver/control01.
                                                 ctl, /u01/oradata/denver/contr
                                                 ol02.ctl, /u01/oradata/denver/
                                                 control03.ctl
SQL> select * from v$controlfile;

no rows selected

SQL> alter database mount;

Database altered.

SQL> select * from v$controlfile;

STATUS  NAME
------- ----------------------------------------------------------------------------------------------------
        /u01/oradata/denver/control01.ctl
        /u01/oradata/denver/control02.ctl
        /u01/oradata/denver/control03.ctl
OK!

2.  Datafile existence judgement

Theory:

  当你启动实例在mount状态下， 后台进程就可以根据控制文件中记录的数据文件信息来验证数据文件是否存在，如果数据文件不存在，则后台进程将在告警日志文件中记录文件缺失信息，并且在动态视图中记录这些信息。

Let's test it!

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> select name from v$datafile;
select name from v$datafile
                 *
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter database mount;

Database altered.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/u01/oradata/denver/system01.dbf
/u01/oradata/denver/undotbs01.dbf
/u01/oradata/denver/cwmlite01.dbf
/u01/oradata/denver/drsys01.dbf
/u01/oradata/denver/example01.dbf
/u01/oradata/denver/indx01.dbf
/u01/oradata/denver/odm01.dbf
/u01/oradata/denver/tools01.dbf
/u01/oradata/denver/users01.dbf
/u01/oradata/denver/xdb01.dbf

10 rows selected.

Next, We move one dbf file.

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


Database dismounted.
ORACLE instance shut down.
SQL> !mv /u01/oradata/denver/example01.dbf /u01/oradata/denver/example01_bak.dbf

[oracle@myCentOs denver]$ pwd
/u01/oradata/denver

[oracle@myCentOs denver]$ ls -l | grep ex
-rw-r--r--  1 oracle oinstall 156639232 Jan 29 18:53 example01_bak.dbf

SQL> startup mount
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/admin/denver/bdump
core_dump_dest                       string      /u01/admin/denver/cdump
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      /u01/admin/denver/udump

[oracle@myCentOs bdump]$ vi alert_denver.log
[oracle@myCentOs bdump]$ vi alert_denver.log 

Mon Jan 30 13:13:56 2012
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water mark = 3
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Mon Jan 30 13:13:58 2012
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Mon Jan 30 13:13:58 2012
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Mon Jan 30 13:13:59 2012
ARCH shutting down
ARC0: Archival stopped
Mon Jan 30 13:13:59 2012
ARCH shutting down
ARC1: Archival stopped
Mon Jan 30 13:14:00 2012
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Mon Jan 30 13:14:11 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  processes                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 83886080
  large_pool_size          = 16777216
  java_pool_size           = 83886080
  control_files            = /u01/oradata/denver/control01.ctl, /u01/oradata/denver/control02.ctl, /u01/oradata/denver/control03.ctl
  db_block_size            = 8192
  db_cache_size            = 33554432
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  log_archive_dest_1       = LOCATION=/u01/oradata/denver/archive
  log_archive_format       = %t_%s.dbf
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = denver
  dispatchers              = (PROTOCOL=TCP) (SERVICE=denverXDB)
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /u01/admin/denver/bdump
  user_dump_dest           = /u01/admin/denver/udump
  core_dump_dest           = /u01/admin/denver/cdump
  sort_area_size           = 524288
  db_name                  = denver
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 25165824
  aq_tm_processes          = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Mon Jan 30 13:14:12 2012
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC1 started with pid=13
ARC1: Archival started
Mon Jan 30 13:14:12 2012
ARCH: STARTING ARCH PROCESSES COMPLETE
Mon Jan 30 13:14:12 2012
ARC0: Thread not mounted
Mon Jan 30 13:14:12 2012
ARC1: Thread not mounted
Mon Jan 30 13:14:12 2012
ALTER DATABASE   MOUNT
Mon Jan 30 13:14:16 2012
Successful mount of redo thread 1, with mount id 4062806948.
Mon Jan 30 13:14:16 2012
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         5 ONLINE  ONLINE  FILE NOT FOUND                                                             0

SQL> select name from v$datafile where file#=5;

NAME
----------------------------------------------------------------------------------------------------
/u01/oradata/denver/example01.dbf


[oracle@myCentOs bdump]$ vi denver_dbw0_27502.trc 

*** 2012-01-30 13:58:14.328
*** SESSION ID:(2.1) 2012-01-30 13:58:14.327
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/oradata/denver/example01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Conclusion:

In the alert_denver.log, I don't to find any wrong.

In the denver_dbw0_27502.trc, I find the error message.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/oradata/denver/example01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Query V$RECOVER_FILE, I get 'FILE NOT FOUND' error message.

Next, i move all *.dbf, try it, i want to know what would have happened.

SQL> !mv /u01/oradata/denver/undotbs01.dbf /u01/oradata/denver/undotbs01_bak.dbf
SQL> !mv /u01/oradata/denver/cwmlite01.dbf /u01/oradata/denver/cwmlite01_bak.dbf
SQL> !mv /u01/oradata/denver/drsys01.dbf /u01/oradata/denver/drsys01_bak.dbf
SQL> !mv /u01/oradata/denver/indx01.dbf /u01/oradata/denver/indx01_bak.dbf
SQL> !mv /u01/oradata/denver/odm01.dbf /u01/oradata/denver/odm01_bak.dbf
SQL> !mv /u01/oradata/denver/tools01.dbf /u01/oradata/denver/tools01_bak.dbf
SQL> !mv /u01/oradata/denver/users01.dbf /u01/oradata/denver/users01_bak.dbf
SQL> !mv /u01/oradata/denver/xdb01.dbf /u01/oradata/denver/xdb01_bak.dbf
[oracle@myCentOs denver]$ ls -l
total 1271420
drwxr-xr-x  2 oracle oinstall      4096 Jan 29 12:56 archive
-rw-r-----  1 oracle oinstall   2023424 Jan 30 13:33 control01.ctl
-rw-r-----  1 oracle oinstall   2023424 Jan 30 13:33 control02.ctl
-rw-r-----  1 oracle oinstall   2023424 Jan 30 13:33 control03.ctl
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 cwmlite01_bak.dbf
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 drsys01_bak.dbf
-rw-r--r--  1 oracle oinstall 156639232 Jan 29 18:53 example01_bak.dbf
-rw-r--r--  1 oracle oinstall  26222592 Jan 29 18:53 indx01_bak.dbf
-rw-r--r--  1 oracle oinstall  20979712 Jan 29 18:53 odm01_bak.dbf
-rw-r-----  1 oracle oinstall 104858112 Jan 29 12:56 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jan 29 12:56 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jan 29 18:53 redo03.log
-rw-r--r--  1 oracle oinstall 398467072 Jan 29 18:53 system01_bak.dbf
-rw-r--r--  1 oracle oinstall  41951232 Jan  2 15:15 temp01.dbf
-rw-r--r--  1 oracle oinstall  10493952 Jan 29 18:53 tools01_bak.dbf
-rw-r--r--  1 oracle oinstall 209723392 Jan 29 18:53 undotbs01_bak.dbf
-rw-r--r--  1 oracle oinstall  26222592 Jan 29 18:53 users01_bak.dbf
-rw-r--r--  1 oracle oinstall  47194112 Jan 29 18:53 xdb01_bak.dbf
SQL> shutdown immedate
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
ORA-01109: database not openDatabase dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
[oracle@myCentOs bdump]$ vi alert_denver.log 
Mon Jan 30 13:35:09 2012
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
All dispatchers and shared servers shutdown
Mon Jan 30 13:35:12 2012
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...Mon Jan 30 13:35:12 2012
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNTARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabledMon Jan 30 13:35:15 2012
ARCH shutting downMon Jan 30 13:35:15 2012
ARCH shutting downARC0: Archival stopped
Mon Jan 30 13:35:15 2012
ARC1: Archival stopped
Mon Jan 30 13:35:16 2012
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Mon Jan 30 13:35:23 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:  
processes                = 150  
timed_statistics         = TRUE  
shared_pool_size         = 83886080  
large_pool_size          = 16777216  
java_pool_size           = 83886080  
control_files            = /u01/oradata/denver/control01.ctl, /u01/oradata/denver/control02.ctl, /u01/oradata/denver/control03.ctl  
db_block_size            = 8192  
db_cache_size            = 33554432  
compatible               = 9.2.0.0.0  
log_archive_start        = TRUE  
log_archive_dest_1       = LOCATION=/u01/oradata/denver/archive  
log_archive_format       = %t_%s.dbf  
db_file_multiblock_read_count= 16  
fast_start_mttr_target   = 300  
undo_management          = AUTO  
undo_tablespace          = UNDOTBS1  
undo_retention           = 10800  
remote_login_passwordfile= EXCLUSIVE  
db_domain                =  instance_name
job_queue_processes      = 10 
hash_join_enabled        = TRUE  
background_dump_dest     = /u01/admin/denver/bdump  
user_dump_dest           = /u01/admin/denver/udump  
core_dump_dest           = /u01/admin/denver/cdump  
sort_area_size           = 524288  
db_name                  = denver  
open_cursors             = 300  
star_transformation_enabled= FALSE  
query_rewrite_enabled    = FALSE  
pga_aggregate_target     = 25165824  
aq_tm_processes          = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Mon Jan 30 13:35:23 2012
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC1 started with pid=13
Mon Jan 30 13:35:24 2012
ARCH: STARTING ARCH PROCESSES COMPLETE
Mon Jan 30 13:35:24 2012
ARC1: Archival started
Mon Jan 30 13:35:24 2012
ARC0: Thread not mounted
Mon Jan 30 13:35:24 2012
ARC1: Thread not mounted
Mon Jan 30 13:35:24 2012
ALTER DATABASE   MOUNT
Mon Jan 30 13:35:28 2012
Successful mount of redo thread 1, with mount id 4062828444.
Mon Jan 30 13:35:28 2012
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
SQL> select * from v$recover_file;     
FILE# ONLINE  ONLINE_ ERROR  CHANGE# TIME---------- ---------- ---------         
1 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
2 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
3 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
4 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
5 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
6 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
7 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
8 ONLINE  ONLINE  FILE NOT FOUND                                                             0         
9 ONLINE  ONLINE  FILE NOT FOUND                                                             0        
10 ONLINE  ONLINE  FILE NOT FOUND                                                            0
SQL> select name from v$datafile where file# in (select file# from v$recover_file);
NAME----------------------------------------------------------------------------------------------------
/u01/oradata/denver/system01.dbf
/u01/oradata/denver/undotbs01.dbf
/u01/oradata/denver/cwmlite01.dbf
/u01/oradata/denver/drsys01.dbf
/u01/oradata/denver/example01.dbf
/u01/oradata/denver/indx01.dbf
/u01/oradata/denver/odm01.dbf
/u01/oradata/denver/tools01.dbf
/u01/oradata/denver/users01.dbf
/u01/oradata/denver/xdb01.dbf

10 rows selected.
[oracle@myCentOs bdump]$ vi denver_dbw0_27633.trc 
*** 2012-01-30 14:12:24.833*** SESSION ID:(2.1) 2012-01-30 14:12:24.
833ORA-01157: cannot identify/lock data file 
1 - see DBWR trace fileORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'
ORA-27037: unable to obtain file statusLinux 
Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/oradata/denver/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/oradata/denver/cwmlite01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oradata/denver/drsys01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/oradata/denver/example01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/oradata/denver/indx01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/oradata/denver/odm01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or direct
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/u01/oradata/denver/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/oradata/denver/xdb01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/oradata/denver/tools01.dbf'
ORA-27037: unable to obtain file status

Conclusion:

In the alert_denver.log, I don't to find any wrong.

In the denver_dbw0_27502.trc, I find the error message.

ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

ORA-01110: data file 10: '/u01/oradata/denver/xdb01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Query V$RECOVER_FILE, I get 'FILE NOT FOUND' error message.

Next, We need to restore back.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> !mv /u01/oradata/denver/example01_bak.dbf /u01/oradata/denver/example01.dbf

SQL> !mv /u01/oradata/denver/system01_bak.dbf  /u01/oradata/denver/system01.dbf 

SQL> !mv /u01/oradata/denver/undotbs01_bak.dbf /u01/oradata/denver/undotbs01.dbf

SQL> !mv /u01/oradata/denver/cwmlite01_bak.dbf /u01/oradata/denver/cwmlite01.dbf

SQL> !mv /u01/oradata/denver/drsys01_bak.dbf   /u01/oradata/denver/drsys01.dbf

SQL> !mv /u01/oradata/denver/indx01_bak.dbf    /u01/oradata/denver/indx01.dbf

SQL> !mv /u01/oradata/denver/odm01_bak.dbf     /u01/oradata/denver/odm01.dbf

SQL> !mv /u01/oradata/denver/tools01_bak.dbf   /u01/oradata/denver/tools01.dbf

SQL> !mv /u01/oradata/denver/users01_bak.dbf   /u01/oradata/denver/users01.dbf

SQL> !mv /u01/oradata/denver/xdb01_bak.dbf     /u01/oradata/denver/xdb01.dbf
mv: cannot stat `/u01/oradata/denver/xdb01_bak.dbf': No such file or directory

[oracle@myCentOs denver]$ mv xdb0_bak1.dbf xdb01.dbf 

SQL> startup mount
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> select * from v$recover_file;

no rows selected

ok!



















                

        

        

    




    

      

        

            

              
                
                  snowqoo
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
AG阅读总结5.2——实例启动与关闭

				
			

			

				

					changyanmanman的专栏
				

				

					03-19
					
					1329
					
				

			

		

		

			
				
转自一沙弥的世界的博文：原文地址：http://blog.csdn.net/robinson_0612/article/details/5542983



    Oracle数据库实例的启动，严格来说应该是实例的启动，数据库仅仅是在实例启动后进行装载。Oracle数据启动的过程被划分为
几个不同的步骤，在不同的启动过程中，我们可以对其实现不同的操作，系统修复等等工作，因此了解Orac

			
		

	



                

              
                



	

		

			

				
					
Oracle实例关闭的三个阶段,Oracle 数据库实例启动关闭过程

				
			

			

				

					weixin_34991792的博客
				

				

					04-16
					
					485
					
				

			

		

		

			
				
Oracle 数据库实例启动关闭过程当数据库从关闭阶段转为完全打开阶段时，数据库会对以下阶段执行内部一致性检查：?NOMOUNT：实例要达到NOMOUNT(又称STARTED)状态，就必须读取初始化参数文件。实例进入NOMOUNT状态时，不会检查任何数据库文件(控制文件、数据文件、在线Redo文件)。?MOUNT：实例进入MOUNT状态时，会检查初始化参数文件中列出的所有控制文件是否都存在且已...

			
		

	



                


  
              

                


	

		

			

				
					
执行select * from v$controlfile;为什么结果是未选定行？

				
			

			

				

					heizistudio的专栏
				

				

					04-19
					
					4053
					
				

			

		

		

			
				
执行select * from v$controlfile;为什么结果是未选定行？





SQL> select * from v$controlfile; 
未选定行 

而执行show parameter control_files; 
能正确显示控制参数的信息 

请问这是问什么？



 


 



SQL> select * from

			
		

	





	

		

			

				
					
Oracle单实例数据库启动过程详解

				
			

			

				

					qq_35435160的博客
				

				

					04-27
					
					398
					
				

			

		

		

			
				
Oracle单实例数据库启动过程详解
Oracle数据库实例启动时，分成nomount、mount和open的三个阶段
1、nomout阶段，该阶段启动的前提是有参数文件，若没有参数文件，系统无法启动，在该过程中，系统分配内存、开启后台进程，同时更新alter日志文件，初始化部分v$视图。
实例nomount之前的状态：
–无实例进程
-----操作系统环境变量，查看数据库实例名称
[oracle@secdb1 admin]$ echo $ORACLE_SID
PROD
linux系统ps命令将某个进程显示

			
		

	



		

			
		



	

		

			

				
					
软件测试：JUnit测试，Mock测试

				
			

			

				

					燕双嘤
				

				

					04-16
					
					807
					
				

			

		

		

			
				
单元测试对程序来说非常重要，它不仅能增强程序的健壮性，而且也为程序的重构提供了依据。目前很多开源项目的测试覆盖率都高达 90%以上，由此可见单元测试的重要性。

			
		

	





	

		

			

				
					
mybatis框架-学习笔记Day01.rar

				
			

			

				

					03-26
				

			

		

		

			
				
本人博客文章《mybatis框架-学习笔记Day01》的相关代码文件本人博客文章《mybatis框架-学习笔记Day01》的相关代码文件本人博客文章《mybatis框架-学习笔记Day01》的相关代码文件本人博客文章《mybatis框架-学习笔记...

			
		

	





	

		

			

				
					
软件测试-软件测试视频教程学习笔记.zip

				
			

			

				

					04-07
				

			

		

		

			
				
软件测试-软件测试视频教程学习笔记.zip软件测试-软件测试视频教程学习笔记.zip软件测试-软件测试视频教程学习笔记.zip软件测试-软件测试视频教程学习笔记.zip软件测试-软件测试视频教程学习笔记.zip软件测试-软件...

			
		

	





	

		

			

				
					
autokeras学习笔记-安装篇

					
最新发布

				
			

			

				

					06-07
				

			

		

		

			
				
autokeras学习笔记-安装篇

			
		

	





	

		

			

				
					
BEAWebLogicWorkshop8.1学习笔记之一－－　准备篇

				
			

			

				

					02-04
				

			

		

		

			
				
火龙果软件工程技术中心 前言本文作者用BEAWebLogicWorkshop做了一个简单的EJB的开发的例子，并对该例子做了一些Web应用方面的拓展，力图通过这些使用流程教会读者能够上手使用BEAWebLogicWorkshop。...

			
		

	





	

		

			

				
					
oracle00214,oracle添加控制文件,ORA-00214: 错误

				
			

			

				

					weixin_39746282的博客
				

				

					04-04
					
					276
					
				

			

		

		

			
				
今天学习oracle添加控制文件，控制文件是一个二进制文件，记录了物理数据库的当前状态，每一个数据文件只属于一个数据库，为了防止文件损坏丢失等情况，一个数据库不止一个控制文件。一个数据库的控制文件中的内容完全一样。利用数据字典v$controlfile查询数据文件的位置、名字：select name from v$controlfile;结果：NAME-----------------------...

			
		

	





	

		

			

				
					
oracle查询控制文件的位置及其文件名,oracle的控制文件(control file)

				
			

			

				

					weixin_32409707的博客
				

				

					04-03
					
					3789
					
				

			

		

		

			
				
控制文件是一个小小的二进制文件，是oracle数据库的一部分，这个控制文件是用于记录数据库的状态和物理结构。每个数据库必须要至少一个控制文件，但是强烈的建议超过一个控制文件，每个控制文件的备份应该放在不同的磁盘上。一：控制文件包含如下信息：1：数据库名2：数据库创建的时间戳3：数据文件的名字和位置4：redo log (联机重做日志文件)的名字和位置5：当前日志的序列号6：checkpoint 信...

			
		

	





	

		

			

				
					
Oracle：Control File

				
			

			

				

					weixin_42161670的博客
				

				

					06-26
					
					187
					
				

			

		

		

			
				
查看Control File的位置

v$controlfile
show parameter controlfile
v$parameter

idle>
idle>
idle>select name from v$controlfile;

NAME
---------------------------------------------------------------...

			
		

	





	

		

			

				
					
Oracle 常用的经典SQL查询

				
			

			

				

					u011868279的博客
				

				

					09-27
					
					1933
					
				

			

		

		

			
				
Oracle 常用的经典SQL查询

			
		

	





	

		

			

				
					
Oracle 重建控制文件（在丢失控制文件的情况下，如何恢复数据库）

				
			

			

				

					雨花石
				

				

					10-19
					
					2909
					
				

			

		

		

			
				
控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的；丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，dba除了多路保存控制文件外，还需要备份控制文件。
备份控制文件也有多种办法，例如cp拷贝、rman自动备份等，今天给大家介绍如何获取创建控制文件的脚本，这样数据库一旦出现意外，即使找不到可用的备份控制文件，也能通过重建控制文件来启动数据库。
     Oracle提供

			
		

	





	

		

			

				
					
Oracle启动三阶段（一）

				
			

			

				

					thy822的专栏
				

				

					05-11
					
					718
					
				

			

		

		

			
				
Oracle启动是可以分为三个阶段的，分别为：nomount、mount和open。在这三个阶段中，Oracle分别实现了不同的职责和目标，将数据库服务器相关的信息和数据进行加载。  下面就一起来看一下三个阶段的现象和作用。  基础知识——关于Oracle实例相关的三个组成 在谈到Oracle启动关闭的时候，我们需要提一下三个主要组件：Oracle监听器、Oracle实例和OEM管理工具。 在笔者...

			
		

	





	

		

			

				
					
ORACLE --启动和关闭详解

				
			

			

				

					weixin_34190136的博客
				

				

					10-18
					
					132
					
				

			

		

		

			
				
--================================
-->Oracle 数据库实例启动关闭过程
--================================
/*
    Oracle数据库实例的启动，严格来说应该是实例的启动，数据库仅仅是在实例启动后进行装载。Oracle数据启动的过程被划分为
几个不同的步骤，在不同的启动过程中，我们可以对其实现不同的操...

			
		

	





	

		

			

				
					
【道森试听】控制文件——Dave偶像

				
			

			

				

					Sharon--start focusing on Oracle
				

				

					04-17
					
					2463
					
				

			

		

		

			
				
Oracle控制文件
1、概念
在讲控制文件之前，我们看2个概念。
物理组成：数据库由数据文件、控制文件、日志文件、参数文件、口令文件、日志归档文件组成。
Instance：实例由内存结构（memory structure）和后台进程（backgroundprocessor）组成。



控制文件说明：控制文件是oracle 数据库的一个入口文件，通过控制文件，数据库可以找到dat

			
		

	





	

		

			

				
					
oracle备份 方式4,Oracle 备份与恢复学习笔记（4）

				
			

			

				

					weixin_39841640的博客
				

				

					04-08
					
					191
					
				

			

		

		

			
				
第四章：手工备份1、备份的分类:物理备份：备份database的数据文件、控制文件、参数文件等(备份database的物理结构)，可以用于任何形式的failure。1) 手工备份：通过OS 的命令，对要备份的文件拷贝2) rman 备份：利用oracle 的备份工具rman 备份(或其他备份软件)逻辑备份：只备份database 的object的数据结构和数据(如对单个表的备份)，不能用于medi...

			
		

	





	

		

			

				
					

				
			

			

				

					
				

			

		

		

			
				

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值