About DATABASE

주로 사용자의 실수로 datafile 삭제

-- 전제 조건 아카이브 모드 상태.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch
Oldest online log sequence     83
Next log sequence to archive   85
Current log sequence           85
SQL> 

-- 상태 확인 
SQL> select file#,name,status,CREATION_TIME from v$datafile;

     FILE# NAME                                               STATUS  CREATION_TIM
---------- -------------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                       SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                       ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                      ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                        ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                        ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                          ONLINE  22-JAN-24

-- 장애 상황 발생 
[oracle@ora19c ORA19C]$ rm users02.dbf 
[oracle@ora19c ORA19C]$ exit

-- 로그 스위칭 도중 db down 발생
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5028
Session ID: 154 Serial number: 38319

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
-- DB 마운트 모드 기동
[ora19c:/home/oracle]> ss

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 14:52:56 2024
Version 19.21.0.0.0

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3137336112 bytes
Fixed Size                  8930096 bytes
Variable Size             671088640 bytes
Database Buffers         2449473536 bytes
Redo Buffers                7843840 bytes
Database mounted.
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME             CON_ID
---------- ------- ------- -------------------- ---------- ------------ ----------
         5 ONLINE  ONLINE  FILE NOT FOUND                0                       0
 
SQL>  select file#,name,status,CREATION_TIME from v$datafile;
     FILE# NAME                                          STATUS  CREATION_TIM
---------- --------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                  SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                  ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                 ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                   ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                   ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                     ONLINE  22-JAN-24

6 rows selected.
-- 삭제 파일  껍데기 만들어 줌
SQL> alter database create datafile '/oradata/ORA19C/users02.dbf';

Database altered.
-- 복구 
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.
-- db open 완료
SQL> alter database open;

Database altered. 

-- 복구 완료 확인

SQL> select * from v$recover_file;

no rows selected

SQL> col file_name for a45
SQL> set lines 200
SQL>  select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                     BYTES/1024/1024
------------------------------ --------------------------------------------- ---------------
USERS                          /oradata/ORA19C/users01.dbf                             93.75
UNDOTBS1                       /oradata/ORA19C/undotbs01.dbf                             605
SYSTEM                         /oradata/ORA19C/system01.dbf                             1150
SYSAUX                         /oradata/ORA19C/sysaux01.dbf                              910
USERS                          /oradata/ORA19C/users02.dbf                           36.5625
SOE                            /oradata/ORA19C/soe01.dbf                                2419

6 rows selected.

SQL>  select file#,name,status,CREATION_TIME from v$datafile;

     FILE# NAME                                          STATUS  CREATION_TIM
---------- --------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                  SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                  ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                 ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                   ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                   ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                     ONLINE  22-JAN-24

6 rows selected.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ora19c           OPEN

※ DB가  다운 되지 않았다면 삭제한 datafile offline 후 alter database create datafile  생성 부터 

진행 하면 된다.

RAC TO SINGLE 복제 목적지 에서 실행
[oracle@aracdb aracdb]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.219.201 ora12c

### Public
192.168.219.111  arac1
192.168.219.113  arac2
### Private
192.168.0.111  arac1-priv
192.168.0.112  arac2-priv
### Virtual
192.168.219.112  arac1-vip
192.168.219.114  arac2-vip
### DNS
192.168.219.115  arac-scan


source 서버에서 pwd파일 목적지 서버로 카피
scp $ORACLE_HOME/dbs/orapwaracdb1 ora12c:$ORACLE_HOME/dbs/orapwaracdb
-- 패스워드 파일이 없을 시에 실행 후 목적지 서버로 옮긴다
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y format=12

파라미터 설정

*.audit_file_dest='/u01/app/oracle/admin/aracdb/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='12.2.0'
*.control_files='/oradata/aracdb/controlfile/control01.ctl','/oradata/aracdb/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/aracdb'
*.db_name='aracdb'
*.db_recovery_file_dest='/oradata/aracdb'
*.db_recovery_file_dest_size=10g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=aracdbXDB)'
#family:dw_helper.instance_mode='read-only'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='KOREAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1000m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=2998m
*.log_file_name_convert='+DATA','/oradata','+FRA','/oradata'
*.db_file_name_convert='+DATA','/oradata','+FRA','/oradata'

오라클 유저로 디렉토리 생성
/oradata/aracdb
/oradata/aracdb/onlinelog        <<== 리두 로그 경로 안만들었더니 에러 발생
/u01/app/oracle/admin/aracdb/adump
/u01/app/oracle/diag/rdbms/aracdb/aracdb/trace
/u01/app/oracle/diag/rdbms/aracdb/aracdb/cdump

