Control file 장애

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 15. 11:26
Control File Multiplexing 작업순서
1. DB 종료
2. Parameter 수정
3. 파일 복사
4. DB 시작

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/control01.ctl
/home/oracle/oradata/testdb/control02.ctl
/home/oracle/oradata/testdb/control03.ctl

위 내용을 아래와 같이 변경한다.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/data/disk1/control01.ctl
/data/disk2/control02.ctl
/data/disk3/control03.ctl


##Control File Multiplexing 과정
[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 15 10:32:20 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/control01.ctl
/home/oracle/oradata/testdb/control02.ctl
/home/oracle/oradata/testdb/control03.ctl

#Control file의 다중화 및 경로 설정
#pfile 사용시
SQL> vi $ORACLE_HOME/dbs/inittestdb.ora
*.control_files='/data/disk1/control01.ctl','/data/disk2/control02.ctl','/data/disk3/control03.ctl'

#spfile 사용시
SQL> alter system set control_files='/data/disk1/control01.ctl',
  2  '/data/disk2/control02.ctl','/data/disk3/control03.ctl'
  3  scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir /data/disk1 /data/disk2 /data/disk3

SQL> !cp /home/oracle/oradata/testdb/control01.ctl /data/disk1/control01.ctl 


##Control File Error #1
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> !cp /data/disk1/control01.ctl /data/disk2/control02.ctl

SQL> !cp /data/disk1/control01.ctl /data/disk3/control03.ctl

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/data/disk1/control01.ctl
/data/disk2/control02.ctl
/data/disk3/control03.ctl

SQL> 


#Control File Error #2
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
#Control file의 버전이 다른 에러를 발생시키기 위해 이전에 Backup 받아놓은 Control file을 복사한다.
SQL> !cp /data/backup/redo_close/control03.ctl /data/disk3/control03.ctl

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
ORA-00214: control file '/data/disk1/control01.ctl' version 572 inconsistent with file '/data/disk3/control03.ctl' version 545


SQL> !cp /data/disk1/control01.ctl /data/disk3/control03.ctl

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> 


##Control File 재 생성
SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/data/disk1/control01.ctl
/data/disk2/control02.ctl
/data/disk3/control03.ctl

SQL> select status from v$instance;

STATUS
------------
OPEN

#실습을 위해 각 디스크에 저장되어 있는 Control file을 모두 삭제한다.
SQL> !rm -rf /data/disk1/*.ctl

SQL> !ls /data/disk1/*.ctl
ls: /data/disk1/*.ctl: 그런 파일이나 디렉토리가 없음

SQL> !rm -rf /data/disk2/*.ctl

SQL> !ls /data/disk2/*.ctl
ls: /data/disk2/*.ctl: 그런 파일이나 디렉토리가 없음

SQL> !rm -rf /data/disk3/*.ctl

SQL> !ls /data/disk3/*.ctl  
ls: /data/disk3/*.ctl: 그런 파일이나 디렉토리가 없음

#Parameter에 지정된 user_dump_dest 경로에 *.trc 포맷으로 저장된다.
SQL> alter database backup controlfile to trace;              

Database altered.

#alter database backup controlfile to trace; 명령어 실행시 user_dump_dest 경로에 저장된다.
SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
user_dump_dest                       string      /home/oracle/admin/testdb/udump
SQL> alter database backup controlfile to trace as '/data/disk1/control01.sql';

Database altered.

#'/data/disk1/control01.sql' 파일 내용엔 NORESETLOGS와 RESETLOG 두 가지 형태의 스크립트가 존재한다.
#위 명령어로 생성한 '/data/disk1/control01.sql' 파일 내용을 아래와 같이 수정한다.
#파일 내용중 아래와 같은 공백이 있을 경우 에러가 발생하니 삭제한다.
#  ) SIZE 5M
#-- STANDBY LOGFILE <--- 행 삭제
# <--- 행 삭제
#DATAFILE

SQL> !vi /data/disk1/control01.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/home/oracle/disk4/redo01_a.log',
    '/home/oracle/disk5/redo01_b.log'
  ) SIZE 5M,
  GROUP 2 (
    '/home/oracle/disk4/redo02_a.log',
    '/home/oracle/disk5/redo02_b.log'
  ) SIZE 5M,
  GROUP 3 (
    '/home/oracle/disk4/redo03_a.log',
    '/home/oracle/disk5/redo03_b.log'
  ) SIZE 5M
DATAFILE
  '/home/oracle/oradata/testdb/system01.dbf',
  '/home/oracle/oradata/testdb/undotbs01.dbf',
  '/home/oracle/oradata/testdb/sysaux01.dbf',
  '/home/oracle/oradata/testdb/users01.dbf',
  '/home/oracle/oradata/testdb/example01.dbf',
  '/home/oracle/oradata/testdb/ts_a01.dbf',
  '/home/oracle/oradata/testdb/ts_b01.dbf',
  '/home/oracle/oradata/testdb/ts_c01.dbf'
CHARACTER SET KO16KSC5601
;
"/data/disk1/control01.sql" 31L, 894C written

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> @/data/disk1/control01.sql
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes

Control file created.

SQL> !ls /data/disk1/*.ctl
/data/disk1/control01.ctl

SQL> !ls /data/disk2/*.ctl
/data/disk2/control02.ctl

SQL> !ls /data/disk3/*.ctl
/data/disk3/control03.ctl

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/data/disk1/control01.ctl
/data/disk2/control02.ctl
/data/disk3/control03.ctl

SQL> 



##control01.sql 파일 내용
[oracle@ghost disk1]$ more control01.sql 
-- 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=%s_%t_%r.arc
--
-- DB_UNIQUE_NAME="testdb"
--
-- 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_2='LOCATION=/data/arc2'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/data/arc1'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER 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 "TESTDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/home/oracle/disk4/redo01_a.log',
    '/home/oracle/disk5/redo01_b.log'
  ) SIZE 5M,
  GROUP 2 (
    '/home/oracle/disk4/redo02_a.log',
    '/home/oracle/disk5/redo02_b.log'
  ) SIZE 5M,
  GROUP 3 (
    '/home/oracle/disk4/redo03_a.log',
    '/home/oracle/disk5/redo03_b.log'
  ) SIZE 5M
-- STANDBY LOGFILE

DATAFILE
  '/home/oracle/oradata/testdb/system01.dbf',
  '/home/oracle/oradata/testdb/undotbs01.dbf',
  '/home/oracle/oradata/testdb/sysaux01.dbf',
  '/home/oracle/oradata/testdb/users01.dbf',
  '/home/oracle/oradata/testdb/example01.dbf',
  '/home/oracle/oradata/testdb/ts_a01.dbf',
  '/home/oracle/oradata/testdb/ts_b01.dbf',
  '/home/oracle/oradata/testdb/ts_c01.dbf'
CHARACTER SET KO16KSC5601
;

-- 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 '/data/arc2/1_1_562360180.arc';
-- ALTER DATABASE REGISTER LOGFILE '/data/arc2/1_1_710077424.arc';
-- ALTER DATABASE REGISTER LOGFILE '/data/arc2/1_1_713363612.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 '/home/oracle/oradata/testdb/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 "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/home/oracle/disk4/redo01_a.log',
    '/home/oracle/disk5/redo01_b.log'
  ) SIZE 5M,
  GROUP 2 (
    '/home/oracle/disk4/redo02_a.log',
    '/home/oracle/disk5/redo02_b.log'
  ) SIZE 5M,
  GROUP 3 (
    '/home/oracle/disk4/redo03_a.log',
    '/home/oracle/disk5/redo03_b.log'
  ) SIZE 5M
-- STANDBY LOGFILE

DATAFILE
  '/home/oracle/oradata/testdb/system01.dbf',
  '/home/oracle/oradata/testdb/undotbs01.dbf',
  '/home/oracle/oradata/testdb/sysaux01.dbf',
  '/home/oracle/oradata/testdb/users01.dbf',
  '/home/oracle/oradata/testdb/example01.dbf',
  '/home/oracle/oradata/testdb/ts_a01.dbf',
  '/home/oracle/oradata/testdb/ts_b01.dbf',
  '/home/oracle/oradata/testdb/ts_c01.dbf'
CHARACTER SET KO16KSC5601
;

-- 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 '/data/arc2/1_1_562360180.arc';
-- ALTER DATABASE REGISTER LOGFILE '/data/arc2/1_1_710077424.arc';
-- ALTER DATABASE REGISTER LOGFILE '/data/arc2/1_1_713363612.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 '/home/oracle/oradata/testdb/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
[oracle@ghost disk1]$ 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Datapump  (0) 2010.03.16
Export / Import - Clone DB로 삭제된 데이터 복구하는 방법  (0) 2010.03.16
Redo log file Recovery  (0) 2010.03.12
Incomplete Recovery - using backup controlfile  (0) 2010.03.11
Incomplete Recovery - Time Base  (0) 2010.03.10

Redo log file Recovery

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 12. 11:16
1. 실습 환경 설정
실습을 위해 아래와 같이 Redo log group 및 member 을 다중화 한다.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES UNUSED
         2 /home/oracle/disk5/redo02_b.log                             5 YES UNUSED
         3 /home/oracle/disk4/redo03_a.log                             5 YES UNUSED
         3 /home/oracle/disk5/redo03_b.log                             5 YES UNUSED

6 rows selected.


2. Group의 한 Member 1개 장애 (Active, Inactive 공통)
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES UNUSED
         2 /home/oracle/disk5/redo02_b.log                             5 YES UNUSED
         3 /home/oracle/disk4/redo03_a.log                             5 YES UNUSED
         3 /home/oracle/disk5/redo03_b.log                             5 YES UNUSED

6 rows selected.

SQL> !rm -rf /home/oracle/disk5/redo01_b.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 YES INACTIVE
         1 /home/oracle/disk5/redo01_b.log                             5 YES INACTIVE
         2 /home/oracle/disk4/redo02_a.log                             5 NO  CURRENT
         2 /home/oracle/disk5/redo02_b.log                             5 NO  CURRENT
         3 /home/oracle/disk4/redo03_a.log                             5 YES INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 YES INACTIVE

6 rows selected.

SQL> alter database drop logfile member '/home/oracle/disk5/redo01_b.log';

Database altered.

SQL> alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES ACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 YES ACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 YES ACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 YES ACTIVE

6 rows selected.

SQL> 

※ alert_testdb.log 내용
Fri Mar 12 11:26:22 2010
Thread 1 advanced to log sequence 7
  Current log# 2 seq# 7 mem# 0: /home/oracle/disk4/redo02_a.log
  Current log# 2 seq# 7 mem# 1: /home/oracle/disk5/redo02_b.log
Fri Mar 12 11:26:22 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:26:22 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thread 1 advanced to log sequence 8
  Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 cannot allocate new log, sequence 9
Checkpoint not complete
  Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log
Fri Mar 12 11:26:32 2010
Thread 1 advanced to log sequence 9
  Current log# 1 seq# 9 mem# 0: /home/oracle/disk4/redo01_a.log
  Current log# 1 seq# 9 mem# 1: /home/oracle/disk5/redo01_b.log
Fri Mar 12 11:27:18 2010
Thread 1 advanced to log sequence 10
  Current log# 2 seq# 10 mem# 0: /home/oracle/disk4/redo02_a.log
  Current log# 2 seq# 10 mem# 1: /home/oracle/disk5/redo02_b.log
Fri Mar 12 11:27:18 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:27:18 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thread 1 advanced to log sequence 11
  Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 cannot allocate new log, sequence 12
Checkpoint not complete
  Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 advanced to log sequence 12
  Current log# 1 seq# 12 mem# 0: /home/oracle/disk4/redo01_a.log
  Current log# 1 seq# 12 mem# 1: /home/oracle/disk5/redo01_b.log
Fri Mar 12 11:27:30 2010
Thread 1 advanced to log sequence 13
  Current log# 2 seq# 13 mem# 0: /home/oracle/disk4/redo02_a.log
  Current log# 2 seq# 13 mem# 1: /home/oracle/disk5/redo02_b.log
Fri Mar 12 11:27:30 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:27:30 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:27:30 2010
Expanded controlfile section 11 from 28 to 205 records
Requested to grow by 177 records; added 7 blocks of records
Fri Mar 12 11:27:52 2010
alter database drop logfile member '/home/oracle/disk5/redo01_b.log'
Fri Mar 12 11:27:52 2010
Completed: alter database drop logfile member '/home/oracle/disk5/redo01_b.log'
Fri Mar 12 11:28:12 2010
alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1
Fri Mar 12 11:28:12 2010
Completed: alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1
Fri Mar 12 11:28:27 2010
Thread 1 advanced to log sequence 14
  Current log# 3 seq# 14 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 14 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 advanced to log sequence 15
  Current log# 1 seq# 15 mem# 0: /home/oracle/disk4/redo01_a.log
  Current log# 1 seq# 15 mem# 1: /home/oracle/disk5/redo01_b.log


3. Inactive한 Group 장애
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES INACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 YES INACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 YES INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 YES INACTIVE

6 rows selected.

SQL> !rm -rf /home/oracle/disk4/redo03_a.log

SQL> !rm -rf /home/oracle/disk5/redo03_b.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES INACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 YES INACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 NO  INACTIVE

6 rows selected.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance testdb (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/home/oracle/disk4/redo03_a.log'
ORA-00312: online log 3 thread 1: '/home/oracle/disk5/redo03_b.log'


SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 NO  INACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 NO  INACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 NO  INACTIVE

6 rows selected.

SQL> 


저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Export / Import - Clone DB로 삭제된 데이터 복구하는 방법  (0) 2010.03.16
Control file 장애  (0) 2010.03.15
Incomplete Recovery - using backup controlfile  (0) 2010.03.11
Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09

Incomplete Recovery - using backup controlfile 연습문제 #2

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 12. 02:25
#시나리오
A. Data file, Control file을 open Backup 한다.
B. 새로운 ts_d Tablespace를 생성한다.
C. Tablespace ts_d에 scott.test Table을 생성하고 Data를 입력한다.
D. ts_d Tablespace를 삭제한다. rm 또는 drop tablespace 동일하다.
　 ※ ts_d Tablespace는 Backup 받은 Data file이 없다.

#연습문제
D에서 삭제된 ts_d Talespace를 복구한다.


#현재 Data file을 확인한다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

#모든 Data file을 Open Backup 한다.
SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter tablespace ts_a begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_a01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/open/ts_a01.dbf'

SQL> alter tablespace ts_a end backup;

Tablespace altered.

SQL> alter tablespace ts_b begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_b01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/open/ts_b01.dbf'

SQL> alter tablespace ts_b end backup;

Tablespace altered.

SQL> alter tablespace ts_c begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_c01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/open/ts_c01.dbf'

SQL> alter tablespace ts_c end backup;

Tablespace altered.

#Data file을 Open Backup 했다면, 중요한 Control file 도 Backup 한다.
SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

#시나리오와 같이 ts_d Tablespace를 생성한다.
SQL> create tablespace ts_d datafile '/home/oracle/oradata/testdb/ts_d01.dbf' size 5m;

Tablespace created.

#ts_d Tablespace에 test Table을 생성하고 Data를 입력한다.
SQL> create table scott.test(no number) tablespace ts_d;

Table created.

SQL> insert into scott.test values (1);

1 row created.

SQL> insert into scott.test values (2);

1 row created.

SQL> insert into scott.test values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

        NO
----------
         1
         2
         3

#Data까지 입력하고 ts_d Tablespace를 삭제하기 전에 복구를 위해 현재 시간을 확인한다.
#rm 이 아닌 drop tablespace ts_d including contents and datafiles; 를 사용할 경우 alter_testdb.log 파일에서 drop tablespace ts_d including contents and datafiles 했던 시간을 확인할 수 있다.
SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:02:02:12

#ts_d Tablespace를 삭제하고 삭제 되었는지 확인한다.
SQL> !rm /home/oracle/oradata/testdb/ts_d01.dbf

SQL> !ls /home/oracle/oradata/testdb/ts_*.dbf
/home/oracle/oradata/testdb/ts_a01.dbf  /home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf

#Archive log를 만들기 위해 로그 스위치를 몇 차례 발생시킨다.
#주의사항 : 실습 또는 연습문제에서 로그 스위치를 발생시키지 않을 경우 Archive log가 생성되지 않아 복구할 수 없다.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:02:04:43

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir /home/oracle/oradata/testdb/temp

#복구시 실수로 인해 기존 DB까지 사용하지 못할 수 있다. 따라서 현재의 원본 Data file, Redo log file, Control file을 /data/backup/temp 디렉토리를 생성하고 Backup 받는다.
SQL> !mkdir /data/backup/temp

SQL> !cp -av /home/oracle/oradata/testdb/* /data/backup/temp/
`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/temp/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/temp/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/temp/control03.ctl'
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/temp/example01.dbf'
`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/temp/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/temp/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/temp/redo03.log'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/temp/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/temp/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/temp/temp01.dbf'
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/temp/ts_a01.dbf'
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/temp/ts_b01.dbf'
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/temp/ts_c01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/temp/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/temp/users01.dbf'

#원본 Data file, Redo log file, Control file을 Backup 받은 후 이전에 Backup 받아놓은 Data file을 복원한다.
SQL> !cp -av /data/backup/open/*.dbf /home/oracle/oradata/testdb/
`/data/backup/open/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/open/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/open/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/open/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/open/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/open/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/open/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/open/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

#이전에 Backup 받아놓은 Control file을 복원한다.
SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control01.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'

SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control02.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'

SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control03.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

#복구를 위해 mount 단계로 시작한다.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> 
#ts_d Tablespace에 문제(삭제)가 발생하기 이전 시간으로 복구한다.
SQL> recover database until time '2010-03-12:02:02:12' using backup controlfile;
ORA-00279: change 505394 generated at 03/12/2010 01:55:53 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 505394 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 9: '/home/oracle/oradata/testdb/ts_d01.dbf'


ORA-01112: media recovery not started

#ts_d01.dbf가 존재하지 않아 오류가 발생한다. 따라서 ts_d01.dbf를 복원해야 한다. 아래와 같이 현재 Data file을 재 확인하면 새로운 Data file("/home/oracle/product/10g/dbs/UNNAMED00009) 목록이 확인된다.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/product/10g/dbs/UNNAMED00009

9 rows selected.

#기존 Data file인 ts_d01.dbf를 복원하기 위해 아래와 같이 실행한다.
SQL> alter database create datafile 'UNNAMED00009' as '/home/oracle/oradata/testdb/ts_d01.dbf';

Database altered.

#위 과정을 통해 현재 Data file을 재 확인해 보면 ts_d01.dbf 파일로 변경된 것을 확인할 수 있다.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/oradata/testdb/ts_d01.dbf

9 rows selected.

#ts_d01.dbf가 복원 되었으니 다시한번 ts_d Tablespace에 문제가 발생하기 이전 시간으로 복구한다.
SQL> recover database until time '2010-03-12:02:02:12' using backup controlfile;               
ORA-00279: change 508678 generated at 03/12/2010 02:00:53 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 508678 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
#복구가 완료된 후 resetlogs 옵션을 이용해 open 단계로 변경한다.
SQL> alter database open resetlogs;

Database altered.

#select 문을 이용해 복구 여부를 확인한다.
SQL> select * from scott.test;

        NO
----------
         1
         2
         3

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Export / Import 종합 연습 문제 #1  (0) 2010.03.15
Control file 장애 종합 연습 문제  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10

Incomplete Recovery - using backup controlfile 연습문제 #1

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 11. 20:55
#시나리오
A. DB를 전체 백업한다.
B. ts_d Tablesapce 생성하고, Control file을 Backup 한다.
C. ts_d에 scott.test Table을 생성하고 Data를 입력한다.
D. scott.test Table을 Drop 한다.
E. ts_d Tablespace를 Drop 한다.
F. 현재 시점

#연습문제
D 시점에서 삭제된 scott.test Table을 복구한다.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/close/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/close/example01.dbf'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/close/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/close/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/close/temp01.dbf'
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/close/ts_a01.dbf'
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/close/ts_b01.dbf'
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/close/ts_c01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/close/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/close/users01.dbf'

SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/close/
`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/close/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/close/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/close/control03.ctl'

SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/close/
`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/close/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/close/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/close/redo03.log'

SQL> !cp -av $ORACLE_HOME/dbs /data/backup/close/
`/home/oracle/product/10g/dbs' -> `/data/backup/close/dbs'
`/home/oracle/product/10g/dbs/orapwtestdb' -> `/data/backup/close/dbs/orapwtestdb'
`/home/oracle/product/10g/dbs/init.ora' -> `/data/backup/close/dbs/init.ora'
`/home/oracle/product/10g/dbs/initdw.ora' -> `/data/backup/close/dbs/initdw.ora'
`/home/oracle/product/10g/dbs/spfiletestdb.ora' -> `/data/backup/close/dbs/spfiletestdb.ora'
`/home/oracle/product/10g/dbs/lkTESTDB' -> `/data/backup/close/dbs/lkTESTDB'
`/home/oracle/product/10g/dbs/hc_testdb.dat' -> `/data/backup/close/dbs/hc_testdb.dat'

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> create tablespace ts_d datafile '/home/oracle/oradata/testdb/ts_d01.dbf' size 5m;

Tablespace created.

SQL> alter database backup controlfile to '/data/backup/close/control01.bak';

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf
TS_D                          5 /home/oracle/oradata/testdb/ts_d01.dbf

9 rows selected.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:01:04:04

SQL> create table scott.test(no number) tablespace ts_d;

Table created.

SQL> insert into scott.test values (1);

1 row created.

SQL> insert into scott.test values (2);

1 row created.

SQL> insert into scott.test values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

        NO
----------
         1
         2
         3

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:01:04:25

SQL> drop table scott.test purge;

Table dropped.

SQL> alter system switch logfile;

System altered.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:01:05:54

SQL> drop tablespace ts_d including contents and datafiles;

Tablespace dropped.

SQL> alter system switch logfile;

System altered.

SQL> select * from scott.test;
select * from scott.test
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir -p /home/oracle/oradata/testdb/temp

SQL> !cp -av /home/oracle/oradata/testdb/*.* /home/oracle/oradata/testdb/temp/
`/home/oracle/oradata/testdb/control01.ctl' -> `/home/oracle/oradata/testdb/temp/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/home/oracle/oradata/testdb/temp/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/home/oracle/oradata/testdb/temp/control03.ctl'
`/home/oracle/oradata/testdb/example01.dbf' -> `/home/oracle/oradata/testdb/temp/example01.dbf'
`/home/oracle/oradata/testdb/redo01.log' -> `/home/oracle/oradata/testdb/temp/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/home/oracle/oradata/testdb/temp/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/home/oracle/oradata/testdb/temp/redo03.log'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/home/oracle/oradata/testdb/temp/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/home/oracle/oradata/testdb/temp/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/home/oracle/oradata/testdb/temp/temp01.dbf'
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/home/oracle/oradata/testdb/temp/ts_a01.dbf'
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/home/oracle/oradata/testdb/temp/ts_b01.dbf'
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/home/oracle/oradata/testdb/temp/ts_c01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/home/oracle/oradata/testdb/temp/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/home/oracle/oradata/testdb/temp/users01.dbf'

SQL> !cp -av /data/backup/close/*.dbf /home/oracle/oradata/testdb/
`/data/backup/close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'
`/data/backup/close/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/close/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/close/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> !cp /data/backup/close/control01.bak /home/oracle/oradata/testdb/control01.ctl

SQL> !cp /data/backup/close/control01.bak /home/oracle/oradata/testdb/control02.ctl

SQL> !cp /data/backup/close/control01.bak /home/oracle/oradata/testdb/control03.ctl

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/oradata/testdb/ts_d01.dbf

9 rows selected.

※ 명령어 확인 !!!
SQL> alter database create




SQL> recover database until time '2010-03-12:01:04:25' using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 9: '/home/oracle/oradata/testdb/ts_d01.dbf'
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/home/oracle/oradata/testdb/ts_d01.dbf'


SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Control file 장애 종합 연습 문제  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #2  (0) 2010.03.12
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10

Incomplete Recovery - using backup controlfile

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 11. 12:36
실습 순서
1. Tablespace 생성 후 DB 전체를 Backup 한다.
2. test 테이블을 1번에서 생성한 tablespace에 생성한다.
3. drop tablespace로 1번에서 생성한 tablespace를 삭제한다.
4. 로그스위치를 수차례 발생시킨다.
5. 1번 과정에서 Backup 받았던 Data files과 Control files를 복원한다.
6. alert_testdb.log 파일에서 3번 작업을 수행했던 시간을 확인한다.
7. DB를 mount 단계로 시작한다.
8. recover database until time 'YYYY-MM-DD:HH24:MI:SS' using backup controlfile; 실행한다.
9. 8번 실행 후 alter database open resetlogs; 실행한다.
10. 복구가 완료 되었는지 확인한다.

SQL> create table scott.test(no number) tablespace ts_c;

Table created.

SQL> insert into scott.test values (1);

1 row created.

SQL> insert into scott.test values (2);

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from scott.test;

        NO
----------
         1
         2

SQL> drop tablespace ts_c including contents and datafiles;

Tablespace dropped.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

#복구 중 실수로 문제가 발생한 DB 이외에 기존 DB 까지 사용하지 못하는 경우가 발생될 수 있다.
#따라서 문제가 발생한 Data file까지 포함해 모든 Data file, Redo log file, Control file까지 Backup을 받아놓는다.
SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/temp
SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/temp
SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/temp

#위와 같이 Data file, Redo log file, Control file까지 Backup을 받아놓은 후 아래와 같이 복구를 시작한다.
SQL> !cp -av /data/backup/cr_close/*.dbf /home/oracle/oradata/testdb/
`/data/backup/cr_close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/cr_close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/cr_close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/cr_close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'
`/data/backup/cr_close/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/cr_close/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/cr_close/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/cr_close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/cr_close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> !cp -av /data/backup/cr_close/*.ctl /home/oracle/oradata/testdb/
`/data/backup/cr_close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'
`/data/backup/cr_close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'
`/data/backup/cr_close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.

#alert_testdb.log 에서 drop tablespace ts_c including contents and datafiles 작업 수행 시간을 확인한다.
#/home/oracle/admin/testdb/bdump/alert_testdb.log 내용 중 일부
Thu Mar 11 12:27:55 2010
drop tablespace ts_c including contents and datafiles
Thu Mar 11 12:27:58 2010
Deleted file /home/oracle/oradata/testdb/ts_c01.dbf
Completed: drop tablespace ts_c including contents and datafiles

SQL> recover database until time '2010-03-11:12:27:50' using backup controlfile;
ORA-00279: change 502969 generated at 03/11/2010 11:14:17 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 502969 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.test;

        NO
----------
         1
         2

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Control file 장애  (0) 2010.03.15
Redo log file Recovery  (0) 2010.03.12
Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09
No Archive log mode Recovery  (0) 2010.03.08

Incomplete Recovery - Time Base 연습문제 2

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 10. 12:11
drop user howkey cascade; 로 사용자를 삭제하고 select로 조회하면 jmember, jumsu Table이 삭제된 것을 확인할수 있다. 사용자를 삭제하기 전 상태로 복구한다.

SQL> create user howkey
  2  identified by howkey
  3  default tablespace ts_a
  4  temporary tablespace temp
  5  quota unlimited on ts_a;

User created.

SQL> grant connect, resource to howkey;

Grant succeeded.

SQL> conn howkey/howkey;
Connected.
SQL> create table jmember
  2  (  id      varchar2(10),
  3     name    varchar2(10),
  4     tel     varchar2(10));

Table created.

SQL> create table jumsu
  2  (  sub_name        varchar2(10),
  3     sub_prof        varchar2(10),
  4     sub_jumsu       number);

Table created.

SQL> insert into jmember values('kara1', '박규리', '1111-1111');

1 row created.

SQL> insert into jmember values('kara2', '한승연', '2222-2222');

1 row created.

SQL> insert into jmember values('kara3', '정니콜', '3333-3333');

1 row created.

SQL> insert into jmember values('kara4', '구하라', '4444-4444');

1 row created.

SQL> insert into jmember values('kara5', '강지영', '5555-5555');

1 row created.

SQL> insert into jumsu values('사회', '강석호', 100);

1 row created.

SQL> insert into jumsu values('수학', '차기봉', 99);

1 row created.

SQL> insert into jumsu values('영어', '앤써니양', 96);

1 row created.

SQL> insert into jumsu values('국어', '이은유', 93);

1 row created.

SQL> insert into jumsu values('과학', '장영식', 90);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from jmember;

ID         NAME       TEL
---------- ---------- ----------
kara1      박규리     1111-1111
kara2      한승연     2222-2222
kara3      정니콜     3333-3333
kara4      구하라     4444-4444
kara5      강지영     5555-5555

SQL> select * from jumsu;

SUB_NAME   SUB_PROF    SUB_JUMSU
---------- ---------- ----------
사회       강석호            100
수학       차기봉             99
영어       앤써니양           96
국어       이은유             93
과학       장영식             90

#recover database until time에 적용할 시간을 확인한다.
SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-11:17:40:19

SQL> conn / as sysdba
Connected.
#연습문제 내용과 같이 cascade 옵션을 사용해 howkey 계정을 삭제한다.
SQL> drop user howkey cascade;

User dropped.

#위에서 howkey 계정을 삭제했기 때문에 접속할 수 없다.
SQL> conn howkey/howkey
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
#복구를 위해 shutdown 한다.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
#복구를 위해 Backup 받아놓은 Data file만 복사한다.
SQL> !cp -av /data/backup/cr_close/*.dbf /home/oracle/oradata/testdb/
`/data/backup/cr_close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/cr_close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/cr_close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/cr_close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'
`/data/backup/cr_close/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/cr_close/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/cr_close/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/cr_close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/cr_close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
#위에서 확인해 두었던 시간을 적용해 recover database until time 명령어를 실행하고 DB를 open한다.
SQL> recover database until time '2010-03-11:17:40:19';
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

#복구 여부를 확인하기 위해 삭제했던 howkey 계정으로 접속하고 select 문을 이용해 Data를 확인한다.
SQL> conn howkey/howkey
Connected.
SQL> select * from jmember;

ID         NAME       TEL
---------- ---------- ----------
kara1      박규리     1111-1111
kara2      한승연     2222-2222
kara3      정니콜     3333-3333
kara4      구하라     4444-4444
kara5      강지영     5555-5555

SQL> select * from jumsu;

SUB_NAME   SUB_PROF    SUB_JUMSU
---------- ---------- ----------
사회       강석호            100
수학       차기봉             99
영어       앤써니양           96
국어       이은유             93
과학       장영식             90

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - using backup controlfile 연습문제 #2  (0) 2010.03.12
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09

Incomplete Recovery - Time Base 연습문제 1

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 10. 12:08
update 실수! update 이전 시점으로 복구하기

SQL> create table member
  2  (  no              number,
  3     name            varchar2(10),
  4     address         varchar2(10),
  5     hiredate        date)
  6  tablespace ts_c;

Table created.

SQL> insert into member values(1, '박규리', '서울', sysdate);

1 row created.

SQL> insert into member values(2, '한승연', '서울', sysdate);

1 row created.

SQL> insert into member values(3, '정니콜', '미국', sysdate);

1 row created.

SQL> insert into member values(4, '구하라', '광주', sysdate);

1 row created.

SQL> insert into member values(5, '강지영', '경기도', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select no, name, address, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from member;

        NO NAME       ADDRESS    HIREDATE
---------- ---------- ---------- -------------------
         1 박규리     서울       2010-03-10:11:45:24
         2 한승연     서울       2010-03-10:11:45:28
         3 정니콜     미국       2010-03-10:11:45:31
         4 구하라     광주       2010-03-10:11:45:34
         5 강지영     경기도     2010-03-10:11:45:36

SQL> update member set address='대한민국';

5 rows updated.

SQL> select no, name, address, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from member;

        NO NAME       ADDRESS    HIREDATE
---------- ---------- ---------- -------------------
         1 박규리     대한민국   2010-03-10:11:45:24
         2 한승연     대한민국   2010-03-10:11:45:28
         3 정니콜     대한민국   2010-03-10:11:45:31
         4 구하라     대한민국   2010-03-10:11:45:34
         5 강지영     대한민국   2010-03-10:11:45:36

SQL> commit;

Commit complete.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-10:11:57:33

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /data/backup/open/*.dbf /home/oracle/oradata/testdb/
`/data/backup/open/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/open/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/open/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/open/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/open/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/open/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/open/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/open/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             121636432 bytes
Database Buffers          159383552 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover database until time '2010-03-10:11:50:00';
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select no, name, address, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from member;

        NO NAME       ADDRESS    HIREDATE
---------- ---------- ---------- -------------------
         1 박규리     서울       2010-03-10:11:45:24
         2 한승연     서울       2010-03-10:11:45:28
         3 정니콜     미국       2010-03-10:11:45:31
         4 구하라     광주       2010-03-10:11:45:34
         5 강지영     경기도     2010-03-10:11:45:36

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09
No Archive log mode Recovery 연습문제  (0) 2010.03.09

Incomplete Recovery - Time Base

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 10. 11:00

SQL> create table test7 (no number, hiredate date) tablespace ts_c;

Table created.

SQL> insert into test7 values (1, sysdate);

1 row created.

SQL> insert into test7 values (2, sysdate);

1 row created.

SQL> insert into test7 values (3, sysdate);

1 row created.

SQL> insert into test7 values (4, sysdate);

1 row created.

SQL> insert into test7 values (5, sysdate);

1 row created.

#commit 하지 않은 insert data는 복구가 불가능하다.
SQL> commit;

Commit complete.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-10:10:58:07

SQL> drop table test7;

Table dropped.

SQL> select * from test7;
select * from test7
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir /data/temp_time

SQL> !cp -av /data/backup/open/*.dbf /data/temp_time/
`/data/backup/open/example01.dbf' -> `/data/temp_time/example01.dbf'
`/data/backup/open/sysaux01.dbf' -> `/data/temp_time/sysaux01.dbf'
`/data/backup/open/system01.dbf' -> `/data/temp_time/system01.dbf'
`/data/backup/open/ts_a01.dbf' -> `/data/temp_time/ts_a01.dbf'
`/data/backup/open/ts_b01.dbf' -> `/data/temp_time/ts_b01.dbf'
`/data/backup/open/ts_c01.dbf' -> `/data/temp_time/ts_c01.dbf'
`/data/backup/open/undotbs01.dbf' -> `/data/temp_time/undotbs01.dbf'
`/data/backup/open/users01.dbf' -> `/data/temp_time/users01.dbf'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             113247824 bytes
Database Buffers          167772160 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/data/temp_time/system01.dbf';

Database altered.

SQL> 

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/sysaux01.dbf'
  3  to '/data/temp_time/sysaux01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/undotbs01.dbf'
  3  to '/data/temp_time/undotbs01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/users01.dbf'
  3  to '/data/temp_time/users01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/example01.dbf'
  3  to '/data/temp_time/example01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/ts_a01.dbf'
  3  to '/data/temp_time/ts_a01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/ts_b01.dbf'
  3  to '/data/temp_time/ts_b01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/ts_c01.dbf'
  3  to '/data/temp_time/ts_c01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/data/temp_time/system01.dbf
/data/temp_time/undotbs01.dbf
/data/temp_time/sysaux01.dbf
/data/temp_time/users01.dbf
/data/temp_time/example01.dbf
/data/temp_time/ts_a01.dbf
/data/temp_time/ts_b01.dbf
/data/temp_time/ts_c01.dbf

8 rows selected.

SQL> recover database until time '2010-03-10:10:58:07';
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select no, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from test7;

        NO HIREDATE
---------- -------------------
         1 2010-03-10:10:56:18
         2 2010-03-10:10:56:20
         3 2010-03-10:10:56:24
         4 2010-03-10:10:56:26
         5 2010-03-10:10:56:28

SQL> 


#실습문제 설명
10:01:00 insert into test7 values (1, sysdate);
10:02:00 insert into test7 values (2, sysdate);
10:03:00 insert into test7 values (3, sysdate);
10:03:30 commit;
10:04:00 insert into test7 values (4, sysdate);
10:05:00 insert into test7 values (5, sysdate);

recover database until time '2010-03-10:10:03:15';
-> commit; 이전으로 복구하는 부분이기 때문에 모든 데이터는 복구되지 않는다.
recover database until time '2010-03-10:10:03:45';
-> commot; 이전 1, 2, 3 데이터만 복구가 되며, commit; 이후 데이터는 복구되지 않는다.

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Redo log file Recovery  (0) 2010.03.12
Incomplete Recovery - using backup controlfile  (0) 2010.03.11
Archive log mode Complete Recovery  (0) 2010.03.09
No Archive log mode Recovery  (0) 2010.03.08
Cold Backup & Hot Backup  (0) 2010.03.08

Complete Recovery (다른 경로에 응급 복구)

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 10. 03:41
현재 서버의 모든 파일들을 아래와 같이 구성하기
/data/data1/system
/data/data2/sysaux, undotbs
/data/data3/users, example, ts_a, ts_b, ts_c
/data/data4/control01.ctl, redo1, redo2, redo3
/data/data5/control02.ctl, redo1, redo2, redo3

/data/data1, data2, data3을 각각 삭제하고 하나씩 복구하기
복구가 완료되면 모든 파일들을 /home/oracle/oradata/testdb 로 이동한다.


저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09
No Archive log mode Recovery 연습문제  (0) 2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)  (0) 2010.02.28

Complete Recovery 연습 문제

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 9. 12:13
#1 /home/oracle/oradata/testdb/undotbs01.dbf를 삭제하고 복구하기

SQL> !rm -rf /home/oracle/oradata/testdb/undotbs01.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/testdb/undotbs01.dbf'


SQL> !cp /data/backup/open/undotbs01.dbf /home/oracle/oradata/testdb/

SQL> recover datafile '/home/oracle/oradata/testdb/undotbs01.dbf';
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> 



#2 ts_a, ts_b, ts_c에 각각 test5, test6, test7 Table을 만들고 Data를 입력한 후 각 Data file을 삭제하고 3개의 Tablespace 전부 복구하기

SQL> create table test5 (no number) tablespace ts_a;

Table created.

SQL> insert into test5 values (5);

1 row created.

SQL> commit;  

Commit complete.

SQL> select * from test5;

        NO
----------
         5

SQL> create table test6 (no number) tablespace ts_b;

Table created.

SQL> insert into test6 values (6);

1 row created.

SQL> commit;  

Commit complete.

SQL> select * from test6;

        NO
----------
         6

SQL> create table test7 (no number) tablespace ts_c;

Table created.

SQL> insert into test7 values (7);

1 row created.

SQL> commit;  

Commit complete.

SQL> select * from test7;

        NO
----------
         7

SQL> !rm /home/oracle/oradata/testdb/ts_a01.dbf

SQL> !rm /home/oracle/oradata/testdb/ts_b01.dbf

SQL> !rm /home/oracle/oradata/testdb/ts_c01.dbf

SQL> !ls /home/oracle/oradata/testdb/ts_*
ls: /home/oracle/oradata/testdb/ts_*: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/oradata/testdb/ts_a01.dbf'


SQL> !cp /data/backup/open/ts_a01.dbf /home/oracle/oradata/testdb/

SQL> !cp /data/backup/open/ts_b01.dbf /home/oracle/oradata/testdb/

SQL> !cp /data/backup/open/ts_c01.dbf /home/oracle/oradata/testdb/

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from test5;

        NO
----------
         5

SQL> select * from test6;

        NO
----------
         6

SQL> select * from test7;

        NO
----------
         7

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
No Archive log mode Recovery 연습문제  (0) 2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)  (0) 2010.02.28
종합 연습문제 (Controlfile, Redo log file, Tablespace)  (0) 2010.02.27

티스토리툴바