지금하자

절차 

① 전체 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;

SQL> grant connect,resource to scott;

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

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

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

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

SQL> commit;

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

SQL> select * from scott.tt10;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[1번 노드 작업하기]

SQL> startup mount

SQL> select instance_name from v$instance;

SQL> archive log list

SQL> show parameter spfile;

SQL> alter system set log_archive_dest_1='location=/data/arc1';

SQL> alter system set log_archive_dest_2='location=/data/arc2';

SQL> alter database archivelog;

SQL> archvie log list;

SQL> alter database open;

절차

① tablespace 별 rawdevice 조사

② 백업 폴더를 만들어주고 

③ alter tablespace system begin backup

④ dd 명령어로 복사하기 

⑤ alter tablespace systemend backup

[tablespace 조회하기]

col tablespace_name for a10

col file_name for a45

select tablespace_name ,bytes/1024/1024 MB ,file_name

from dba_data_files;

[백업용 폴더 만들어 주고]

[oracle@rac1 data]$ mkdir -p /data/backup/open

[end backup 조회하기]

set line 200

col name for a50

col status for a15

select a.file#, a.name, b.status , to_char(b.time,

'YYYY-MM-DD:HH24:MI:SS') as time

from v$datafile a, v$backup b

where a.file#=b.file#;

[begin backup 시작]

SQL> alter tablespace system begin backup;

SQL> !dd if=/dev/raw/raw3 of=/data/backup/open/system_raw3 bs=8k

[end backup 해주기]

SQL> alter tablespace system end backup;

[동기화 하기]

SQL> alter system checkpoint;

[모아서 sql]

alter tablespace system begin backup;

!dd if=/dev/raw/raw3 of=/data/backup/open/system_raw3 bs=8k

alter tablespace system end backup;

alter tablespace undotbs1 begin backup;

!dd if=/dev/raw/raw17 of=/data/backup/open/undotbs1_raw17 bs=8k

① rac1 /etc/hosts vip 확인하기 

② clinent 에서 C:\app\stu\product\11.2.0\client_1\network\admin 에가서 tnsname.ora 수정

③

④

⑤

[Full name ]

ora.rac.db                          (ra1 +rac2 instance)                                

ora.rac.rac1.inst   (rac1 DB instance)                                                       

ora.rac.rac2.inst                   (rac2 DB instance)                               

ora.rac1.LISTENER_RAC1.lsnr                                           

ora.rac1.gsd                                                             

ora.rac1.ons                                                               

ora.rac1.vip                                                              

ora.rac2.LISTENER_RAC2.lsnr                                            

ora.rac2.gsd                                                               

ora.rac2.ons                                                             

ora.rac2.vip  

insa PREF: rac2 AVAIL: rac1

rac 데이터베이스에서 insa 서비스를 제거하겠습니까? (y/[n]) y

PRKP-1024 : insa 서비스는 아직 실행 중입니다.

insa PREF: rac2 AVAIL: rac1

rac 데이터베이스에서 insa 서비스를 제거하겠습니까? (y/[n]) y

[oracle@server113 ~]$ dbv file=/app/oracle/oradata/testdb/example01.dbf

[admin_table 통해 REPAIR_TABLE 만들기]

 begin

  2  dbms_repair.admin_tables(

  3  table_name => 'REPAIR_TABLE',

  4  table_type => dbms_repair.repair_table,

  5  action => dbms_repair.create_action,

  6  tablespace => 'USERS');

  7  end;

  8  /

[Index 만드는 table 만들기]

 begin

  dbms_repair.admin_tables(

   table_name => 'ORPHAN_KEY_TABLE',

  table_type => dbms_repair.orphan_table,

  action => dbms_repair.create_action,

  tablespace => 'USERS');

   end;

   /

[db_block_checking = true 로만들기]

SQL> show parameter db_block_checking;

SQL> alter system set db_block_checking=true;

System altered.

SQL> show parameter db_block_checking;

[table space 조회하기]

set line 200

col tablespace_name for a10

col file_name for a50

col mb for 9999

select tablespace_name , bytes/1024/1024 MB , file_name

from dba_data_files

[tablespace 생성]

SQL> create tablespace test20

  2  datafile '/app/oracle/oradata/testdb/test20.dbf' size 15M

  3  autoextend on;

[table 만들어 주기 ]

SQL> create table scott.tt910(no number , name varchar2(20)) tablespace test20;

[데이터 입력하기]

BEGIN

FOR  i IN 1..50000 LOOP

INSERT INTO scott.tt910 VALUES( i, DBMS_RANDOM.STRING('B',19));

END LOOP;

COMMIT;

END;

/

[확인하기]

SQL> select * from scott.tt910;

[off line 후]

SQL> alter tablespace test20 offline;

