DR 전환 시 DB Recovery(1. 아카이브 존재 시, 2. 히든파라미터)

# DB Recovery (Archivelog 파일 존재할 시)

1.     복구 데이터를 Copy 하기에 앞서 아래 디렉토리 생성(소요시간 : 1)

mkdir -p /orcl11/sysdata/orcl1/

mkdir -p /orcl11/ora_data/

mkdir -p /orcl12/ora_data/

mkdir -p /orcl13/ora_data/

mkdir -p /orcl14/ora_data/

mkdir -p /orcl13/bradmark/

mkdir -p /orcl14/archive

mkdir -p /export/home/orcl1/admin/orcl1/adump

mkdir -p /export/home/orcl1/admin/orcl1/bdump

mkdir -p /export/home/orcl1/admin/orcl1/cdump

mkdir -p /export/home/orcl1/admin/orcl1/udump

 

2.     DAT TAPE에서 dbf 파일을 Restore 한 후 아래 cp 명령어 실행(Local à DBF 위치로 Copy소요시간 : 25)

cp users01.dbf /orcl11/sysdata/orcl1/users01.dbf

cp TEST_IDX_NEW01.dbf /orcl11/ora_data/TEST_IDX_NEW01.dbf

cp undotbs01.dbf /orcl11/sysdata/orcl1/undotbs01.dbf

cp system01.dbf /orcl11/sysdata/orcl1/system01.dbf

cp ORCL1_COS_DAT01.dbf /orcl11/ora_data/ORCL1_TEST_DAT01.dbf

cp ORCL1_TEST_DAT02.dbf /orcl12/ora_data/ORCL1_TEST_DAT02.dbf

cp ORCL1_TEST_DAT03.dbf /orcl13/ora_data/ORCL1_TEST_DAT03.dbf

cp ORCL1_TEST_DAT04.dbf /orcl14/ora_data/ORCL1_TEST_DAT04.dbf

cp ORCL1_TEST_DAT05.dbf /orcl11/ora_data/ORCL1_TEST_DAT05.dbf

cp ORCL1_TEST_DAT06.dbf /orcl12/ora_data/ORCL1_TEST_DAT06.dbf

cp ORCL1_TEST_DAT07.dbf /orcl13/ora_data/ORCL1_TEST_DAT07.dbf

cp ORCL1_TEST_IDX01.dbf /orcl11/ora_data/ORCL1_TEST_IDX01.dbf

cp ORCL1_TEST_IDX02.dbf /orcl12/ora_data/ORCL1_TEST_IDX02.dbf

cp ORCL1_TEST_IDX03.dbf /orcl13/ora_data/ORCL1_TEST_IDX03.dbf

cp ORCL1_FORE_DAT01.dbf /orcl11/ora_data/ORCL1_FORE_DAT01.dbf

cp ORCL1_FORE_IDX01.dbf /orcl12/ora_data/ORCL1_FORE_IDX01.dbf

cp ORCL1_FORE_IDX02.dbf /orcl13/ora_data/ORCL1_FORE_IDX02.dbf

cp ORCL1_FORE_IDX03.dbf /orcl14/ora_data/ORCL1_FORE_IDX03.dbf

cp AIOMS_DAT01.dbf /orcl14/ora_data/TEST_DAT01.dbf

cp TEST_IDX101.dbf /orcl14/ora_data/TEST_IDX101.dbf

cp TEST_DAT01.dbf /orcl11/ora_data/TEST_DAT01.dbf

cp TEST_DAT02.dbf /orcl12/ora_data/TEST_DAT02.dbf

cp TEST_DAT03.dbf /orcl13/ora_data/TEST_DAT03.dbf

cp sysaux01.dbf /orcl11/sysdata/orcl1/sysaux01.dbf

cp TEST_DAT_NEW01.dbf /orcl11/ora_data/TEST_DAT_NEW01.dbf

cp bradmark.dbf /orcl13/bradmark/bradmark.dbf

 

 

3.     백업 받은 pfile을 이용하여 nomount 단계로 만든다. (소요시간 : 5)

$vi initorcl1.ora

orcl1.__db_cache_size=402653184

orcl1.__java_pool_size=4194304

orcl1.__large_pool_size=4194304

