查询之前创建的表users1中的数据(第一次查询是乱码)
[官方文档]
(http://docs.oracle.com/cd/E11882_01/server.112/e25494/control.htm#ADMIN11293
)
实验环境:centos7+11.2.0.4+单实例
这个实验的目的是在没有使用DG、RAC、RMAN的情况下,丢失了控制文件,
总结分为几种情况:
开机状态(跳过有备份情况)
1.noresetlog模式;
2.resetlog模式
关机状态
有备份
无备份
验证结果:
1.数据库能够正常启动
2.表中的数据正常
实验前步骤:
1.创建两个表空间:test1
2.在表空间test1中创建表 users并添加数据,设置为只读变空间
3.备份控制文件
创建表空间test1
create tablespace test1
datafile '/u01/app/oracle/oradata/orcl/test1.dbf'
size 10M
autoextend on
next 5M maxsize 100M;
创建表users1并添加数据
create table users1(id int,name varchar(200)) tablespace test1;
insert into users1(id,name) values(1,'张三');
insert into users1(id,name) values(1,'李四');
insert into users1(id,name) values(1,'王五');
commit;
修改test1表空间为只读表空间
alter tablespace test1 read only;
备份控制文件
alter database backup controlfile to '/oracle/backup/control.bkp';
open状态
1.noresetlog模式;
查询控制文件位置
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/fast_recovery_area/o rcl/control02.ctl
模拟控制文件丢失或损坏
cd /u01/app/oracle/oradata/orcl
mv control01.ctl control01.bak
cd /u01/app/oracle/fast_recovery_area/orcl
mv control02.ctl control02.bak
在这时创建一个表空间
这里我的test2表空间能创建成功是因为之前我创建过这个表空间然后用drop tablespace test2 including contents and datafiles; 语句 删除了这个表空间,后面我又独立测试下,发现在控制文件删除之后不仅能创建这个表空间,重命名一个表空间还是能够创建成功。正常情况下是不能创建成功的会报控制文件未找到。
SQL> create tablespace test2
datafile '/u01/app/oracle/oradata/orcl/test2.dbf'
size 10M
autoextend on
next 5M maxsize 100M;
create table users2(id int,name varchar(200)) tablespace test2;
insert into users2(id,name) values(1,'张三');
insert into users2(id,name) values(2,'李四');
insert into users2(id,name) values(3,'王五');
commit;
open状态发现控制文件丢失了,需要执行下下面的命令把当前数据库的控制文件写入到trace文件中并查看trace路径
SQL> oradebug setmypid
SQL> alter database backup controlfile to trace;
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trc
查看控制trace中写入的数据,这里介绍的很清楚,创建控制文件分为noresetloghe和ressetlog两种情况。
SQL> !cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trc
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 = /u01/app/oracle/product/11.2.0/db_home1
System name: Linux
Node name: orcl1
Release: 3.10.0-327.el7.x86_64
Version: #1 SMP Thu Nov 19 22:10:57 UTC 2015
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 3560, image: oracle@orcl1 (TNS V1-V3)
*** 2017-09-23 13:55:59.198
*** SESSION ID:(125.5) 2017-09-23 13:55:59.198
*** CLIENT ID:() 2017-09-23 13:55:59.198
*** SERVICE NAME:(SYS$USERS) 2017-09-23 13:55:59.198
*** MODULE NAME:(sqlplus@orcl1 (TNS V1-V3)) 2017-09-23 13:55:59.198
*** ACTION NAME:() 2017-09-23 13:55:59.198
kwqmnich: current time:: 5: 55: 58: 0
kwqmnich: instance no 0 repartition flag 1
kwqmnich: initialized job cache structure
*** 2017-09-23 13:56:40.526
Processing Oradebug command 'setmypid'
*** 2017-09-23 13:56:40.526
Oradebug command 'setmypid' console output: <none>
*** 2017-09-23 13:57:26.939
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
--