-- 목적지 복제용 리스너 구성

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c)(PORT = 1521))
     )
   )

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (SID_NAME = aracdb)
       (ORACLE_HOME = /u01/app/oracle/product/12C/db_1)
     )
   )

목적지 NO MOUNT 모드 기동

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 23 17:18:57 2023

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3154116608 bytes
Fixed Size                  8625656 bytes
Variable Size             788529672 bytes
Database Buffers         2348810240 bytes
Redo Buffers                8151040 bytes
SQL> 


목적지
## tnsnames.ora
ARACDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.112)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = aracdb)
    )
  )

aracdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = aracdb)
    )
  )
-- 리스너 기동

[oracle@aracdb admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 25-JAN-2024 16:31:24
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/12C/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12C/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                25-JAN-2024 16:31:24
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12C/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
Services Summary...
Service "aracdb" has 1 instance(s).
  Instance "aracdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

-- 목적지에서 rman  접속 후 복제 명령어 실행
[oracle@aracdb admin]$ rman target sys/oracle@aracdb1 auxiliary sys/oracle@aracdb

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 25 16:31:34 2024

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

connected to target database: ARACDB (DBID=2943394426)
connected to auxiliary database: ARACDB (not mounted)

RMAN> DUPLICATE DATABASE TO aracdb
2>   FROM ACTIVE DATABASE
3>   NOFILENAMECHECK;

Starting Duplicate Db at 24/01/25
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=261 device type=DISK
current log archived

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    3154116608 bytes

Fixed Size                     8625656 bytes
Variable Size                788529672 bytes
Database Buffers            2348810240 bytes
Redo Buffers                   8151040 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''ARACDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''aracdb'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'aracdb1' primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ARACDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''aracdb'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    3154116608 bytes

Fixed Size                     8625656 bytes
Variable Size                788529672 bytes
Database Buffers            2348810240 bytes
Redo Buffers                   8151040 bytes

Starting restore at 24/01/25
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=135 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/oradata/aracdb/controlfile/control01.ctl
output file name=/oradata/aracdb/controlfile/control02.ctl
Finished restore at 24/01/25

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to 
 "/oradata/aracdb/datafile/system.265.1136924533";
   set newname for datafile  3 to 
 "/oradata/aracdb/datafile/sysaux.266.1136924577";
   set newname for datafile  4 to 
 "/oradata/aracdb/datafile/undotbs1.267.1136924603";
   set newname for datafile  5 to 
 "/oradata/aracdb/datafile/undotbs2.273.1136924729";
   set newname for datafile  7 to 
 "/oradata/aracdb/datafile/users.268.1136924605";
   restore
   from  nonsparse   from service 
 'aracdb1'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24/01/25
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/aracdb/datafile/system.265.1136924533
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/aracdb/datafile/sysaux.266.1136924577
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/aracdb/datafile/undotbs1.267.1136924603
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata/aracdb/datafile/undotbs2.273.1136924729
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata/aracdb/datafile/users.268.1136924605
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 24/01/25

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'aracdb1' 
           archivelog from scn  1728198;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 24/01/25
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=23
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service aracdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=24
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 24/01/25

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1159202130 file name=/oradata/aracdb/datafile/system.265.1136924533
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=1159202130 file name=/oradata/aracdb/datafile/sysaux.266.1136924577
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1159202130 file name=/oradata/aracdb/datafile/undotbs1.267.1136924603
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=1159202130 file name=/oradata/aracdb/datafile/undotbs2.273.1136924729
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=1159202130 file name=/oradata/aracdb/datafile/users.268.1136924605

contents of Memory Script:
{
   set until scn  1728333;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 24/01/25
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file /oradata/aracdb/ARACDB/archivelog/2024_01_25/o1_mf_1_23_lv43szth_.arc
archived log for thread 1 with sequence 24 is already on disk as file /oradata/aracdb/ARACDB/archivelog/2024_01_25/o1_mf_1_24_lv43t18x_.arc
archived log file name=/oradata/aracdb/ARACDB/archivelog/2024_01_25/o1_mf_1_23_lv43szth_.arc thread=1 sequence=23
archived log file name=/oradata/aracdb/ARACDB/archivelog/2024_01_25/o1_mf_1_24_lv43t18x_.arc thread=1 sequence=24
media recovery complete, elapsed time: 00:00:01
Finished recover at 24/01/25
Oracle instance started

Total System Global Area    3154116608 bytes

Fixed Size                     8625656 bytes
Variable Size                788529672 bytes
Database Buffers            2348810240 bytes
Redo Buffers                   8151040 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''ARACDB'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''ARACDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    3154116608 bytes

Fixed Size                     8625656 bytes
Variable Size                788529672 bytes
Database Buffers            2348810240 bytes
Redo Buffers                   8151040 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ARACDB" RESETLOGS ARCHIVELOG 
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/oradata/aracdb/onlinelog/group_1.260.1136924673', '/oradata/aracdb/onlinelog/group_1.270.1136924671' ) SIZE 200 M  REUSE,
  GROUP   2 ( '/oradata/aracdb/onlinelog/group_2.261.1136924673', '/oradata/aracdb/onlinelog/group_2.271.1136924671' ) SIZE 200 M  REUSE
 DATAFILE
  '/oradata/aracdb/datafile/system.265.1136924533'
 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE 
  
  INSTANCE 'i2' 
  GROUP   3 ( '/oradata/aracdb/onlinelog/group_3.274.1136924835', '/oradata/aracdb/onlinelog/group_3.262.1136924835' ) SIZE 200 M  REUSE,
  GROUP   4 ( '/oradata/aracdb/onlinelog/group_4.275.1136924837', '/oradata/aracdb/onlinelog/group_4.263.1136924837' ) SIZE 200 M  REUSE

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/oradata/aracdb/tempfile/temp.272.1136924683";
   switch clone tempfile all;
   catalog clone datafilecopy  "/oradata/aracdb/datafile/sysaux.266.1136924577", 
 "/oradata/aracdb/datafile/undotbs1.267.1136924603", 
 "/oradata/aracdb/datafile/undotbs2.273.1136924729", 
 "/oradata/aracdb/datafile/users.268.1136924605";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/aracdb/tempfile/temp.272.1136924683 in control file

cataloged datafile copy
datafile copy file name=/oradata/aracdb/datafile/sysaux.266.1136924577 RECID=1 STAMP=1159202165
cataloged datafile copy
datafile copy file name=/oradata/aracdb/datafile/undotbs1.267.1136924603 RECID=2 STAMP=1159202165
cataloged datafile copy
datafile copy file name=/oradata/aracdb/datafile/undotbs2.273.1136924729 RECID=3 STAMP=1159202165
cataloged datafile copy
datafile copy file name=/oradata/aracdb/datafile/users.268.1136924605 RECID=4 STAMP=1159202165

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1159202165 file name=/oradata/aracdb/datafile/sysaux.266.1136924577
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1159202165 file name=/oradata/aracdb/datafile/undotbs1.267.1136924603
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=1159202165 file name=/oradata/aracdb/datafile/undotbs2.273.1136924729
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1159202165 file name=/oradata/aracdb/datafile/users.268.1136924605

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 24/01/25

RMAN> 


접속 후 상태 확인

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 25 16:37:44 2024

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
aracdb           OPEN

##################################################################################

채널 여러개 할당

복제 시에 채널 개수 만큼 패러럴 하게 복제

ex) 채널 2개 할당 

rman target sys/oracle@aracdb1 auxiliary sys/oracle@aracdb
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
duplicate database to aracdb from active database nofilenamecheck;
}