orcl1.__shared_pool_size=620756992

orcl1.__streams_pool_size=8388608

*._gby_hash_aggregation_enabled=TRUE

*.audit_file_dest=’/export/home/orcl1/admin/orcl1/adump’

*.background_dump_dest=’/export/home/orcl1/admin/orcl1/bdump’

*.compatible=’10.2.0.3.0′

*.control_files=’/orcl11/sysdata/orcl1/control01.ctl’,’/orcl12/sysdata/orcl1/control02.ctl’,’/orcl13/sysdata/orcl1/control03.ctl’

*.core_dump_dest=’/export/home/orcl1/admin/orcl1/cdump’

*.db_block_size=8192

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_name=’orcl1′

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1XDB)’

*.job_queue_processes=10

*.log_archive_dest_1=’location=/orcl14/archive’

*.log_archive_format=’%t_%s_%r.arc’

*.open_cursors=300

*.optimizer_features_enable=’10.2.0.4′

*.pga_aggregate_target=629145600

*.processes=1000

*.remote_login_passwordfile=’EXCLUSIVE’

*.sessions=1105

*.sga_target=1048576000

*.undo_management=’AUTO’

*.undo_tablespace=’UNDOTBS1′

*.user_dump_dest=’/export/home/orcl1/admin/orcl1/udump’

 

SQL>startup pfile=’/export/home/orcl1/product/10.2.0/dbs/initorcl1.ora’ nomount;

4.     백업 받은 Controlfile 재생성 스크립트를 이용하여 아래 부분만 편집하여 쿼리를 수행(소요시간 : 5)

SQL>CREATE CONTROLFILE REUSE DATABASE “ORCL1” RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 2160

LOGFILE

  GROUP 1 (

    ‘/orcl11/sysdata/orcl1/redo01a.log’,

    ‘/orcl12/sysdata/orcl1/redo01b.log’

  ) SIZE 100M,

  GROUP 2 (

    ‘/orcl11/sysdata/orcl1/redo02a.log’,

    ‘/orcl12/sysdata/orcl1/redo02b.log’

  ) SIZE 100M,

  GROUP 3 (

    ‘/orcl11/sysdata/orcl1/redo03a.log’,

    ‘/orcl12/sysdata/orcl1/redo03b.log’

  ) SIZE 100M

DATAFILE

  ‘/orcl11/sysdata/orcl1/system01.dbf’,

  ‘/orcl11/sysdata/orcl1/undotbs01.dbf’,

  ‘/orcl11/sysdata/orcl1/sysaux01.dbf’,

  ‘/orcl11/sysdata/orcl1/users01.dbf’,

  ‘/orcl11/ora_data/ORCL1_TEST_DAT01.dbf’,

  ‘/orcl12/ora_data/ORCL1_TEST_DAT02.dbf’,

  ‘/orcl13/ora_data/ORCL1_TEST_DAT03.dbf’,

  ‘/orcl14/ora_data/ORCL1_TEST_DAT04.dbf’,

  ‘/orcl11/ora_data/ORCL1_TEST_DAT05.dbf’,

  ‘/orcl12/ora_data/ORCL1_TEST_DAT06.dbf’,

  ‘/orcl13/ora_data/ORCL1_TEST_DAT07.dbf’,

  ‘/orcl11/ora_data/ORCL1_TEST_IDX01.dbf’,

  ‘/orcl12/ora_data/ORCL1_TEST_IDX02.dbf’,

  ‘/orcl13/ora_data/ORCL1_TEST_IDX03.dbf’,

  ‘/orcl11/ora_data/ORCL1_FORE_DAT01.dbf’,

  ‘/orcl12/ora_data/ORCL1_FORE_IDX01.dbf’,

  ‘/orcl13/ora_data/ORCL1_FORE_IDX02.dbf’,

  ‘/orcl14/ora_data/ORCL1_FORE_IDX03.dbf’,

  ‘/orcl14/ora_data/TEST_DAT01.dbf’,

  ‘/orcl14/ora_data/TEST_IDX101.dbf’,

  ‘/orcl11/ora_data/TEST_DAT01.dbf’,

  ‘/orcl12/ora_data/TEST_DAT02.dbf’,

  ‘/orcl13/ora_data/TEST_DAT03.dbf’,

  ‘/orcl13/bradmark/bradmark.dbf’,

  ‘/orcl11/ora_data/TEST_DAT_NEW01.dbf’,

  ‘/orcl11/ora_data/TEST_IDX_NEW01.dbf’