[scp로 이동후 block 깨는 작업하기]

[online]

SQL> alter tablespace test20 online;

SQL> select * from scott.tt910;

[DBMS_REPAIR에 에러를 넣어서 에러찾기]

declare n_corrupt int;

begin

n_corrupt:=0;

dbms_repair.check_object(

schema_name => 'SCOTT',

object_name => 'TT910',

repair_table_name =>'REPAIR_TABLE',

corrupt_count =>n_corrupt);

dbms_output.put_line('장애블럭수: ' || to_char(n_corrupt));

end;

/

[repair 조회하기]

col line 200;

col object_name for a10

col corrupt_description for a20

col repair_description for a20

select object_name , block_id, corrupt_type, marked_corrupt,

         corrupt_description, repair_description

from repair_table;

[접속]

[전체백업 받기]

RMAN> backup as compressed backupset database

2> format '/data/backup/open/rman/%U_%T_full';

[예제 테이블 생성하기 ]

[oracle@server113 rman]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 05:55:11 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning option

SQL> create table scott.gogak2

  2  (no number,

  3  name varchar2(10),

  4  addr varchar2(20));

Table created.

SQL> insert into scott.gogak2 values(1,'하민성', '경기성남');

1 row created.

SQL> insert into scott.gogak2 values(2,'김태희', '경기부천');

1 row created.

SQL> insert into scott.gogak2 values(3,'한가인', '부산');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.gogak2;

        NO NAME       ADDR

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

         1 하민성     경기성남

         2 김태희     경기부천

         3 한가인     부산

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

