RUI个人博客 首页>>Oracle RMAN>>[原]利用trace文件进行控制文件的重建恢复

[原]利用trace文件进行控制文件的重建恢复

[原]利用trace文件进行控制文件的重建恢复

本次实验:丢失全部控制文件,利用trace文件进行控制文件的重建恢复

1.本次实验的环境

     1.1 系统环境

[oracle@ora11g64 ~]$ uname  -a
Linux ora11g64 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
    1.2 数据库环境


SYS@ORA11G>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2. 开始实验

    2.1 创建测试read only表空间,包含两个数据文件

create tablespace ts_ora datafile 

'/u01/app/oracle/oradata/ORA11G/ts_ora1.dbf' size 5m,
'/u01/app/oracle/oradata/ORA11G/ts_ora2.dbf' size 5m;

    2.2查看当前数据库中表空间和数据文件状态 

col file_name for a45
select file_name,status,online_status from dba_data_files;
FILE_NAME                                     STATUS    ONLINE_
--------------------------------------------- --------- -------
/u01/app/oracle/oradata/ORA11G/users01.dbf    AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/undotbs01.dbf  AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/sysaux01.dbf   AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/system01.dbf   AVAILABLE SYSTEM
/u01/app/oracle/oradata/ORA11G/ts_ora1.dbf    AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/example01.dbf  AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/ts_ora2.dbf    AVAILABLE ONLINE

7 rows selected.

col name for a45
select tablespace_name,status,contents from dba_tablespaces;
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
TS_ORA                         ONLINE    PERMANENT

7 rows selected.

    2.3 将测试表空间置于read only 状态

  目的:read only 表空间在使用trace备份恢复控制文件时不自动恢复(还有临时表空间)

alter tablespace ts_ora read only;

Tablespace altered.

  2.4 再次查看相应的文件状态
select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
TS_ORA                         READ ONLY PERMANENT

7 rows selected.

   2.5 查看当前控制文件的位置 
SYS@ORA11G>select name from v$controlfile;

NAME
---------------------------------------------
/u01/app/oracle/oradata/ORA11G/control01.ctl
/u01/app/oracle/oradata/ORA11G/control02.ctl

 

  2.6 使用oradebug进行控制文件的生成脚本

SYS@ORA11G>oradebug setmypid;
Statement processed.
SYS@ORA11G>alter database backup controlfile to trace;

Database altered.

SYS@ORA11G>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_26215.trc
SYS@ORA11G>
SYS@ORA11G>!

   2.7 查看生成的重建控制文件脚本
[oracle@ora11g64 ~]$ cat /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_26215.trc
Trace file /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_26215.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      ora11g64
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: ORA11G
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 26215, image: oracle@ora11g64 (TNS V1-V3)


*** 2015-09-06 16:43:12.402
*** SESSION ID:(37.103) 2015-09-06 16:43:12.402
*** CLIENT ID:() 2015-09-06 16:43:12.402
*** SERVICE NAME:(SYS$USERS) 2015-09-06 16:43:12.402
*** MODULE NAME:(sqlplus@ora11g64 (TNS V1-V3)) 2015-09-06 16:43:12.402
*** ACTION NAME:() 2015-09-06 16:43:12.402
 
Processing Oradebug command 'setmypid'

*** 2015-09-06 16:43:12.403
Oradebug command 'setmypid' console output:

*** 2015-09-06 16:43:35.626
-- 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="ORA11G"
--
-- 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)'
-- LOG_ARCHIVE_DEST_STATE_1=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 "ORA11G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORA11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORA11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORA11G/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORA11G/system01.dbf',
  '/u01/app/oracle/oradata/ORA11G/sysaux01.dbf',
  '/u01/app/oracle/oradata/ORA11G/undotbs01.dbf',
  '/u01/app/oracle/oradata/ORA11G/users01.dbf',
  '/u01/app/oracle/oradata/ORA11G/example01.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- 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 '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/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;
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00006'
  TO '/u01/app/oracle/oradata/ORA11G/ts_ora1.dbf';