CHARACTER SET US7ASCII

; ß 이 부분 까지만 수행

 

— 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 ‘/orcl14/archive/1_1_651132086.arc’;

— ALTER DATABASE REGISTER LOGFILE ‘/orcl14/archive/1_1_716140947.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 ‘/orcl11/sysdata/orcl1/temp01.dbf’

     SIZE 7000M REUSE AUTOEXTEND ON NEXT 10485760  MAXSIZE 32767M;

— End of tempfile additions.

  

5.     재생성이 완료되었으면 Recovery 작업을 수행합니다. 빨간 부분의 경로에 Archive를 위치한 후 Enter를 입력합니다.(아카이브로그 한 개씩 적용, 복구 됐을 시 현재 시점을 알기 위해서) (소요시간 :  )

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 2034211841 generated at 06/12/2014 04:00:01 needed for thread1

ORA-00289: suggestion : /orcl14/archive/1_26093_716140947.arc

ORA-00280: change 2034211841 for thread 1 is in sequence #26093

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

엔터 입력

 

6.     Recovery 완료 후 v$datafile에서 status를 확인 합니다.(SYSEM status SYSTEM으로 표시됩니다. 나머지 dbf ONLINE이어야 합니다.) (소요시간 : 1)

SQL> select name,status from v$datafile;

 

7.     DBOPEN 합니다. (소요시간 : 5)

SQL>alter database open resetlogs;

 

8.     TEMP Tablespace를 생성하여 줍니다.( 위에서 Controlfile 생성 스크립트에 있는 내용) (소요시간 : 3)

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/orcl11/sysdata/orcl1/temp01.dbf’

     SIZE 7000M REUSE AUTOEXTEND ON NEXT 10485760  MAXSIZE 32767M;


 

 

# DB Recovery (Archivelog 파일 유실 시, 히든 파라미터 사용)

 * 이 방법은 Oracle에서 보장하지 않는 방법으로 정합성이 맞지 않을 수 있고 오 동작을 일으킬 수 있습니다.

 

1.     복구 데이터를 Copy 하기에 앞서 아래 디렉토리 생성(소요시간 : 1)

mkdir -p /orcl11/sysdata/orcl1/

mkdir -p /orcl11/ora_data/

mkdir -p /orcl12/ora_data/

mkdir -p /orcl13/ora_data/

mkdir -p /orcl14/ora_data/

mkdir -p /orcl13/bradmark/

mkdir -p /orcl14/archive

mkdir -p /export/home/orcl1/admin/orcl1/adump

mkdir -p /export/home/orcl1/admin/orcl1/bdump

mkdir -p /export/home/orcl1/admin/orcl1/cdump

mkdir -p /export/home/orcl1/admin/orcl1/udump

 

2.     DAT TAPE에서 dbf 파일을 Restore 한 후 아래 cp 명령어 실행(Local à DBF 위치로 Copy소요시간 : 25)

cp users01.dbf /orcl11/sysdata/orcl1/users01.dbf

cp TEST_IDX_NEW01.dbf /orcl11/ora_data/TEST_IDX_NEW01.dbf

cp undotbs01.dbf /orcl11/sysdata/orcl1/undotbs01.dbf

cp system01.dbf /orcl11/sysdata/orcl1/system01.dbf

cp ORCL1_TEST_DAT01.dbf /orcl11/ora_data/ORCL1_TEST_DAT01.dbf

cp ORCL1_TEST_DAT02.dbf /orcl12/ora_data/ORCL1_TEST_DAT02.dbf

cp ORCL1_TEST_DAT03.dbf /orcl13/ora_data/ORCL1_TEST_DAT03.dbf

cp ORCL1_TEST_DAT04.dbf /orcl14/ora_data/ORCL1_TEST_DAT04.dbf

cp ORCL1_TEST_DAT05.dbf /orcl11/ora_data/ORCL1_TEST_DAT05.dbf

