[Oracle DBA]Clone DB

Clone DB

/*

<cloneDB 구성시 주의해야 할 점>

1개의 software는 1개의 DB만을 사용해야 합니다.

spfile구성, control file 생성시 주의

import, export 실행시 dmp파일은 현재 디렉터리상에 생성됩니다. 주의

*/

SYS@orcl2> @logfile

    GROUP#  SEQUENCE# MEMBER                                      MB ARC STATUS

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

         1         13 /u01/app/oracle/orcl2/redo01.log            50 YES INACTIVE

         2         14 /u01/app/oracle/orcl2/redo02.log            50 YES INACTIVE

         3         15 /u01/app/oracle/orcl2/redo03.log            50 NO  CURRENT

SYS@orcl2> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@orcl2> !

[orcl2:~]$ pwd

/home/oracle

[orcl2:~]$ mkdir clone

[orcl2:~]$ cd clone

[orcl2:clone]$ cp -av /u01/app/oracle/orcl2/*.dbf /home/oracle/clone

`/u01/app/oracle/orcl2/data01.dbf' -> `/home/oracle/clone/data01.dbf'

`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/clone/example01.dbf'

`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/clone/sysaux01.dbf'

`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/clone/system01.dbf'

`/u01/app/oracle/orcl2/temp01.dbf' -> `/home/oracle/clone/temp01.dbf'

`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/clone/undotbs01.dbf'

`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/clone/users01.dbf'

[orcl2:clone]$ ls

data01.dbf  example01.dbf   sysaux01.dbf  temp01.dbf     users01.dbf

system01.dbf  undotbs01.dbf

[orcl2:clone]$ exit

SYS@orcl2> startup

ORACLE instance started.

Total System Global Area  431038464 bytes

Fixed Size                  1337016 bytes

Variable Size             146803016 bytes

Database Buffers          276824064 bytes

Redo Buffers                6074368 bytes

Database mounted.

Database opened.

SYS@orcl2> select systimestamp from dual;

SYSTIMESTAMP

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

26-APR-19 11.38.06.333458 AM +09:00

SYS@orcl2> create table hr.copy_emp tablespace users as select * from hr.employees;

Table created.

SYS@orcl2> select count(*) from hr.copy_emp;

  COUNT(*)

----------

       107

SYS@orcl2> select systimestamp from dual;

SYSTIMESTAMP

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

26-APR-19 11.38.41.542922 AM +09:00

SYS@orcl2> drop table hr.copy_emp purge;

Table dropped.

SYS@orcl2> @switch

System altered.

SYS@orcl2> /

System altered.

SYS@orcl2> /

System altered.

SYS@orcl2> /

System altered.

/*clone DB를 생성하기 앞서 DBstartup에 필요한 pfile을 생성한다.*/

SYS@orcl2> create pfile='$ORACLE_HOME/dbs/initclone.ora' from spfile;

File created.

/*target DB의 control file을 cloneDB쪽으로 text 형식으로 trace할 것이다. 이걸 베이스로 cloneDB쪽의 control file을 생성하기 위함.*/

SYS@orcl2> alter database backup controlfile to trace as '/home/oracle/clone/c_clone.sql';

Database altered.

<<CLONEDB_terminal에서 진행>>

=========================

clone 초기파라미터 변경

다른내용 다 버려도

control_files경로, db_name, log_archive_dest_1, log_archive_format, undo_tablespace는 필요함

<주의!!>clone DB 생성할때는 꼭!!주의!!

memory_target : /dev/shm file system의 크기가 최소 sga_max_size 크기가 되어야 합니다.

                실습환경에서는 2개의 인스턴스가 시작되어야 하므로 sga_max_size * 2 의 크기가 필요하나

                실습환경인 vbox 이미지에서는 부족하므로 설정하지 않습니다.

=========================

[orcl2:~]$ vi $ORACLE_HOME/dbs/initclone.ora

*.compatible='11.2.0.0.0'

*.control_files='/home/oracle/clone/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='clone'

*.log_archive_dest_1='location=/home/oracle/clone/arch1 mandatory'     

-- orcl2과 같은 경로로 지정하거나 다른경로로 지정할 경우 archive file을 복사해 줍니다.

*.log_archive_format='arch_%t_%s_%r.arc'                               

-- orcl2과 같이 지정합니다.

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

[orcl2:~]$ mkdir -p /home/oracle/clone/arch1

[orcl2:~]$ cd clone/

[orcl2:clone]$ ls

arch1        data01.dbf     sysaux01.dbf  temp01.dbf     users01.dbf

c_clone.sql  example01.dbf  system01.dbf  undotbs01.dbf

[orcl2:clone]$ cd

[orcl2:~]$ cd arch1

[orcl2:arch1]$ ls

arch_1_10_1006362834.arc  arch_1_14_1006362834.arc  arch_1_2_1006358295.arc  arch_1_5_1006362834.arc

arch_1_1_1006358295.arc   arch_1_15_1006362834.arc  arch_1_2_1006362834.arc  arch_1_6_1006362834.arc

arch_1_1_1006362834.arc   arch_1_16_1006362834.arc  arch_1_3_1006358295.arc  arch_1_7_1006362834.arc

arch_1_11_1006362834.arc  arch_1_17_1006362834.arc  arch_1_3_1006362834.arc  arch_1_8_1006362834.arc