ALTER DATABASE RENAME FILE 'MISSING00007'
  TO '/u01/app/oracle/oradata/ORA11G/ts_ora2.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "TS_ORA" ONLINE;
-- 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/ORA11G/temp02.dbf'
     SIZE 10485760  REUSE AUTOEXTEND OFF;
-- 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 "ORA11G" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORA11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORA11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORA11G/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORA11G/system01.dbf',
  '/u01/app/oracle/oradata/ORA11G/sysaux01.dbf',
  '/u01/app/oracle/oradata/ORA11G/undotbs01.dbf',
  '/u01/app/oracle/oradata/ORA11G/users01.dbf',
  '/u01/app/oracle/oradata/ORA11G/example01.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- 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 '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash/ORA11G/archivelog/2015_09_06/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;
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00006'
  TO '/u01/app/oracle/oradata/ORA11G/ts_ora1.dbf';
ALTER DATABASE RENAME FILE 'MISSING00007'
  TO '/u01/app/oracle/oradata/ORA11G/ts_ora2.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "TS_ORA" ONLINE;
-- 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/ORA11G/temp02.dbf'
     SIZE 10485760  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--

*** 2015-09-06 16:43:49.935
Processing Oradebug command 'tracefile_name'

*** 2015-09-06 16:43:49.935
Oradebug command 'tracefile_name' console output: 
/u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_26215.trc

注:两种不同的情况:一种是非归档模式重建,一种是归档模式的重建

3. 重建控制文件,启动数据库

    3.1 手工模拟控制文件全部丢失,删除控制文件

[oracle@ora11g64 ~]$ cd /u01/app/oracle/oradata/ORA11G/

[oracle@ora11g64 ORA11G]$ ll
total 1659384
-rw-r----- 1 oracle oinstall   9846784 Sep  6 16:47 control01.ctl
-rw-r----- 1 oracle oinstall   9846784 Sep  6 16:47 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Sep  2 17:50 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Sep  2 17:44 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Sep  2 17:46 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Sep  6 16:45 redo03.log
-rw-r----- 1 oracle oinstall 534781952 Sep  6 16:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 765468672 Sep  6 16:43 system01.dbf
-rw-r----- 1 oracle oinstall  10493952 Sep  6 16:00 temp02.dbf
-rw-r----- 1 oracle oinstall   5251072 Sep  6 16:34 ts_ora1.dbf
-rw-r----- 1 oracle oinstall   5251072 Sep  6 16:34 ts_ora2.dbf
-rw-r----- 1 oracle oinstall  89137152 Sep  6 16:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Sep  2 17:50 users01.dbf
[oracle@ora11g64 ORA11G]$ rm control0*
[oracle@ora11g64 ORA11G]$ ll *.ctl
ls: *.ctl: No such file or directory                       // 可见控制文件已经全部删除

   3.2 如果是在未知的情况下丢失了控制文件,在使用的过程中,执行DDL操作或者对数据库表空间进行操作或者切换日志等操作都会报错:控制文件找不到了。

SYS@ORA11G>shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ORA11G/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@ORA11G>shutdown abort
ORACLE instance shut down.

   3.3 重新启动数据库到nomount状态进行数据库控制文件重建
SYS@ORA11G>startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
ORA-00205: error in identifying control file, check alert log for more info


SYS@ORA11G>
SYS@ORA11G>CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/ORA11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/ORA11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/ORA11G/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/oradata/ORA11G/system01.dbf',
 14    '/u01/app/oracle/oradata/ORA11G/sysaux01.dbf',
 15    '/u01/app/oracle/oradata/ORA11G/undotbs01.dbf',
 16    '/u01/app/oracle/oradata/ORA11G/users01.dbf',
 17    '/u01/app/oracle/oradata/ORA11G/example01.dbf'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.


   3.4 启动数据库进行恢复
SYS@ORA11G>select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SYS@ORA11G>alter database open;      // 如果报错就按照对应的提示操作

Database altered.

   3.5 确认当前数据库数据文件和表空间情况
SYS@ORA11G>select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
TS_ORA                         READ ONLY PERMANENT          // 依然是read only状态

7 rows selected.

SYS@ORA11G>select file_name,status,online_status from dba_data_files;

