oracle怎么重建,oracle表重建

oracle中怎么重建控制文件或是修改控制文件参数

环境:

创新互联公司-专业网站定制、快速模板网站建设、高性价比胶州网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式胶州网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖胶州地区。费用合理售后完善，10年实体公司更值得信赖。

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.

说明:

重建控制文件后,若备份信息是存储在控制文件的,该信息会丢失.

oracle数据库如何重建索引

当索引的碎片过多时，会影响执行查询的速度，从而影响到我们的工作效率。这时候采取的最有利的措施莫过于重建索引了。本文主要介绍了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临时表空间删除和重建过程分享

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当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