'DB - ORACLE/Oracle Trouble Shooting' 카테고리의 글 목록 (36 Page)

DB - ORACLE/Oracle Trouble Shooting

DB 백업데이타로 타 서버 DB Recovery 방법(1. 아카이브 존재 시 정상복구, 2. 아카이브 유실시 비정상복구) 2015.01.19

DB 백업데이타로 타 서버 DB Recovery 방법(1. 아카이브 존재 시 정상복구, 2. 아카이브 유실시 비정상복구)

알 수 없는 사용자 2015. 1. 19. 20:46

2015. 1. 19. 20:46

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. DB를 OPEN 합니다. (소요시간 : 5분)

SQL>alter database open resetlogs;

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

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