arch_1_12_1006362834.arc  arch_1_18_1006362834.arc  arch_1_4_1006358295.arc  arch_1_9_1006362834.arc

arch_1_13_1006362834.arc  arch_1_19_1006362834.arc  arch_1_4_1006362834.arc

[orcl2:arch1]$ cp * /home/oracle/clone/arch1

/*

clone DB를 운영할 때는 반드시 control file을 새로 생성해야 합니다.

<주의점 : 주석처리된부분 모두 삭제.

clone DB에서 사용할 control file을 생성하기 위해 trace 떠온 file을 수정한다..

datafile, logfile의 경로를 clone쪽으로 잡아줄 것.

conrol file을 처음 생성할 때는 CREATE CONTROLFILE SET DATABASE ~ 이지만

control file이 이미 생성되어있다면 CREATE CONTROLFILE reuse DATABASE ~로 쓰여있었을 것

CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG        -- reuse를 SET , orcl을 CLONE으로 변경할 것.

    MAXLOGFILES 16        -- 최대 구성할 수 있는 group의 갯수 : 16개

    MAXLOGMEMBERS 3        -- 그룹당 member의 갯수        : 3개

    MAXDATAFILES 100        -- datafile을 100개까지 사용할 수 있음

    MAXINSTANCES 8        -- RAC 사용 할 때 instance(memory)를 8개까지 구성할 수 있음

    MAXLOGHISTORY 292        -- RAC 에서 archive log의 history를 최대 292개까지 기억할 수 있음.

부분부터

LOGFILE -- cloneDB의 경로로 지정할 것.

  GROUP 1 '/home/oracle/clone/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/home/oracle/clone/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/home/oracle/clone/redo03.log'  SIZE 50M BLOCKSIZE 512

--이 사이에 standby~~로 시작하는 부분은 삭제해서 logfile부분과 datafile부분이 바로 연결되게끔 구성해줄것.

DATAFILE -- cloneDB의 경로로 지정할 것.

  '/home/oracle/clone/system01.dbf',

  '/home/oracle/clone/sysaux01.dbf',

  '/home/oracle/clone/undotbs01.dbf',

  '/home/oracle/clone/users01.dbf',

  '/home/oracle/clone/example01.dbf'

CHARACTER SET AL32UTF8                --unicode를 뜻함.

*/

[orcl2:clone]$ vi c_clone.sql

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/home/oracle/clone/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/home/oracle/clone/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/home/oracle/clone/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

  '/home/oracle/clone/system01.dbf',

  '/home/oracle/clone/sysaux01.dbf',

  '/home/oracle/clone/undotbs01.dbf',

  '/home/oracle/clone/users01.dbf',

  '/home/oracle/clone/example01.dbf'

CHARACTER SET AL32UTF8

;

[orcl2:clone]$ . oraenv

ORACLE_SID = [orcl2] ? clone

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/dbhome_1

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[clone:clone]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 26 12:05:46 2019

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

Connected to an idle instance.

SYS@clone> @c_clone

Control file created.

SYS@clone> show parameter control

NAME                                 TYPE        VALUE

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

control_files                       string      /home/oracle/clone/control01.c

                                                 tl

/*time-based recovery를 할 때는 반드시 세션의 date format을 다음과 같이 설정합니다.*/

SYS@clone> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';     

Session altered.

/*

controlfile을 사용해서 time-based recovery를 진행한다.

controlfile을 재생성할 때는 반드시 using backup controlfile 로 진행해야 함.

이 상황에서는 controlfile을 새로 생성했기 때문에 using backup controlfile 키워드를 반드시 명시해주어야 한다.

*/

SYS@clone> recover database until time '2019-04-26 11:38:40' using backup controlfile;

ORA-00279: change 1021369 generated at 04/26/2019 12:30:00 needed for thread 1

ORA-00289: suggestion : /home/oracle/clone/arch1/arch_1_15_985378217.arc

ORA-00280: change 1021369 for thread 1 is in sequence #15

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

/u01/app/oracle/oradata/orcl/redo03.log                    

<- archived 화일이 없고 online log file 이므로 이름을 입력합니다.

Log applied.

Media recovery complete.

SYS@clone> alter database open resetlogs;

Database altered.

SYS@clone> select status, instance_name from v$instance;

STATUS       INSTANCE_NAME

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

OPEN         clone

SYS@clone> select count(*) from hr.copy_emp;

  COUNT(*)

----------

       107

SYS@clone> ! exp hr/hr file=copy_emp.dmp tables=copy_emp

Export: Release 11.2.0.1.0 - Production on Fri Apr 26 13:47:24 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table                       COPY_EMP        107 rows exported

Export terminated successfully without warnings.

SYS@clone> ! ls copy_emp.dmp

copy_emp.dmp

/*ORCL2의 hr계정으로 접속*/

HR@orcl2> select count(*) from hr.copy_emp;

select count(*) from hr.copy_emp

                        *

ERROR at line 1:

ORA-00942: table or view does not exist

/*owner라서 스스로 import가능.*/

HR@orcl2> !imp hr/hr file=copy_emp.dmp tables=copy_emp

Import: Release 11.2.0.1.0 - Production on Fri Apr 26 13:50:23 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing HR's objects into HR

. importing HR's objects into HR

. . importing table                     "COPY_EMP"        107 rows imported

Import terminated successfully without warnings.

HR@orcl2> select count(*) from hr.copy_emp;

  COUNT(*)

----------

       107