TO_CHAR(SYSDATE,'YY

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

2015-04-10-06:00:26

SQL> drop table scott.gogak purge;

[clone DB 만들기]

[oracle@server113 dbs]$ cp inittestdb.ora ./initrman.ora

[oracle@server113 dbs]$ vi initrman.ora

※ inittestdb.ora가 원본이면 안됨 .... 

[clonedb용 디렉토리 설정]

[oracle@server113 data2]$ mkdir -p rman/clone

[oracle@server113 rmanclone]$ mkdir -p /app/oracle/admin/rman

[oracle@server113 rmanclone]$ mkdir -p /app/oracle/admin/rman/adump

[oracle@server113 rmanclone]$ mkdir -p /app/oracle/admin/rman/dpdump

[archive log 복사 → ramn 있는 곳으로]

[oracle@server113 rman]$ cp -av /data2/arc1/* /data/backup/open/rman/

[현재 시스템 조회]

col platform_name for a20

col endian_format for a20 

select d.platform_name, endian_format

from v$transportable_platform tp , v$database d

where tp.platform_name=d.platform_name;

[RHEL4 에서 작업하기]

SQL> create tablespace conv_test

  2  datafile '/home/oracle/oradata/testdb/conv_test01.dbf' size 10M;

Tablespace created.

SQL> create table scott.ctest1(no number) tablespace conv_test;

Table created.

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

1 row created.

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.ctest1;

        NO

----------

         1

         2

[점검]

exec dbms_tts.transport_set_check('CONV_TEST',true);

[데이터 전송받기]

[oracle@server113 dp_conv]$ scp 172.16.65.140:/home/oracle/dp_conv/* /data/dp_conv/

[data pump 작업 target 에서]

[oracle@server113 dp_conv]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 12:11:09 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning option

SQL> create directory dp_conv as '/data/dp_conv';

Directory created.

SQL> grant read, write on directory dp_conv to system;

Grant succeeded.

[oracle@server113 dp_conv]$ rman target sys/oracle

RMAN> convert datafile '/data/dp_conv/data_D-TESTDB_I-2661467944_TS-CONV_TEST_FNO-6_01q5eq82'

 format "/app/oracle/oradata/testdb/conv_test01.dbf";2>

[상황 만들기]

SQL> create tablespace rtest

  2  datafile '/app/oracle/oradata/testdb/rtest01.dbf' size 10M

  3  autoextend on;

[RMAN 전체 백업 받기]

RMAN> backup as compressed backupset database

2> format '/data/backup/open/rman/%T_full_%U';

[삭제하기]

[oracle@server113 ~]$ rm -f /app/oracle/oradata/testdb/rtest01.dbf

[oracle@server113 ~]$ ls /app/oracle/oradata/testdb/rtest01.dbf

ls: /app/oracle/oradata/testdb/rtest01.dbf: 그런 파일이나 디렉토리가 없음

[oracle@server113 ~]$

[복구하기]

RMAN> sql 'alter tablespace rtest offline immediate';

RMAN> restore tablespace rtest;

[상황설정]

[oracle@server113 ~]$ rm -f /app/oracle/oradata/testdb/system01.dbf

[oracle@server113 ~]$ ls /app/oracle/oradata/testdb/system01.dbf

ls: /app/oracle/oradata/testdb/system01.dbf: 그런 파일이나 디렉토리가 없음

[설정 명령어]

SQL> alter database enable block change tracking

  2  using file '/data/backup/open/dt.bat';

[사용 안함 명령어]

SQL> alter database disable block change tracking;

[조회 명령어]

SQL> set line 200

SQL> col status for a10

SQL> col filename for a50

SQL> col MB for 999999

SQL> select status , filename, bytes/1024/1024 MB

  2  from v$block_change_tracking;

[접속하기]

[oracle@server113 ~]$ rman target sys/oracle

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 7 06:22:10 2015

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

connected to target database: TESTDB (DBID=2657215234)

□ 자동으로 채널 설정하기 

  - 기본채널설정

  - 사용자가 원하는 채널 설정 

[기본체널로 설정하기]

RMAN> configure default device type to disk;

[저장되는 위치 확인]

RMAN> backup tablespace example;

※  default 저장되는 위치

/app/oracle/flash_recovery_area/TESTDB/backupset/2015_04_07

[사용자 지정 저장 위치 정하기]

RMAN> configure channel device type disk

2> format '/data/backup/open/rman/%U_%T.bak'';

[테스트 하기]

RMAN> backup tablespace example;

□ 수동으로 Channel 설정

     명령어를 할때마다 

RMAN> run {

2> allocate channel ch1 type disk

3> format '/data/backup/open/%U_%T';

4> backup tablespace example;

5> }

[압축 하면서 백업하기] 

RMAN> backup as compressed backupset database

2> format '/data/backup/open/rman/%T_full_comp_%U';

[keep_backup]

RMAN> backup as compressed backupset tablespace example

2> format '/data/backup/open/rman/%U_keep_%T'

3> keep until time 'sysdate+90';

[not backed up]

RMAN> backup as compressed backupset database;

SELECT  SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK !=0

AND SOFAR <> TOTALWORK;

[조회]

RMAN> list backupset;

[특정삭제]

RMAN> delete backupset 2;

[다지우기]

RMAN> delete backupset;

[만일 OS에서지우면 다시 생성하기] 

RMAN> crosscheck backupset;

RMAN> report schema;

[여러개의 채널로 받기]

run {

allocate channel  ch10  device type disk;

allocate channel  ch11  device  type disk;

backup as compressed backupset 

tablespace example channel ch10

format  '/data/backup/open/rman/ch10_%U_%T';

backup as compressed backupset 

tablespace sysaux channel ch11

format '/data/backup/open/rman/ch11_%U_%T';

}

[]

select versions_startscn st_scn, versions_endscn endscn,

versions_xid txid, versions_operation opt,name

from scott.fmem versions between scn minvalue and maxvalue

where tel=111;

[시간 조회하기 ]

select scn_to_timestamp(1873484) from dual;

[바뀐 내용을 확인하고  update 문장 수행]

update "SCOTT"."FMEM" set "NAME" = '박동주' where tel =111;

[Window Client ]

①  관리자 실행 setup Program 설치하기 (주의 할점은 관리자 선택)

     C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle - OraClient11g_home1

② 설치 완료후 윈도우 → 모든프로그램→ OraClient11g_home1→ →구성 및 이전 툴

    → Net Configuration Assistant(관리자 권한 실행)

③ 설치 화면 나옴 1

사진

이전다음

01234567891011

사진

- 이름 지정 방법구성, 다음

-  기본값대로 다음

-  완료화면

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

- 로컬 네트 서비스 이름 구성 다음

- 추가 다음

- 서비스 이름은 database 이름(SID)하고 같게 해야함 ★★

- TCP 다음

- oracle 서버 확인후에 호스트 이름에 ip 주소를 넣어줌, 포트 확인하기

- 테스트를 수행합니다 다음 

④ [ Linux Server 설정] (리스너가 없습니다 경우)

① 콘솔 화면이 아닌 display 화면으로 가서 터미널 실행 후 export LANG=ko , netca 

사진

이전다음

012

사진

② Listener 구성 

③ add 선택

④ 이름 선택 

[oracle@server113 ~]$ cd /app/oracle/product/11g/network/admin/listener.ora 가 생겨야 함

⑤ [oracle@server113 admin]$ vi listener.ora

[내용 추가해줄것] 

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

   (SID_NAME = testdb)

   (ORACLE_HOME = /app/oracle/product/11g)

  )

 )

⑥ 완료된 후에 consol 에서 lsnrctl 후  status  , start listener , stop listener 

③ 설치 화면 나옴 2 

- 로그인문제의 경우 계정을 scott/tiger 로 바꿀것 

- 네트 서비스이름도 testdb로 해줄것을 권장 (SID 와 같게해주기 쉽게 구분하기위해)

- 다른 네트 서비스를 구성하시겠습니까 아니오

- C:\app\stu\product\11.2.0\client_1\network\admin/tnsnames.ora 가 있어야함 

사진

이전다음

012

사진

⑤ 확인 하기 window cmd 에서 tnsping testdb

    sqlplus scott/tiger@testdb

    sqlplus sys/oracle@testdb as sysdba

※ 설정바뀌면 리스너 재시작 할것 

※  네트워크 설정이 Bridged 여야 되는듯= 같은 대역이여야 됨 

※ 네트 서비스 : tnsnames.ora 설정되어 있는 접속하려는 db 정보 

※ Listener :  DB의 접속하기 위한 정보기 들어 있는 것 listener.ora

※ lsnrctl start listener2 : 번호 조심할것!! 

[테이블 만들어 주기]

 create table scott.ctest(

  2  no number,

  3  name varchar2(10),

  4  address varchar2(10))

  5  tablespace users;

[root 돌리기 ]

begin

for i in 1..100 loop

insert into scott.ctest

values(i, dbms_random.string('a',9),

dbms_random.string('b',9));

end loop;

commit;

end;

/

[조회하기]

select count(*) from scott.ctest;

[시간 조회하기]

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

SQL> truncate table scott.ctest;

테이블이 잘렸습니다.

SQL> select count(*) from scott.ctest;

  COUNT(*)

----------

         0

SQL> alter system switch logfile;

시스템이 변경되었습니다.

SQL> /

시스템이 변경되었습니다.

SQL> /

시스템이 변경되었습니다.

SQL> /

시스템이 변경되었습니다.

SQL> /

시스템이 변경되었습니다.

SQL>

[복구는 알아서 할것 ㅋㅋㅋㅋ]

[Listner 연결하기]

운영 DB 를 Client 로 만들려면 tnsnames.ora 가있어야함 

/app/oracle/product/11g/network/admin 위치에 만들어 줄것 (sample에서 복사하면 쉬움)

listener.ora (복구 Server에 대한 정보 )

[oracle@server113 admin]$ lsnrctl start listener2

[ Clinet 에 설정된 net 서비스 실행하기]

[oracle@server113 admin]$ tnsping clonedb

[Clinet 에서 clone DB 로 연결하는 db link 생성]

SQL> create tablespace test4

  2  datafile '/app/oracle/oradata/testdb/test04.dbf' size 10M

  3  autoextend on;

SQL> create temporary tablespace temp_test4

  2  tempfile '/app/oracle/oradata/testdb/temp_test04.dbf' size 10M

  3  autoextend on;

SQL> create user usera

  2  identified by abc123

  3  default tablespace test4

  4  temporary tablespace temp_test4;

SQL> grant connect , resource to usera;

SQL> conn usera/abc123;

USERA>create table gogak

  2  (no number ,

  3  name varchar2(10),

  4  address varchar2(20));

USERA>create table sangpum

  2  (no number,

  3  name varchar2(10),

  4  qty number);

begin

for i in 1..1000 loop

insert into gogak values(i, dbms_random.string('A',10), dbms_random.string('K',10));

end loop;

commit;

end;

/

 19 db_name=clonedb

 24 control_files="/data2/clone_db/control01.ctl",

 25                "/data2/clone_db/control02.ctl",

 26                "/data2/clone_db/control03.ctl"

 35 #memory_target=422576128

ORA-00279: change 1789629 generated at 04/04/2015 09:58:32 needed for thread 1

ORA-00289: suggestion : /data2/arc2/20_1_876058156.arc

ORA-00280: change 1789629 for thread 1 is in sequence #20

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

auto

Log applied.

Media recovery complete.

        NO

----------

         1

         2

         3

Export: Release 11.2.0.1.0 - Production on Sat Apr 4 10:58:07 2015

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 option

Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                            ABC          3 rows exported

Export terminated successfully without warnings.

INSTANCE_NAME

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

testdb

        NO

----------

         1

         2

         3

SQL> recover database until time '2015-04-04:12:37:31' using backup controlfile;

ORA-00279: change 1809542 generated at 04/04/2015 12:34:13 needed for thread 1

ORA-00289: suggestion : /data2/arc2/27_1_876058156.arc

ORA-00280: change 1809542 for thread 1 is in sequence #27

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

Log applied.

Media recovery complete.

Export: Release 11.2.0.1.0 - Production on Sat Apr 4 13:03:14 2015

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 option

Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                           ABC2          3 rows exported

Export terminated successfully without warnings.

Import: Release 11.2.0.1.0 - Production on Sat Apr 4 13:21:47 2015

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 option

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

import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. importing SCOTT's objects into SCOTT

. . importing table                         "ABC2"          3 rows imported

Import terminated successfully without warnings.