cp ORCL1_TEST_DAT06.dbf /orcl12/ora_data/ORCL1_TEST_DAT06.dbf

cp ORCL1_TEST_DAT07.dbf /orcl13/ora_data/ORCL1_TEST_DAT07.dbf

cp ORCL1_TEST_IDX01.dbf /orcl11/ora_data/ORCL1_TEST_IDX01.dbf

cp ORCL1_TEST_IDX02.dbf /orcl12/ora_data/ORCL1_TEST_IDX02.dbf

cp ORCL1_TEST_IDX03.dbf /orcl13/ora_data/ORCL1_TEST_IDX03.dbf

cp ORCL1_FORE_DAT01.dbf /orcl11/ora_data/ORCL1_FORE_DAT01.dbf

cp ORCL1_FORE_IDX01.dbf /orcl12/ora_data/ORCL1_FORE_IDX01.dbf

cp ORCL1_FORE_IDX02.dbf /orcl13/ora_data/ORCL1_FORE_IDX02.dbf

cp ORCL1_FORE_IDX03.dbf /orcl14/ora_data/ORCL1_FORE_IDX03.dbf

cp TEST_DAT01.dbf /orcl14/ora_data/TEST_DAT01.dbf

cp TEST_IDX101.dbf /orcl14/ora_data/TEST_IDX101.dbf

cp TEST_DAT01.dbf /orcl11/ora_data/TEST_DAT01.dbf

cp TEST_DAT02.dbf /orcl12/ora_data/TEST_DAT02.dbf

cp TEST_DAT03.dbf /orcl13/ora_data/TEST_DAT03.dbf

cp sysaux01.dbf /orcl11/sysdata/orcl1/sysaux01.dbf

cp TEST_DAT_NEW01.dbf /orcl11/ora_data/TEST_DAT_NEW01.dbf

cp bradmark.dbf /orcl13/bradmark/bradmark.dbf

 

3.     백업 받은 pfile에 히든 파라미터를 기입한다. 그리고 nomount 단계로 만든다. (소요시간 : 5)

$vi initorcl1.ora

orcl1.__db_cache_size=402653184

orcl1.__java_pool_size=4194304

orcl1.__large_pool_size=4194304

orcl1.__shared_pool_size=620756992

orcl1.__streams_pool_size=8388608

*._gby_hash_aggregation_enabled=TRUE

*.audit_file_dest=’/export/home/orcl1/admin/orcl1/adump’

*.background_dump_dest=’/export/home/orcl1/admin/orcl1/bdump’

*.compatible=’10.2.0.3.0′

*.control_files=’/orcl11/sysdata/orcl1/control01.ctl’,’/orcl12/sysdata/orcl1/control02.ctl’,’/orcl13/sysdata/orcl1/control03.ctl’

*.core_dump_dest=’/export/home/orcl1/admin/orcl1/cdump’

*.db_block_size=8192

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_name=’orcl1′

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1XDB)’

*.job_queue_processes=10

*.log_archive_dest_1=’location=/orcl14/archive’

*.log_archive_format=’%t_%s_%r.arc’

*.open_cursors=300

*.optimizer_features_enable=’10.2.0.4′

*.pga_aggregate_target=629145600

*.processes=1000

*.remote_login_passwordfile=’EXCLUSIVE’

*.sessions=1105

*.sga_target=1048576000

*.undo_management=’AUTO’

*.undo_tablespace=’UNDOTBS1′

*.user_dump_dest=’/export/home/orcl1/admin/orcl1/udump’

*._corrupted_rollback_segments”=true

 

SQL>startup pfile=’/export/home/orcl1/product/10.2.0/dbs/initorcl1.ora’ nomount;

4.     백업 받은 Controlfile 재생성 스크립트를 이용하여 아래 부분만 편집하여 쿼리를 수행(소요시간 : 5)

SQL>CREATE CONTROLFILE REUSE DATABASE “ORCL1” RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 2160

