RAC& ASM to Single& Filesystem Clone DB Create

 

1. Clone database 생성

 

1) Source Database 사전 작업

1-1) Backup Database

Source Database Rman 접속

   

$ rman target /

   

Controlfile Auto backup 이 활성화 되어있지 않은 경우 활성화 시켜준다.

   

RMAN> configure controlfile autobackup on;

   

RMAN> configure controlfile autobackup format for device type disk to '/u01/app/oracle/rman_bak/%F';

   

Database backup

   

RMAN> RUN

2> {

3> allocate channel ch1 type disk

4> format '/u01/app/oracle/rman_bak/%d_DB_%u_%s_%p';

5> backup database plus archivelog;

6> release channel ch1;

7> }

    

1-2) Prameter File 백업

Controlfile Auto backup 시에 자동으로 Parameter file 도 백업이 되지만

Pfile 로 생성해서 직접 수정하기 위해 따로 한번 더 생성해준다.

   

SQL> create pfile ='/home/oracle/initTEST.ora' from spfile;

   

RMAN> RESTORE SPFILE TO '/tmp/spfileTEMP.ora' FROM AUTOBACKUP;

   

1-3) Backup File 전송

Source 에서 Target 으로 SCP 를 이용해 RMAN Backup 파일들과 parameter file 전송

   

$ scp FILENAME oracle@192.168.1.100:/home/oracle/rman_bak/

   

2. Clone DB 설정(Target)

2-1) Parameter File 설정

기존 RAC 관련 파라미터는 삭제하거나 주석처리하고, 경로나 메모리부분을 환경에 맞게 조절

DB NAME 의 경우 추후 변경

$ vi initTEST.ora

   

*.audit_file_dest='/app/oracle/admin/TEST/adump'

*.audit_sys_operations=TRUE

*.audit_trail='db'

#*.cluster_database=true

*.compatible='11.2.0.2.0'

*.db_create_file_dest='/app/oracle/oradata/TEST'

*.db_files=1024

*.db_name='TESTORA'

*.db_recovery_file_dest='/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4992899480

*.diagnostic_dest='/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'

*.fast_start_mttr_target=300

*.global_names=TRUE

#TESTORA2.instance_number=2

#TESTORA1.instance_number=1

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=1000

*.os_authent_prefix=''

*.pga_aggregate_target=15899340

*.processes=800

#*.remote_listener='etk-scan:1521'

*.remote_login_passwordfile='exclusive'

*.session_cached_cursors=100

*.sga_target=1001798690

#TESTORA2.thread=2

#TESTORA1.thread=1

TEST.undo_tablespace='UNDOTBS1'

#TSETORA2.undo_tablespace='UNDOTBS2'

#*.use_large_pages='ONLY'

   

2-2) Password File 생성/ Startup nomount

   

$ orapwd file=orapwTEST entries=6 password=oracle

   

nomount 상태로 startup

   

SQL> startup nomount

   

2-3) Controlfile restore / Backuppiece 등록

Source DB 의 Database backup 시에 autobackup 으로 받아진 file 로 Restore

   

RMAN> restore controlfile from '/u01/11g_db/rman_db_backups/con_c-208065887-20121014-01';

   

Restore 시에 기존에 받았던 백업들이 RMAN 에 등록이 되나 실제로 존재하지 않기 때문에 삭제해주고 새로 등록해준다.

   

RMAN> crosscheck backup;

RMAN> list expired backup;

RMAN> delete expired backup;

   

RMAN> catalog backuppiece '/home/oracle/rman_bak/TESTORA_DB_0uq6bchc_30_1';

RMAN> catalog backuppiece '/home/oracle/rman_bak/TESTORA_DB_0vq6bcid_31_1;

RMAN> catalog backuppiece '/home/oracle/rman_bak/ TESTORA_DB_0sq6bce7_28_1;

RMAN> catalog backuppiece '/home/oracle/rman_bak/TESTORA_DB_0tq6bcfu_29_1;

 

2-4) Restore Datafile

Controlfile 이 Restore 되었기 때문에 mount 시킨 뒤

Datafile Restore 진행

   

SQL> alter database mount;

   

RMAN> run {

2> set until sequence 63 thread 2; --set until cancel;

3> set newname for datafile 1 to '/backup/oradata/system01.dbf';

4> set newname for datafile 2 to '/backup/oradata/sysaux01.dbf';

5> set newname for datafile 3 to '/backup/oradata/undotbs01.dbf';

6> set newname for datafile 4 to '/backup/oradata/undotbs02.dbf';

7> set newname for datafile 5 to '/backup/oradata/users01.dbf';

8> restore datafile 1;

9> restore datafile 2;

10> restore datafile 3;

11> restore datafile 4;

12> restore datafile 5;

13> switch datafile all;

14> recover database;

15> }

   

2-5) RedoLog File 이름 수정 / Database Open

기존 ASM 기반의 경로와 이름으로 되어있는 것을 현재 경로에 맞게 수정해준다.

   

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

+REDO/etk/onlinelog/group_1.256.860865469

+REDO/testora/onlinelog/group_2.257.860865477

+REDO/testora/onlinelog/group_3.258.860865841

+REDO/testora/onlinelog/group_4.259.860865849

   

SQL> alter database rename file '+REDO/testora/onlinelog/group_1.256.860865469' to

2 '/app/oracle/oradata/TEST/redo01.log';

SQL> alter database rename file '+REDO/testora/onlinelog/group_3.258.860865841' to

2 '/app/oracle/oradata/TEST/redo03.log';

SQL> alter database rename file '+REDO/testora/onlinelog/group_4.259.860865849' to

2 '/app/oracle/oradata/TEST/redo04.log';

SQL> alter database rename file '+REDO/testora/onlinelog/group_4.259.860865849' to

2 '/app/oracle/oradata/TEST/redo04.log';

   

수정 완료 후 Database open

   

SQL > alter database open resetlogs;

    

2-6) 필요 없는 Redo Log group 삭제

RAC 에서 사용하던 THREAD 2 번의 Redo Log Goup 을 삭제

   

SQL> select thread#, status, enabled from v$thread;

THREAD# STATUS ENABLED

---------- ------ --------

1 OPEN PUBLIC

2 CLOSED PUBLIC

   

SQL> select group# from v$log where thread#2;

GROUP#

----------

3

4

   

SQL> alter database disable thread 2;

SQL> alter database drop logfile group 3;

SQL> alter database drop logfile group 4;

   

   

2-7) DB_NAME 변경

DB NAME 을 변경할 필요가 있을 경우 nid utility 를 사용해서 변경해준다

   

SQL> shutdown immediate

SQL> startup mount

   

$ nid target=sys/oracle dbname=TEST