FILE_NAME                                     STATUS    ONLINE_
--------------------------------------------- --------- -------
/u01/app/oracle/oradata/ORA11G/example01.dbf  AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/users01.dbf    AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/undotbs01.dbf  AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/sysaux01.dbf   AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/system01.dbf   AVAILABLE SYSTEM
/u01/app/oracle/product/11.2.0/db_1/dbs/MISSI AVAILABLE OFFLINE
NG00006

/u01/app/oracle/product/11.2.0/db_1/dbs/MISSI AVAILABLE OFFLINE
NG00007


7 rows selected.

// 数据文件中多了两个数据文件 MISSING00006 和 MISSING00007   

MISSING后面的是数据文件号,这里可知之前的数据文件 ts_ora1.dbf 和 ts_ora2.dbf


   3.6 将数据表空间ts_ora online时报错,由于read only表空间是不会自动恢复的,所以数据库中找不多对应的数据文件了
SYS@ORA11G>alter tablespace ts_ora online;
alter tablespace ts_ora online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00006'

    重要:将数据文件改名为之前的名字
SYS@ORA11G>alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00006' to '/u01/app/oracle/oradata/ORA11G/ts_ora1.dbf';

Database altered.

SYS@ORA11G>alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/u01/app/oracle/oradata/ORA11G/ts_ora2.dbf';

Database altered.

    3.7 将表空间置为online状态,但是还是不能正常使用,需要置为read write状态,数据库恢复。


SYS@ORA11G>alter tablespace ts_ora online;


Tablespace altered.

SYS@ORA11G>select file_name,status,online_status from dba_data_files;

FILE_NAME                                     STATUS    ONLINE_
--------------------------------------------- --------- -------
/u01/app/oracle/oradata/ORA11G/example01.dbf  AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/users01.dbf    AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/undotbs01.dbf  AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/sysaux01.dbf   AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/system01.dbf   AVAILABLE SYSTEM
/u01/app/oracle/oradata/ORA11G/ts_ora1.dbf    AVAILABLE ONLINE
/u01/app/oracle/oradata/ORA11G/ts_ora2.dbf    AVAILABLE ONLINE

7 rows selected.

SYS@ORA11G>select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
TS_ORA                         READ ONLY PERMANENT

7 rows selected.

SYS@ORA11G>create user tyger identified by oracle default tablespace ts_ora;
create user tyger identified by oracle default tablespace ts_ora
            *
ERROR at line 1:
ORA-01920: user name 'TYGER' conflicts with another user or role name


SYS@ORA11G>alter user tyger default tablespace ts_ora;

User altered.

SYS@ORA11G>conn tyger/oracle
Connected.
TYGER@ORA11G>select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST_BCT                       TABLE

TYGER@ORA11G>create table tyger_ora as select * from test_bct;
create table tyger_ora as select * from test_bct
                                        *
ERROR at line 1:
ORA-01647: tablespace 'TS_ORA' is read-only, cannot allocate space in it


TYGER@ORA11G>conn / as sysdba
Connected.
SYS@ORA11G>alter tablespace ts_ora read write;

Tablespace altered.

SYS@ORA11G>select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
TS_ORA                         ONLINE    PERMANENT

7 rows selected.

SYS@ORA11G>conn tyger/oracle
Connected.
TYGER@ORA11G>create table tyger_ora as select * from test_bct;

Table created.


   3.8 恢复临时表空间

SYS@ORA11G>select file_name,tablespace_name from dba_temp_files;

no rows selected


SYS@ORA11G>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA11G/temp01.dbf' SIZE 30m;

Tablespace altered.

SYS@ORA11G>select file_name,tablespace_name from dba_temp_files;

FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ORA11G/temp01.dbf     TEMP


感想:

通过trace文件可以恢复控制文件,但是整个实验过程中都未进行数据库的读写,所有对于本实验中没有谈到数据丢失情况,但是在生产环境中就有可能会导致数据的丢失,为了避免数据丢失, 备份重于一切!!!


       tyger.wang

     2015/09/06     于北京



版权声明:本文为博主原创文章,未经博主允许不得转载。


昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码