출처 :https://hoing.io/archives/268

1. TEST Information

2. Scenario

1) 현재 testdb1 , testdb2 의 SID로 RAC(10.2.0.5) 에서 HOT Backup을 이용하여 clonedb를 생성

2) 복제 되는 instance의 SID는 copydb 로 할 것이며, single 로 복구를 시도 할 것이다.

3) RAC는 /oradata/testdb ,  single 은 /oradata3/copydb 로 복구 할 것이다.

3. HOT BACKUP

■ 테스트를 간편하게 하기 위해 alter database begin backup; 으로 백업 진행한다.

SQL> alter database begin backup;

■ Redo와 Temp Tablespace 파일을 제외한 모든 datafile 을 복사 한다.

$ cp system01.dbf /oradata3/copydb/

$ cp sysaux01.dbf /oradata3/copydb/

$ cp undotbs01.dbf /oradata3/copydb/

$ cp undotbs02.dbf /oradata3/copydb/

$ cp users01.dbf /oradata3/copydb/

■ 복사 후 end backup  을 실행한다.

SQL> alter database end backup;

END Backup 을 실행한 시간을 확인한다.

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

Time

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

2011-12-04:12:10:09

4. TEST Table Creation

■ 복사 후 복구 테스트를 위해서 테이블과 데이터를 입력한다.

