14장 RAC Backup & Recovery(논리적복구)

Drop table 상황 발생

절차 

① 전체 open openbackup 받기 

② 테이블 생성후 commit 

③ 테이블 삭제하는 상황발생 

④ spfile 통해 pfile 생성하기 

⑤ pfile 수정후 이름 바꾸기 

⑥ control 파일 생성하는 스크립트 만들기 

⑦ 받아 두었던 backup clone 경로로 이동 

⑧ rac2 경로의 archive 파일을 이동시키기 

⑨ recover 해주기 

⑩

⑪

[기본 설정하기]

SQL> create user scott identified by tiger

  2  default tablespace users

  3  temporary tablespace temp

  4  quota unlimited on users;

User created.

SQL> grant connect,resource to scott;

Grant succeeded.

SQL> create table scott.tt10(no number);

Table created.

SQL> insert into scott.tt10 values(1);

1 row created.

SQL> insert into scott.tt10 values(2);

1 row created.

SQL> insert into scott.tt10 values(3);

1 row created.

SQL> commit;

Commit complete.

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

TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS

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

2015-05-04:11:04:48

SQL> select * from scott.tt10;

        NO

----------

         1

         2

         3

[Drop table 장애 발생]

SQL> drop table scott.tt10 purge;

Table dropped.

SQL> select * from scott.tt10;

select * from scott.tt10

                    *

ERROR at line 1:

ORA-00942: table or view does not exist

-1,2 번 노드 공통-

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

[pfile 만들기]

SQL> create pfile from spfile;

[clone DB 생성하기]

[oracle@rac1 dbs]$ pwd

/home/oracle/product/10g/db/dbs

[oracle@rac1 dbs]$ vi initrac1.ora

#rac2.__db_cache_size=96468992

#rac1.__db_cache_size=92274688

#rac1.__java_pool_size=4194304

#rac2.__java_pool_size=4194304

#rac1.__large_pool_size=4194304

#rac2.__large_pool_size=4194304

#rac2.__shared_pool_size=75497472

#rac1.__shared_pool_size=79691776

#rac1.__streams_pool_size=0

#rac2.__streams_pool_size=0

*.audit_file_dest='/home/oracle/admin/rac/adump'

*.background_dump_dest='/home/oracle/admin/rac/bdump'

#*.cluster_database_instances=2

#*.cluster_database=true

*.compatible='10.2.0.3.0'

*.control_files='/data/temp/control01.ctl'

*.core_dump_dest='/home/oracle/admin/rac/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='clone'

#rac2.instance_number=2

#rac1.instance_number=1

*.job_queue_processes=10

*.log_archive_dest_1='location=/data/arc1'

*.log_archive_dest_2='location=/data/arc2'

*.nls_language='KOREAN'

*.nls_territory='KOREA'

*.open_cursors=300

*.pga_aggregate_target=60817408

*.processes=150

#*.remote_listener='LISTENERS_RAC'

*.remote_login_passwordfile='exclusive'

*.sga_target=184549376

#rac2.thread=2

#rac1.thread=1

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

#rac1.undo_tablespace='UNDOTBS1'

#rac2.undo_tablespace='UNDOTBS2'

*.user_dump_dest='/home/oracle/admin/rac/udump'

[이름 바꾸어주기]

[oracle@rac1 dbs]$ mv initrac1.ora initclone.ora

[control 파일 재생성 쿼리 만들기]

SQL> alter database backup controlfile to trace as '/home/oracle/re.sql';

Database altered.

[oracle@rac1 ~]$ vi re.sql

STARTUP NOMOUNT

CREATE CONTROLFILE  SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/data/temp/redo01.log'  SIZE 50M,

  GROUP 2 '/data/temp/redo02.log'  SIZE 50M,

  GROUP 3 '/data/temp/redo03.log'  SIZE 50M,

  GROUP 4 '/data/temp/redo04.log'  SIZE 50M

DATAFILE

  '/data/temp/raw3',

  '/data/temp/raw17',

  '/data/temp/raw4',

  '/data/temp/raw18',

  '/data/temp/raw15'

CHARACTER SET KO16MSWIN949

;

[데이터 파일 복사하기]

[oracle@rac1 open]$ mkdir /data/temp

[oracle@rac1 ~]$ cd /data/backup/open/

[oracle@rac1 open]$ dd if=/data/backup/open/sysaux_raw4 of=/data/temp/raw4 bs=8k

[oracle@rac1 open]$ dd if=/data/backup/open/system_raw3 of=/data/temp/raw3 bs=8k

[oracle@rac1 open]$ dd if=/data/backup/open/undotbs1_raw17 of=/data/temp/raw17 bs=8k

[oracle@rac1 open]$ dd if=/data/backup/open/undotbs2_raw18 of=/data/temp/raw18 bs=8k

[oracle@rac1 open]$ dd if=/data/backup/open/users_raw15 of=/data/temp/raw15 bs=8k

[oracle SID 바꾸어주기]

[oracle@rac1 ~]$ export ORACLE_SID=clone

SQL> @re.sql

ORACLE instance started.

Total System Global Area  184549376 bytes

Fixed Size                  1266488 bytes

Variable Size              62917832 bytes

Database Buffers          117440512 bytes

Redo Buffers                2924544 bytes

Control file created.

[archive log 옮겨주기]

SYS>!scp rac2:/data/arc1/* /data/arc1/

2_10_878407832.dbf                            100% 1024     1.0KB/s   00:00

2_11_878407832.dbf                            100% 1024     1.0KB/s   00:00

2_12_878407832.dbf                            100% 1024     1.0KB/s   00:00

2_13_878407832.dbf                            100% 1024     1.0KB/s   00:00

2_5_878407832.dbf                             100% 1599KB   1.6MB/s   00:00

2_6_878407832.dbf                             100% 2716KB   2.7MB/s   00:00

2_7_878407832.dbf                             100% 1536     1.5KB/s   00:00

2_8_878407832.dbf                             100% 1024     1.0KB/s   00:00

2_9_878407832.dbf                             100% 1024     1.0KB/s   00:00