扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
当索引的碎片过多时，会影响执行查询的速度，从而影响到我们的工作效率。这时候采取的最有利的措施莫过于重建索引了。本文主要介绍了Oracle数据库中检查索引碎片并重建索引的过程，接下来我们就开始介绍这一过程。 重建索引的步骤如下： 1. 确认基本信息 登入数据库，找到专门存放index 的tablespace，并且这个tablespace下所有index的owner都是tax.将index专门存放在一个独立的tablespace, 与数据表的tablespace分离，是常用的数据库设计方法。 height 4 pct_used 50% del_lf_rows / lf_rows +0.001 0.03 g ) 3. google上下载了遍历所有index脚本发现anlyze index .... validate structure只能填充单个index分析信息，于是google了下，从网上下了个Loop 脚本，遍历索引空间下所有的索引名字，并且可以把所有index的分析信息存放到自己建立的一个用户表中。 4. anlyze index 锁定index发现下载的脚本不好用，应为anlyze index在分析索引前要争取独占锁，锁住index，很明显有些index正在被应用系统的使用，所以运行anlyze失败。这里吸取的教训是，尽量晚上做这种事。但是本人比较喜欢准时回家，所以在语句中添加Exception Handler，抛出anlyze index执行失败的那些index 名称，使脚本正常运行完毕。并且根据打印到前台的index name手动执行那些index分析。 5. 总结虽然发现522个index中有160个符合上面的判断的依据。但是发现索引都不大，而那些拥有百万leaf的索引又没有符合上面的判断条件，所以结论是无需index rebuild online. 没有啥碎片。 rebuild index online，对那些有大量DML操作的大索引是有益的。可以每个月季度做一次针对较大索引的rebuild。通常哪怕rebuild index online也会造成I/O争用，所以有无online意义不大，可以放到３－５个晚上，分批执行rebuild index，锁定index，不让用户用（没有用户等入的时候），并且加上paralle 8关键字，应为发现数据库服务器有8个cpu processors.
创新互联专注于矿区企业网站建设,响应式网站建设,成都做商城网站。矿区网站建设公司,为矿区等地区提供建站服务。全流程按需规划网站，专业设计，全程项目跟踪，创新互联专业和态度为您提供的服务
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。
创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。
另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。
STEP1: Find the existing temp tablespace details
STEP2: Create another Temporary Tablespace TEMP1
STEP3: Move Default Database temp tablespace
STEP4: If any sessions are using temp space, then kill them.
STEP5: Drop the original temp tablespace.
Drop temp tablespace
If you want to change the name from TEMP1 to TEMP, then follow the same process as below.
STEP6: Create TEMP tablespace
STEP7: Make TEMP as default tablespace
STEP8: Drop temporary for tablespace temp1
1、将数据关闭 svrmgrl Shutdown abort2、修改初始化参数文件 $ vi $ORACLE_HOME/dbs/init sid .ora添加以下参数 rollback_segments=(system) _corrupted_rollback_segments=(r01,r02,r03,r04) _allow_resetlogs_corruption=ture3、重新装载数据库 svrmgrl Startup mount4、从数据库的控制文件中将回滚段表空间rbs的数据文件离线并去掉。 svrmgrl recover database using backup controlfile;---- 此时屏幕上可能会出现： ORA_00280 Change #### for thread# is in sequence# specify log:[ RET for suggested|AUTO|from logsource|cancel]输入cancel(不要输入其他命令) svrmgrl alter database open resetlogs;5、 重建新的回滚段 ---- 将旧回滚段及回滚表空间删除。 svrmgrl connect internal svrmgrl create rollback segment ro tablespace system; svrmgrl alter rollback segment ro online; svrmgrl create tablespace rbs datafile ##/##/rbs01.dbf’ size ##k; svrmgrl create rollback segment r01 tablespace rbs; svrmgrl create rollback segment r02 tablespace rbs; svrmgrl create rollback segment r03 tablespace rbs; svrmgrl create rollback segment r04 tablespace rbs; svrmgrl alter rollback segment r01 online; svrmgrl alter rollback segment r02 online; svrmgrl alter rollback segment r03 online; svrmgrl alter rollback segment r04 online; svrmgrl Shutdown abort $ vi $ORACLE_HOME/dbs/init sid .ora rollback_segments=(r01,r02,r03,r04) 将参数_corrupted_rollback_segment _allow_resetlogs_corruption=true去掉 svrmgrl Startup normal(T114)
1、登录主库备份控制文件到数据文件所在的目录。
1.1先查询数据文件所在的目录：select name from v$datafile
SQL select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/ora/oracle/oradata/tsing/system01.dbf
/ora/oracle/oradata/tsing/undotbs01.dbf
/ora/oracle/oradata/tsing/sysaux01.dbf
/ora/oracle/oradata/tsing/users01.dbf
/ora/oracle/oradata/tsing/mast.dbf
/ora/oracle/oradata/tsing/mas.dbf
6 rows selected.
1.2 备份控制文件到数据文件所在的目录
SQL alter database create standby controlfile as '/ora/oracle/oradata/tsing/node1.ctl';
Database altered.
2、登录备库删除数据文件
2.1关闭备库实例
SQL shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
2.2进入数据文件所在的目录，然后删除
cd /ora/oracle/oradata/tsing/
[oracle@TAIXIN-HR1 tsing]$ ls
control01.ctl mast.dbf redo03.log stdREDO04.LOG undotbs01.dbf
control02.ctl orapwtsing stdREDO01.LOG sysaux01.dbf users01.dbf
control03.ctl redo01.log stdREDO02.LOG system01.dbf
mas.dbf redo02.log stdREDO03.LOG temp01.dbf
[oracle@TAIXIN-HR1 tsing]$ rm -f *
3、从主库用SCP命令将数据文件及控制文件传到备库
scp -r /ora/oracle/oradata/tsing/* oracle@TAIXIN-HR1:/ora/oracle/oradata/tsing/
oracle@taixin-hr1's password:
control01.ctl 100% 7088KB 6.9MB/s 00:00
control02.ctl 100% 7088KB 6.9MB/s 00:00
control03.ctl 100% 7088KB 6.9MB/s 00:01
inittsing.ora 100% 1407 1.4KB/s 00:00
mas.dbf 100% 16GB 25.5MB/s 10:30
mast.dbf 100% 136MB 22.7MB/s 00:06
node1.ctl 100% 7088KB 6.9MB/s 00:00
orapwtsing 100% 1536 1.5KB/s 00:00
redo01.log 100% 50MB 25.0MB/s 00:02
redo02.log 100% 50MB 25.0MB/s 00:02
redo03.log 100% 50MB 25.0MB/s 00:02
sqlnet.log 100% 668 0.7KB/s 00:00
stdREDO01.LOG 100% 50MB 16.7MB/s 00:03
stdREDO02.LOG 100% 50MB 50.0MB/s 00:01
stdREDO03.LOG 100% 50MB 50.0MB/s 00:01
stdREDO04.LOG 100% 50MB 25.0MB/s 00:02
sysaux01.dbf 100% 490MB 18.9MB/s 00:26
system01.dbf 100% 520MB 21.7MB/s 00:24
temp01.dbf 100% 20MB 20.0MB/s 00:01
tsing1.ctl 100% 6928KB 6.8MB/s 00:01
undotbs01.dbf 100% 115MB 23.0MB/s 00:05
users01.dbf 100% 5128KB 5.0MB/s 00:00
[oracle@TAIXIN-HR tsing]$
4、重命名刚传到备库的控制文件
4.1先删除目录下原有的三个控制文件
[oracle@TAIXIN-HR1 tsing]$ ls
control01.ctl mast.dbf redo03.log stdREDO04.LOG undotbs01.dbf
control02.ctl node1.ctl sqlnet.log sysaux01.dbf users01.dbf
control03.ctl orapwtsing stdREDO01.LOG system01.dbf
inittsing.ora redo01.log stdREDO02.LOG temp01.dbf
mas.dbf redo02.log stdREDO03.LOG tsing1.ctl
[oracle@TAIXIN-HR1 tsing]$ rm contro*
[oracle@TAIXIN-HR1 tsing]$ ls
inittsing.ora orapwtsing sqlnet.log stdREDO04.LOG tsing1.ctl
mas.dbf redo01.log stdREDO01.LOG sysaux01.dbf undotbs01.dbf
mast.dbf redo02.log stdREDO02.LOG system01.dbf users01.dbf
node1.ctl redo03.log stdREDO03.LOG temp01.dbf
4.2 将node1.ctl的控制文件重命名成三个控制文件
[oracle@TAIXIN-HR1 tsing]$ mv node1.ctl control01.ctl
[oracle@TAIXIN-HR1 tsing]$ cp control01.ctl control02.ctl
[oracle@TAIXIN-HR1 tsing]$ cp control02.ctl control03.ctl
[oracle@TAIXIN-HR1 tsing]$ ls
control01.ctl mast.dbf sqlnet.log sysaux01.dbf users01.dbf
control02.ctl orapwtsing stdREDO01.LOG system01.dbf
control03.ctl redo01.log stdREDO02.LOG temp01.dbf
inittsing.ora redo02.log stdREDO03.LOG tsing1.ctl
mas.dbf redo03.log stdREDO04.LOG undotbs01.dbf
5、连接备库，并按如下操作
sqlplus / as sysdba
SQLstartup nomount
SQLalter database mount standby database;
SQLalter database recover managed standby database disconnect from session;
6、测试重建是否成功（主库切换日志后查看备库是否应用日志）
在主库上执行
SQLalter system switch logfile;
在从库上
SQLselect sequence#,applied from v$archived_log;
alter table tablename move [tablespace tablespacename];\x0d\x0a\x0d\x0adelete数据不会回收已经分配出去的block（也就是delete前后你查看user_segments中的信息不会有改动）。\x0d\x0a但这时你对表执行analyze后查看dba_tables表的话会发现empty_block数目变大或者avg_space数据变小。\x0d\x0a \x0d\x0a如果你希望减少该table占用的实际block数目，\x0d\x0a你需要使用move操作将table重建，oracle才会重新分配block，这时table上的索引会失效，需要rebuild。\x0d\x0a\x0d\x0a一，创建测试环境\x0d\x0a1.1 创建测试表，为其插入16万条记录\x0d\x0acreate table jax_t11 \x0d\x0aas\x0d\x0aselect * from dba_objects \x0d\x0awhere rownum user, -- 表的拥有者\x0d\x0a tabname = upper('jax_t11'), -- 表名称\x0d\x0a method_opt = 'for all indexed columns size 1', -- 获得所有索引列的柱状图\x0d\x0a cascade = TRUE ); -- 级联获取 indexes的统计信息\x0d\x0aend;\x0d\x0a\x0d\x0a1.4 查看表占用空间大小\x0d\x0aselect segment_name,segment_type,bytes/1024/1024 from dba_segments ds\x0d\x0awhere ds.segment_name in ( 'JAX_T11', upper('idx_jax_t11_01'));\x0d\x0a\x0d\x0aSEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024\x0d\x0aJAX_T11 TABLE 17\x0d\x0aIDX_JAX_T11_01 INDEX 9\x0d\x0a这里我们可以看到，表占空间17M，索引占空间9M；\x0d\x0a\x0d\x0a表空间占用明细\x0d\x0aSELECT table_name,tablespace_name,\x0d\x0anum_rows, -- 记录行数\x0d\x0aavg_row_len, --平均行长度 \x0d\x0ablocks,\x0d\x0aavg_space, \x0d\x0aempty_blocks\x0d\x0afrom user_tables ut\x0d\x0awhere ut.table_name = 'JAX_T11'\x0d\x0a\x0d\x0aTABLE_NAME TABLESPACE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS AVG_SPACE EMPTY_BLOCKS\x0d\x0aJAX_T11 DRP_DATA 160000 100 2146 0 0\x0d\x0a\x0d\x0a索引空间占用明细\x0d\x0aSELECT index_name,table_name,leaf_blocks,distinct_keys,num_rows\x0d\x0afrom user_indexes ut\x0d\x0awhere ut.index_name = upper('idx_jax_t11_01')\x0d\x0aINDEX_NAME TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS\x0d\x0aIDX_JAX_T11_01 JAX_T11 1036 9832 160000\x0d\x0a\x0d\x0a二，删除90%的记录后的空间占用\x0d\x0a2.1 删除90%的记录\x0d\x0adelete from jax_t11\x0d\x0a where rowid in (select r1\x0d\x0a from (select rowid r1, mod(rownum, 100) r2 from jax_t11) t\x0d\x0a where r2 user, -- 表的拥有者\x0d\x0a tabname = upper('jax_t11'), -- 表名称\x0d\x0a method_opt = 'for all indexed columns size 1', -- 获得所有索引列的柱状图\x0d\x0a cascade = TRUE ); -- 级联获取 indexes的统计信息\x0d\x0aend;\x0d\x0a\x0d\x0a2.3 查看表占用空间大小\x0d\x0aselect segment_name,segment_type,bytes/1024/1024 from dba_segments ds\x0d\x0awhere ds.segment_name in ( 'JAX_T11', upper('idx_jax_t11_01'));\x0d\x0a\x0d\x0aSEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024\x0d\x0aJAX_T11 TABLE 17\x0d\x0aIDX_JAX_T11_01 INDEX 9\x0d\x0a这里我们可以看到，表占空间17M，索引占空间9M；与删除数据前相比，没有任何改变\x0d\x0a\x0d\x0a表空间占用明细\x0d\x0aSELECT table_name,tablespace_name,\x0d\x0anum_rows, -- 记录行数\x0d\x0aavg_row_len, --平均行长度 \x0d\x0ablocks,\x0d\x0aavg_space, \x0d\x0aempty_blocks\x0d\x0afrom user_tables ut\x0d\x0awhere ut.table_name = 'JAX_T11'\x0d\x0a\x0d\x0aTABLE_NAME TABLESPACE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS AVG_SPACE EMPTY_BLOCKS\x0d\x0aJAX_T11 DRP_DATA 14400 100 2146 0 0\x0d\x0a\x0d\x0a索引空间占用明细\x0d\x0aSELECT index_name,table_name,leaf_blocks,distinct_keys,num_rows\x0d\x0afrom user_indexes ut\x0d\x0awhere ut.index_name = upper('idx_jax_t11_01')\x0d\x0aINDEX_NAME TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS\x0d\x0aIDX_JAX_T11_01 JAX_T11 998 7654 14400\x0d\x0a\x0d\x0a三，move table rebuild index\x0d\x0a3.1 删除90%的记录\x0d\x0aalter table jax_t11 move;\x0d\x0aalter index idx_jax_t11_01 rebuild;\x0d\x0a\x0d\x0a3.2 分析表及索引\x0d\x0abegin\x0d\x0a dbms_stats.gather_table_stats\x0d\x0a ( ownname = user, -- 表的拥有者\x0d\x0a tabname = upper('jax_t11'), -- 表名称\x0d\x0a method_opt = 'for all indexed columns size 1', -- 获得所有索引列的柱状图\x0d\x0a cascade = TRUE ); -- 级联获取 indexes的统计信息\x0d\x0aend;\x0d\x0a\x0d\x0a3.3 查看表占用空间大小\x0d\x0aselect segment_name,segment_type,bytes/1024/1024 from dba_segments ds\x0d\x0awhere ds.segment_name in ( 'JAX_T11', upper('idx_jax_t11_01'));\x0d\x0a\x0d\x0aSEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024\x0d\x0aJAX_T11 TABLE 2\x0d\x0aIDX_JAX_T11_01 INDEX 0.8125\x0d\x0a这里我们可以看到，表占空间2M，索引占空间0.8125M；与删除数据前相比，该回收的空间已经回收完毕
环境:
OS:Red Hat Linux As 5
DB:10.2.0.4
在全部控制文件丢失或损坏，而且没有备份的情况下,可以使用重建控制文件的办法打开数据库.以下模拟所有的控制文件丢失的情况下重建控制文件.
1.备份控制文件(数据库mount或是open状态)
SQL select status from v$instance;
STATUS
------------
OPEN
SQLalter database backup controlfile to trace as '/u01/ftp/bak_controlfile';
2.删除控制文件
[oracle@hxl oracl]$ rm control01.ctl
rm: remove regular file `control01.ctl'? y
[oracle@hxl oracl]$ rm control02.ctl
rm: remove regular file `control02.ctl'? y
[oracle@hxl oracl]$ rm control03.ctl
rm: remove regular file `control03.ctl'? y
3.关闭数据库后尝试打开数据库
SQL shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 1221564 bytes
Variable Size 218106948 bytes
Database Buffers 511705088 bytes
Redo Buffers 2969600 bytes
ORA-00205: error in identifying control file, check alert log for more info
这个时候数据无法打开,以为我们已经删除了控制文件.
4.查看备份控制文件的内容
[oracle@hxl ftp]$ more bak_controlfile
-- 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="oracl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/oracl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/oracl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
5.从备份控制文件中提取我们需要的部分,这里我们选择RESETLOGS,将如下内容保存文件为
create_confile.sql
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
6.执行create_confile.sql
SQLset sqlblanklines on -- 因为文件中有空行,需要将该选项打开,否则执行的时候报语法错误
SQL@/u01/ftp/create_confile.sql
SQL alter database open resetlogs;
Database altered.
说明:
重建控制文件后,若备份信息是存储在控制文件的,该信息会丢失.
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网