■ 테이블 생성

SQL> create table test01 (no number) tablespace users;

■ 데이터 입력

BEGIN

for i in 1..1000 loop

insert into test01 values(i);

end loop;

commit;

END;

/

■ log switch  및 checkpoint 발생

SQL> alter system switch logfile;   -- 수회 실행

■ test table삭제

SQL> drop table test01 purge;

■ 삭제 시간 확인

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

Time

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

2011-12-04:12:26:00

5. Ready to clonedb

■ spfile 로 pfile을 생성

SQL> create pfile='$ORACLE_HOME/dbs/initCOPYDB.ora' from spfile;

■ controlfile 재생성을 위해 trace 파일 생성

SQL> alter database backup controlfile to trace as '/oradata3/copydb/recon.sql';

■ pfile 수정

아래 파라미터를 clonedb 환경에 맞게 수정 한다, 물론 디렉토리도 생성을 해야 한다.

*.audit_file_dest=

*.background_dump_dest=

*.control_files='

*.user_dump_dest='

*.db_name='testdb'

RAC 파라미터 변경, 아래와 같이 변경

*.instance_number=1

*.cluster_database=false

*.thread=1

*.undo_tablespace='UNDOTBS1'

■ pfile 수정

create controlfile 절을 수정한다.

CREATE CONTROLFILE SET DATABASE "COPYDB" RESETLOGS  NOARCHIVELOG

   REUSE => SET

  NORESETLOGS => RESETLOGS

그 외 경로를 clonedb에 맞게 수정한다.

복구 하고 open 후 temp tablespace를 생성 함으로 temp tablespace 생성 절을 별도로 백업 해둔다

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata3/copydb/temp01.dbf'

SIZE 524288000  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

아래와 같이 CHARACTER SET KO16MSWIN949;   까지 유지해서 파일을 작성한다.

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "COPYDB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/oradata3/copydb/redo01a.log',

    '/oradata3/copydb/redo01b.log'

  ) SIZE 300M,

  GROUP 2 (

    '/oradata3/copydb/redo02a.log',

    '/oradata3/copydb/redo02b.log'

  ) SIZE 300M,

  GROUP 3 (

    '/oradata3/copydb/redo03a.log',

    '/oradata3/copydb/redo03b.log'

  ) SIZE 300M,

  GROUP 4 (

    '/oradata3/copydb/redo04a.log',

    '/oradata3/copydb/redo04b.log'

  ) SIZE 300M

-- STANDBY LOGFILE

DATAFILE

  '/oradata3/copydb/system01.dbf',

  '/oradata3/copydb/undotbs01.dbf',

  '/oradata3/copydb/sysaux01.dbf',

  '/oradata3/copydb/undotbs02.dbf',

  '/oradata3/copydb/users01.dbf'

CHARACTER SET KO16MSWIN949;

6. Creation clonedb

■ sid를 변경하고 컨트롤 파일을 재생성 한다.

$ export ORACLE_SID=COPYDB

SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 12:48:19 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance

SQL> @recon.sql

■ 시간 기반으로 복구 한다.

복구를 진행할 세션에서 복구하기 편한 방식으로 시간 설정을 한다.

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

삭제한 시간이 2011-12-04:12:26:00 이기 때문에 24 분으로 복구 하겠다.

SQL> recover database until time '2011-12-04:12:24:00' using backup controlfile;

복구를 실시하면 아래와 같이 아카이브 파일을 필요로 한다.

ORA-00279: change 565796 generated at 12/04/2011 12:01:54 needed for thread 2

ORA-00289: suggestion : /oracle/product/102/db/dbs/arch2_14_768267462.dbf

ORA-00280: change 565796 for thread 2 is in sequence #14

위에서 알 수 있는 것은 thread 2(RAC에서 2번째 노드) 의 시퀀스 14번 을 가진 아카이브를 원하는

것이다 .

테스트 환경에서는 파일명이 arc_2_14_768267462.arc  이며, 경로 및 파일을 입력한다.

파일명 입력

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

/oradata/arch_testdb/arc_2_14_768267462.arc

이번에는 thread 1의 change 565796 을 포함 한 아카이브 파일을 입력 해야 한다.