LOGFILE

  GROUP 1 (

    ‘/orcl11/sysdata/orcl1/redo01a.log’,

    ‘/orcl12/sysdata/orcl1/redo01b.log’

  ) SIZE 100M,

  GROUP 2 (

    ‘/orcl11/sysdata/orcl1/redo02a.log’,

    ‘/orcl12/sysdata/orcl1/redo02b.log’

  ) SIZE 100M,

  GROUP 3 (

    ‘/orcl11/sysdata/orcl1/redo03a.log’,

    ‘/orcl12/sysdata/orcl1/redo03b.log’

  ) SIZE 100M

DATAFILE

  ‘/orcl11/sysdata/orcl1/system01.dbf’,

  ‘/orcl11/sysdata/orcl1/undotbs01.dbf’,

  ‘/orcl11/sysdata/orcl1/sysaux01.dbf’,

  ‘/orcl11/sysdata/orcl1/users01.dbf’,

  ‘/orcl11/ora_data/ORCL1_TEST_DAT01.dbf’,

  ‘/orcl12/ora_data/ORCL1_TEST_DAT02.dbf’,

  ‘/orcl13/ora_data/ORCL1_TEST_DAT03.dbf’,

  ‘/orcl14/ora_data/ORCL1_TEST_DAT04.dbf’,

  ‘/orcl11/ora_data/ORCL1_TEST_DAT05.dbf’,

  ‘/orcl12/ora_data/ORCL1_TEST_DAT06.dbf’,

  ‘/orcl13/ora_data/ORCL1_TEST_DAT07.dbf’,

  ‘/orcl11/ora_data/ORCL1_TEST_IDX01.dbf’,

  ‘/orcl12/ora_data/ORCL1_TEST_IDX02.dbf’,

  ‘/orcl13/ora_data/ORCL1_TEST_IDX03.dbf’,

  ‘/orcl11/ora_data/ORCL1_FORE_DAT01.dbf’,

  ‘/orcl12/ora_data/ORCL1_FORE_IDX01.dbf’,

  ‘/orcl13/ora_data/ORCL1_FORE_IDX02.dbf’,

  ‘/orcl14/ora_data/ORCL1_FORE_IDX03.dbf’,

  ‘/orcl14/ora_data/TEST_DAT01.dbf’,

  ‘/orcl14/ora_data/TEST_IDX101.dbf’,

  ‘/orcl11/ora_data/TEST_DAT01.dbf’,

  ‘/orcl12/ora_data/TEST_DAT02.dbf’,

  ‘/orcl13/ora_data/TEST_DAT03.dbf’,

  ‘/orcl13/bradmark/bradmark.dbf’,

  ‘/orcl11/ora_data/TEST_DAT_NEW01.dbf’,

  ‘/orcl11/ora_data/TEST_IDX_NEW01.dbf’

CHARACTER SET US7ASCII

; ß 이 부분 까지만 수행

 

— 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 ‘/orcl14/archive/1_1_651132086.arc’;

— ALTER DATABASE REGISTER LOGFILE ‘/orcl14/archive/1_1_716140947.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 ‘/orcl11/sysdata/orcl1/temp01.dbf’

     SIZE 7000M REUSE AUTOEXTEND ON NEXT 10485760  MAXSIZE 32767M;

— End of tempfile additions.

  

5.     SQL 수행(소요시간 : 1)

SQL> alter session set events ‘10015 trace name adjust_scn level 1’;

 

6.     재생성이 완료되었으면 Recovery 작업을 수행합니다. 빨간 부분의 경로에 Archive를 위치한 후 Cancel 입력합니다.(아카이브로그 한 개씩 적용, 복구 됐을 시 현재 시점을 알기 위해서) (소요시간 : 1)

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 2034211841 generated at 06/12/2014 04:00:01 needed for thread1

ORA-00289: suggestion : /orcl14/archive/1_26093_716140947.arc

ORA-00280: change 2034211841 for thread 1 is in sequence #26093

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel 입력

 

7.     DBOPEN 합니다. (소요시간 : 5)

SQL>alter database open resetlogs;

 

8.     TEMP Tablespace를 생성하여 줍니다.( 위에서 Controlfile 생성 스크립트에 있는 내용) (소요시간 : 3)

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/orcl11/sysdata/orcl1/temp01.dbf’

     SIZE 7000M REUSE AUTOEXTEND ON NEXT 10485760  MAXSIZE 32767M;

 

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다