create control file 测试3种情况
来源：互联网 发布：中英飞耀速录软件 编辑：程序博客网 时间：2024/05/05 02:45
Case 1: 正常关闭的DB-create control file noresetlogs
Case 2: Abort关闭的DB-create control file noresetlogs
Case 3: 正常关闭的DB-create control file resetlogs
-->Case 1: 正常关闭的DB-create control file noresetlogs
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2641197
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2641197
2641197
2641197
2641197
2641197
2641197
已选择6行。
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2641197
2641197
2641197
2641197
2641197
2641197
SQL> shutdown immediate
从 Oracle Database 11g Release 11.2.0.1.0 - Production 断开
C:\Users\qiang>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 7月 5 17:07:34 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 820236288 bytes
Fixed Size 1377700 bytes
Variable Size 633342556 bytes
Database Buffers 180355072 bytes
Redo Buffers 5160960 bytes
SQL>
SQL>
SQL>
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_1_79WCZTZ7_.LOG' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_2_79WCZW77_.LOG' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_3_79WCZXB3_.LOG' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_8MYLM5WJ_.DBF',
14 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_79WD0G6D_.DBF',
15 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_79WD0RDG_.DBF',
16 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_USERS_79WD155G_.DBF',
17 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_APEX_TBS_88B844KR_.DBF',
18 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_APEX_TEM_88B84R4Z_.DBF'
19 CHARACTER SET ZHS16GBK
20 ;
控制文件已创建。
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2641572
SQL> select checkpoint_change# , last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2641572 2641572
2641572 2641572
2641572 2641572
2641572 2641572
2641572 2641572
2641572 2641572
已选择6行。
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2641572
2641572
2641572
2641572
2641572
2641572
已选择6行。
SQL> alter database open;
数据库已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- -------------- ------------ --------------
1 1 259 52428800 512 1 YES
INACTIVE 2635135 05-7? -13 2640457 05-7? -13
2 1 260 52428800 512 1 YES
INACTIVE 2640457 05-7? -13 2641575 05-7? -13
3 1 261 52428800 512 1 NO
CURRENT 2641575 05-7? -13 2.8147E+14
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_TEMP_79WD0W1J_.TMP'
2 SIZE 93323264 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-->Case 2: Abort关闭的DB-create control file noresetlogs
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2641576
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2641576
2641576
2641576
2641576
2641576
2641576
已选择6行。
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2641576
2641576
2641576
2641576
2641576
2641576
已选择6行。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- -------------- ------------ --------------
1 1 259 52428800 512 1 YES
INACTIVE 2635135 05-7? -13 2640457 05-7? -13
2 1 260 52428800 512 1 YES
INACTIVE 2640457 05-7? -13 2641575 05-7? -13
3 1 261 52428800 512 1 NO
CURRENT 2641575 05-7? -13 2.8147E+14
SQL> shutdown abort
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 820236288 bytes
Fixed Size 1377700 bytes
Variable Size 633342556 bytes
Database Buffers 180355072 bytes
Redo Buffers 5160960 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_1_79WCZTZ7_.LOG'
9 GROUP 2 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_2_79WCZW77_.LOG'
10 GROUP 3 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_3_79WCZXB3_.LOG'
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_8MYLM5WJ_.DBF',
14 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_79WD0G6D_.DBF',
15 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_79WD0RDG_.DBF',
16 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_USERS_79WD155G_.DBF',
17 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_APEX_TBS_88B844KR_.DBF',
18 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_APEX_TEM_88B84R4Z_.DBF'
19 CHARACTER SET ZHS16GBK
20 ;
控制文件已创建。
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2641575
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2641576
2641576
2641576
2641576
2641576
2641576
已选择6行。
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2641576
2641576
2641576
2641576
2641576
2641576
已选择6行。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: ?? 1 ??????
ORA-01110: ???? 1:
'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_8MYLM5WJ_.DBF'
SQL> recover database;
完成介质恢复。
SQL> alter database open;
数据库已更改。
SQL>
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_TEMP_79WD0W1J_.TMP'
2 SIZE 93323264 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
--> Case 3: 正常关闭的DB-create control file resetlogs
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2665508
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2665508
2665508
2665508
2665508
2665508
2665508
已选择6行。
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2665508
2665508
2665508
2665508
2665508
2665508
已选择6行。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- -------------- ------------ --------------
1 1 262 52428800 512 1 NO
CURRENT 2665099 05-7月 -13 2.8147E+14
2 1 260 52428800 512 1 YES
INACTIVE 2640457 05-7月 -13 2641575 05-7月 -13
3 1 261 52428800 512 1 YES
INACTIVE 2641575 05-7月 -13 2665099 05-7月 -13
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 820236288 bytes
Fixed Size 1377700 bytes
Variable Size 633342556 bytes
Database Buffers 180355072 bytes
Redo Buffers 5160960 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_1_79WCZTZ7_.LOG' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_2_79WCZW77_.LOG' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 'D:\APP\QIANG\ORADATA\TEST\ONLINELOG\O1_MF_3_79WCZXB3_.LOG' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_8MYLM5WJ_.DBF',
14 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_79WD0G6D_.DBF',
15 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_79WD0RDG_.DBF',
16 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_USERS_79WD155G_.DBF',
17 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_APEX_TBS_88B844KR_.DBF',
18 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_APEX_TEM_88B84R4Z_.DBF'
19 CHARACTER SET ZHS16GBK
20 ;
控制文件已创建。
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
0
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2669160
2669160
2669160
2669160
2669160
2669160
已选择6行。
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2669160
2669160
2669160
2669160
2669160
2669160
已选择6行。
SQL> recover database;
ORA-00283: ????????????????????
ORA-01610: ???? BACKUP CONTROLFILE ????????????????????
SQL> recover database using backup controlfile;
ORA-00279: ?? 2669160 (? 07/05/2013 17:30:07 ??) ???? 1 ????
ORA-00289: ??:
D:\APP\QIANG\ORADATA\TEST\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_07_05\O1_MF_1
_262_%U_.ARC
ORA-00280: ?? 2669160 (???? 1) ??? #262 ?
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: ????????????????
'D:\APP\QIANG\ORADATA\TEST\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_07_05\O1_MF_
1_262_%U_.ARC'
ORA-27041: ????????????
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????
ORA-00308: ????????????????
'D:\APP\QIANG\ORADATA\TEST\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_07_05\O1_MF_
1_262_%U_.ARC'
ORA-27041: ????????????
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????
SQL> recover database using backup controlfile;
ORA-00279: ?? 2669160 (? 07/05/2013 17:30:07 ??) ???? 1 ????
ORA-00289: ??:
D:\APP\QIANG\ORADATA\TEST\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_07_05\O1_MF_1
_262_%U_.ARC
ORA-00280: ?? 2669160 (???? 1) ??? #262 ?
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\app\qiang\oradata\TEST\ONLINELOG\O1_MF_1_79WCZTZ7_.LOG
已应用的日志。
完成介质恢复。
SQL>
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
0
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2669161
2669161
2669161
2669161
2669161
2669161
已选择6行。
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2669161 2669161
2669161 2669161
2669161 2669161
2669161 2669161
2669161 2669161
2669161 2669161
已选择6行。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ??
SQL> alter database open resetlogs;
数据库已更改。
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2669165
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
2669165
2669165
2669165
2669165
2669165
2669165
已选择6行。
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2669165
2669165
2669165
2669165
2669165
2669165
已选择6行。
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\QIANG\ORADATA\TEST\DATAFILE\O1_MF_TEMP_79WD0W1J_.TMP'
2 SIZE 93323264 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
- create control file 测试3种情况
- control file
- Create+File
- create db时control/ redo logfile至少3个
- Control File中备份信息被覆盖情况下的数据库恢复案例
- Class create, device create, device create file
- Class create, device create, device create file
- Class create, device create, device create file
- Class create, device create, device create file
- Class create, device create, device create file
- Class create, device create, device create file
- oracle control file
- embedded system (control file )
- file system control (FSCTL)
- ORACLE control file
- oracle 中的control file
- Shader - Effect file control
- Sample Control File
- Android的广播机制注册
- Linux多线程系列-2-条件变量的使用（线程安全队列的实现）
- 联系人导入\导出到SIM卡分析
- org.springframework.orm.hibernate3.HibernateQueryException: ClassNotFoundException:
- 什么是控制文件？
- create control file 测试3种情况
- TFT模块闪屏（Flicker)现象分析
- IOS开发学习笔记（十一）——ObjectC中集合类型的使用
- 孙鑫VC++深入详解:Lesson6 Part4 ---CMenu 类的应用:添加子菜单项AppendMenu,插入子菜单项InsertMenu,删除菜单DeleteMenu
- json格式
- Ubuntu 12.04中文输入法的安装
- 未完成
- 瑞芯微平台编译环境搭建
- Java Annotation手册