ORA-00279: change 565796 generated at  needed for thread 1

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

RAC 로 접속해서 쿼리를 수행하여 THREAD1의 565796 변경본이 포함된 아카이브 파일을 찾는다.

SQL> set lines 500

SQL> col name for a50

SQL> SELECT THREAD# ,SEQUENCE# , FIRST_CHANGE#, NEXT_CHANGE#, NAME,

TO_CHAR(FIRST_TIME,'YYYY-MM-DD:HH24:MI:SS') FIRST_TIME  FROM V$ARCHIVED_LOG;

THREAD#  SEQUENCE  # FIRST_CHANGE   # NEXT_CHANGE#      NAME                                   FIRST_TIME

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

  2          14       553454              567190      /oradata/arch_testdb/arc_2_14_768267462.arc    2011-12-04:05:52:04

  2          15       567190              567247      /oradata/arch_testdb/arc_2_15_768267462.arc    2011-12-04:12:20:05

  2          16        567247             567249      /oradata/arch_testdb/arc_2_16_768267462.arc    2011-12-04:12:20:09

  1          11       553456              567252    /oradata/arch_testdb/arc_1_11_768267462.arc   2011-12-04:05:52:05

  2          17       567249              567254      /oradata/arch_testdb/arc_2_17_768267462.arc    2011-12-04:12:20:12

  2          18       567254              567274      /oradata/arch_testdb/arc_2_18_768267462.arc    2011-12-04:12:20:15

  2          19       567274              567279      /oradata/arch_testdb/arc_2_19_768267462.arc    2011-12-04:12:21:12

  1          12       567252              567293      /oradata/arch_testdb/arc_1_12_768267462.arc    2011-12-04:12:20:14

확인 해 보면 arc_1_11_768267462.arc 파일인 것을 알 수 있다. 아래와 같이 경로와 파일명을 입력한다.

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

/oradata/arch_testdb/arc_1_11_768267462.arc

ORA-00279: change 567190 generated at 12/04/2011 12:20:05 needed for thread 2

ORA-00289: suggestion : /oracle/product/102/db/dbs/arch2_15_768267462.dbf

ORA-00280: change 567190 for thread 2 is in sequence #15

ORA-00278: log file '/oradata/arch_testdb/arc_2_14_768267462.arc' no longer

needed for this recovery

change 567190 for thread 2 is in sequence #15 메세지를 보면 THREAD2의 15 시퀀스 아카이브 파일을

필요로 한다는 것을 알 수 있다. 경로 및 파일명을 입력한다.

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

/oradata/arch_testdb/arc_2_15_768267462.arc

위와 같은 패턴으로 요청하는 아카이브 파일을 계속 적용해준다.

운영중인 RAC에 생성 된 아카이브 보다 더 높은 시퀀스를 요구 한다면 로그 스위치를 발생하여

생성한 아카이브를 적용 시켜준다.

계속 적용 시키면 아래와 같이 recovery가 되었다는 메세지를 볼 수 있다.

Log applied.

Media recovery complete.

resetlogs로 instance를 open한다

SQL> alter database open resetlogs;

Temp Tablespace 를 추가한다.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata3/copydb/temp01.dbf'

SIZE 524288000  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

■ 예외 처리

아래와 같이 ORA-38856 가 나오게 되면 Unpulbished Bug-4355382 로써

오라클 문서ID: 334899.1 를 참조한다.

ERROR at line 1:

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

pfile에 _no_recovery_through_resetlogs=TRUE  를 추가한 후 다시 resetlogs로 open한다.

open 후 test01 테이블이 복구 되었는지 조회해 본다.

select count(*) from test01;

  COUNT(*)

----------

      1000

● clone DB를 이용한 incomplete recovery 실습1

  ◎ User Managed

    1. 실습을 위한 환경 설정 : ① whole backup       

    1. 실습을 위한 환경 설정 : ② 테이블 생성 -> 데이터 입력 -> 테이블 삭제 (recovery 확인용)

    2. clone DB를 이용한 복구 : ① control file 및 parameter file 준비

    2. clone DB를 이용한 복구 : ② datafile backup, online redo log file, archive log file 복사

    2. clone DB를 이용한 복구 : ③ startup nomount -> control file 생성 -> 불완전 복구

    2. clone DB를 이용한 복구 : ④ clone DB에서 export -> devdb로 import

    2. clone DB를 이용한 복구 : ⑤ clone database 종료 및 삭제