오라클 유저생성
 
1. 접속
[root@oracle ~]# su - oracle
[oracle@oracle ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 17 15:35:57 2011

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

SQL> connect /as sysdba
Connected.                            

2. 테이블 스페이스 생성
SQL> create tablespace test datafile '/oracle/oradata/orcl/test.dbf' size 10M;

Tablespace created.

3. 유저생성
SQL> create user test identified by xxxx default tablespace test temporary tablespace temp quota 500k on test;

User created.

4. 권한주기
SQL> grant connect to test;

Grant succeeded.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> grant connect,dba to test;

Grant succeeded.

5. 생성된 유저로 테이블 생성해 보기
SQL> connect test/xxxx
Connected.
SQL> create table test(
  2  id number(3));

Table created.

SQL> insert into test values('1');

1 row created.

SQL> select * from test;

        ID
----------
         1

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret

목적:

백업되지 않은 데이터 파일 완전복구
장애:

닫힌 백업 이후 테이블 스페이스 1개 생성했다.
그런데 그 테이블을 실수로 지워버렸다.

테이블스페이스 새로 생성


SQL> create tablespace test1 datafile '/oracle/oradata/orcl/test1.dbf' size 3M;

Tablespace created.


SQL> create user test1 identified by test1 default tablespace test1 temporary tablespace temp quota unlimited on test1;

User created.


SQL> grant connect to test1;

Grant succeeded.

SQL> grant resource to test1;

Grant succeeded.

SQL> connect test1/test1
Connected.


SQL> create table test1(id number);

Table created.


SQL> connect /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.


SQL> connect test1/test1
Connected.

SQL> insert into test1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> connect /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.


SQL> !ls /oracle/oradata/orcl | grep test1
test1.dbf

SQL> !rm /oracle/oradata/orcl/test1.dbf  ---> 장애 발생


복구 과정:

SQL> alter tablespace test1 offline immediate;

Tablespace altered.


SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
         8 OFFLINE OFFLINE FILE NOT FOUND                0

SQL> select h.tablespace_name, d.name, h.error from v$datafile d, v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- --------------------
SYSTEM          /oracle/oradata/orcl/system01.dbf
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
USERS           /oracle/temp/users01.dbf
EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf
                /oracle/oradata/orcl/test1.dbf           FILE NOT FOUND   ---> 이 파일이 장애이다.

8 rows selected.


데이터 파일 재생성 

SQL> alter database create datafile '/oracle/oradata/orcl/test1.dbf' as '/oracle/oradata/orcl/test1.dbf';

Database altered.


recover 명령으로 복구

SQL> recover tablespace test1;
Media recovery complete.

복구된 파일 온라인으로 변경

SQL> alter tablespace test1 online;

Tablespace altered.

SQL> select * from v$recover_file;

no rows selected

SQL> connect test1/test1
Connected.
SQL> select * from test1;

        ID
----------
         1



저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret

목적
임의의 디렉토리에 복구
장애
shutdown 상태에서 users01.dbf 삭제

아카이브 로그 파일의 수 늘리기 위해 로그 스위치 강제 실행
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> col datafile format a45
SQL> select v$tablespace.name TS, v$datafile.name datafile from v$tablespace,v$datafile where v$datafile.ts#=v$tablespace.ts#;

TS                             DATAFILE
------------------------------ ---------------------------------------------
SYSTEM                         /oracle/oradata/orcl/system01.dbf
SYSAUX                         /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1                       /oracle/oradata/orcl/undotbs01.dbf
USERS                          /oracle/oradata/orcl/users01.dbf
EXAMPLE                        /oracle/oradata/orcl/example01.dbf
GUK                            /oracle/oradata/orcl/guk.dbf
TEST                           /oracle/oradata/orcl/test.dbf

7 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


파일삭제 장애 재현
SQL> !rm /oracle/oradata/orcl/users01.dbf



복구

데이터 베이스 마운트 단계까지 시작

SQL> startup mount
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
Database mounted.



장애 파일 확인

SQL> col tablespace_name format a15
SQL> col name format a40
SQL> col error format a10
SQL> select h.tablespace_name, d.name, h.error from v$datafile d, v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- ----------
SYSTEM          /oracle/oradata/orcl/system01.dbf
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
                /oracle/oradata/orcl/users01.dbf         FILE NOT                          ---> 이파일이 장애
                                                         FOUND

EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.


문제 파일 offline 변경

SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' offline;

Database altered.


SQL> select d.file#,d.name,d.status,h.status from v$datafile d, v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         OFFLINE OFFLINE                 ---> 변경된것 확인됨.
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


SQL> alter database open;

Database altered.


장애 파일 백업본을 임의의 디렉토리에 복사

SQL> !mkdir /oracle/temp

SQL> !cp /backup/close/20110217/oradata/users01.dbf /oracle/temp/

SQL> !ls /oracle/temp
users01.dbf


SQL> alter database rename file '/oracle/oradata/orcl/users01.dbf' to '/oracle/temp/users01.dbf';

Database altered.


SQL> select file_name , tablespace_name from dba_data_files;

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/oracle/temp/users01.dbf                 USERS                             ---> 위치가 변경된것 확인됨.
/oracle/oradata/orcl/undotbs01.dbf       UNDOTBS1
/oracle/oradata/orcl/sysaux01.dbf        SYSAUX
/oracle/oradata/orcl/system01.dbf        SYSTEM
/oracle/oradata/orcl/example01.dbf       EXAMPLE
/oracle/oradata/orcl/guk.dbf             GUK
/oracle/oradata/orcl/test.dbf            TEST

7 rows selected.


recover 명령으로 파일 복구

SQL> recover datafile '/oracle/temp/users01.dbf'
ORA-00279: change 945400 generated at 02/17/2011 16:39:57 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_10_6osq55bn_.arc
ORA-00280: change 945400 for thread 1 is in sequence #10


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 951877 generated at 02/17/2011 17:30:28 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_11_6osq99oz_.arc
ORA-00280: change 951877 for thread 1 is in sequence #11


ORA-00279: change 952160 generated at 02/17/2011 17:32:41 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_12_6osqz7vm_.arc
ORA-00280: change 952160 for thread 1 is in sequence #12


ORA-00279: change 953139 generated at 02/17/2011 17:44:23 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_13_6osr2031_.arc
ORA-00280: change 953139 for thread 1 is in sequence #13


ORA-00279: change 953301 generated at 02/17/2011 17:45:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_14_6ot725sz_.arc
ORA-00280: change 953301 for thread 1 is in sequence #14


ORA-00279: change 973479 generated at 02/17/2011 22:01:56 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_15_6ot94gdz_.arc
ORA-00280: change 973479 for thread 1 is in sequence #15


ORA-00279: change 978379 generated at 02/17/2011 22:37:18 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_16_6ov0wgtn_.arc
ORA-00280: change 978379 for thread 1 is in sequence #16


ORA-00279: change 1009050 generated at 02/18/2011 05:22:37 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_17_6ovng7j2_.arc
ORA-00280: change 1009050 for thread 1 is in sequence #17


ORA-00279: change 1052352 generated at 02/18/2011 10:56:22 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_18_6ow143ph_.arc
ORA-00280: change 1052352 for thread 1 is in sequence #18

ORA-00279: change 1070284 generated at 02/18/2011 14:32:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_19_6ow4nh9r_.arc
ORA-00280: change 1070284 for thread 1 is in sequence #19


ORA-00279: change 1074904 generated at 02/18/2011 15:32:47 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_20_6ow4np4n_.arc
ORA-00280: change 1074904 for thread 1 is in sequence #20


Log applied.
Media recovery complete.


복구파일 online 으로 변경

SQL> select d.file#,d.name,d.status,h.status from v$datafile d, v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/temp/users01.dbf                 OFFLINE OFFLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


SQL> alter database datafile '/oracle/temp/users01.dbf' online;

Database altered.


SQL> select d.file#,d.name,d.status,h.status from v$datafile d, v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/temp/users01.dbf                 ONLINE  ONLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


SQL> select h.tablespace_name, d.name, h.error from v$datafile d, v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- ----------
SYSTEM          /oracle/oradata/orcl/system01.dbf
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
USERS           /oracle/temp/users01.dbf
EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret

목적:

손상된 데이터 파일 완전 복구
장애:

데이터 파일을 지워버렸다.


장애 재현:

SQL> connect /as sysdba
Connected.

SQL> connect /as sysdba
Connected.
SQL> connect test/1111
Connected.
SQL> select * from test;

        ID
----------
         1
         2
         3

SQL> commit;

Commit complete.


SQL> col table_name format a30

SQL> col owner format a20

SQL> select table_name,tablespace_name,owner from dba_tables where owner='TEST';

TABLE_NAME                     TABLESPACE_NAME OWNER
------------------------------ --------------- --------------------
TEST                           TEST            TEST


SQL> alter system checkpoint;

System altered.


SQL> alter system switch logfile;

System altered.


SQL> !rm /oracle/oradata/orcl/users01.dbf

SQL> !ls /oracle/oradata/orcl/user01.dbf
ls: /oracle/oradata/orcl/user01.dbf: No such file or directory

SQL> !ls /oracle/oradata/orcl/test.dbf
ls: /oracle/oradata/orcl/test.dbf: No such file or directory

SQL> !rm /oracle/oradata/orcl/test.dbf



복구


장애 파일 확인하고 오프라인으로 변경

SQL> col tablespace_name format a15
SQL> col error format a20
SQL> col name format a40

SQL> select h.tablespace_name, d.name, h.error from v$datafile d, v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- ----------
SYSTEM          /oracle/oradata/orcl/system01.dbf
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
                /oracle/oradata/orcl/users01.dbf         CANNOT
                                                         OPEN FILE

EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
                /oracle/oradata/orcl/test.dbf            CANNOT
                                                         OPEN FILE


7 rows selected.


SQL> select d.file#,d.name,d.status,h.status from v$datafile d,v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         ONLINE  ONLINE                          ---> 장애파일
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE                                --->장애 파일

7 rows selected.



SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' offline;

Database altered.

SQL> alter database datafile '/oracle/oradata/orcl/test.dbf' offline;

Database altered.


SQL> select d.file#,d.name,d.status,h.status from v$datafile d,v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         RECOVER OFFLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            RECOVER OFFLINE

7 rows selected.


장애 파일 백업에서 복사

SQL> !cp /backup/close/20110217/oradata/users01.dbf /oracle/oradata/orcl/

SQL> !cp /backup/close/20110217/oradata/test.dbf /oracle/oradata/orcl/


recover 명령으로 파일 복구


SQL> recover datafile '/oracle/oradata/orcl/users01.dbf'
ORA-00279: change 945400 generated at 02/17/2011 16:39:57 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_10_6osq55bn_.arc
ORA-00280: change 945400 for thread 1 is in sequence #10


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


ORA-00279: change 951877 generated at 02/17/2011 17:30:28 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_11_6osq99oz_.arc
ORA-00280: change 951877 for thread 1 is in sequence #11


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 952160 generated at 02/17/2011 17:32:41 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_12_6osqz7vm_.arc
ORA-00280: change 952160 for thread 1 is in sequence #12


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto       ---> 이부분은 직접 입력(아카이브로그 파일이 자동 적용)
ORA-00279: change 953139 generated at 02/17/2011 17:44:23 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_13_6osr2031_.arc
ORA-00280: change 953139 for thread 1 is in sequence #13


ORA-00279: change 953301 generated at 02/17/2011 17:45:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_14_6ot725sz_.arc
ORA-00280: change 953301 for thread 1 is in sequence #14


ORA-00279: change 973479 generated at 02/17/2011 22:01:56 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_15_6ot94gdz_.arc
ORA-00280: change 973479 for thread 1 is in sequence #15


ORA-00279: change 978379 generated at 02/17/2011 22:37:18 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_16_6ov0wgtn_.arc
ORA-00280: change 978379 for thread 1 is in sequence #16


Log applied.
Media recovery complete.

SQL> recover datafile '/oracle/oradata/orcl/test.dbf'
ORA-00279: change 945400 generated at 02/17/2011 16:39:57 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_10_6osq55bn_.arc
ORA-00280: change 945400 for thread 1 is in sequence #10


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 951877 generated at 02/17/2011 17:30:28 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_11_6osq99oz_.arc
ORA-00280: change 951877 for thread 1 is in sequence #11


ORA-00279: change 952160 generated at 02/17/2011 17:32:41 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_12_6osqz7vm_.arc
ORA-00280: change 952160 for thread 1 is in sequence #12


ORA-00279: change 953139 generated at 02/17/2011 17:44:23 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_13_6osr2031_.arc
ORA-00280: change 953139 for thread 1 is in sequence #13


ORA-00279: change 953301 generated at 02/17/2011 17:45:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_14_6ot725sz_.arc
ORA-00280: change 953301 for thread 1 is in sequence #14


ORA-00279: change 973479 generated at 02/17/2011 22:01:56 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_15_6ot94gdz_.arc
ORA-00280: change 973479 for thread 1 is in sequence #15


ORA-00279: change 978379 generated at 02/17/2011 22:37:18 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_16_6ov0wgtn_.arc
ORA-00280: change 978379 for thread 1 is in sequence #16


Log applied.
Media recovery complete.




SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' online;

Database altered.

SQL> alter database datafile '/oracle/oradata/orcl/test.dbf' online;

Database altered.


SQL> select d.file#,d.name,d.status,h.status from v$datafile d,v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         ONLINE  ONLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.



SQL> connect test/1111
Connected.
SQL> select * from test;

        ID
----------
         1
         2
         3


복구완료.

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret


아래 패키지 다운로드
rlwrap-0.30.tar.gz

아래 블로그에 보면 소스 다운로드를 받을 수 있다.
http://blog.naver.com/PostView.nhn?blogId=ydcrew&logNo=10037034303&redirect=Dlog&widgetTypeCall=true


설치:

./configure ; make ; make install


사용방법:

1. 아래 명령으로 사용
[oracle@oracle ~]$ rlwrap sqlplus /nolog

2. alias 설정해서 사용
alias sqlplus='rlwrap sqlplus'


이제 sqlplus /nolog 로 접속해서 방향키 이용하면 이전 명령어를 다시 사용할 수 있습니다.

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret

장애:
실수로 데이터 파일을 지워버렸다.
[root@oracle orcl]# ls
control01.ctl  example01.dbf  guk.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  test.dbf  undotbs01.dbf  users01.dbf
[root@oracle orcl]# rm -fv users01.dbf
removed `users01.dbf'


복구:

SQL> connect /as sysdba
Connected.

SQL> select d.file#,d.name,d.status,h.status from v$datafile d,v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         ONLINE  ONLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' offline;

Database altered.


SQL> select d.file#,d.name,d.status,h.status from v$datafile d,v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         RECOVER OFFLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.

SQL> !cp /backup/close/20110217/oradata/users01.dbf /oracle/oradata/orcl


SQL> recover datafile '/oracle/oradata/orcl/users01.dbf'
ORA-00279: change 945400 generated at 02/17/2011 16:39:57 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_10_6osq55bn_.arc
ORA-00280: change 945400 for thread 1 is in sequence #10


ORA-00279: change 951877 generated at 02/17/2011 17:30:28 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_11_6osq99oz_.arc
ORA-00280: change 951877 for thread 1 is in sequence #11


ORA-00279: change 952160 generated at 02/17/2011 17:32:41 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_12_6osqz7vm_.arc
ORA-00280: change 952160 for thread 1 is in sequence #12


ORA-00279: change 953139 generated at 02/17/2011 17:44:23 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_13_6osr2031_.arc
ORA-00280: change 953139 for thread 1 is in sequence #13


ORA-00279: change 953301 generated at 02/17/2011 17:45:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_14_6ot725sz_.arc
ORA-00280: change 953301 for thread 1 is in sequence #14


ORA-00279: change 973479 generated at 02/17/2011 22:01:56 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_15_6ot94gdz_.arc
ORA-00280: change 973479 for thread 1 is in sequence #15


Log applied.
Media recovery complete.


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' online;

Database altered.

SQL> select d.file#,d.name,d.status,h.status from v$datafile d,v$datafile_header h where d.file#=h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         ONLINE  ONLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.



저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret


완전복구 : 닫힌 데이터 베이스 복구


장애:
데이터 파일중 system 테이블 스페이스나 활성화된 undo(rollback) 세그먼트를 포함한
테이블스페이스에 포함된 데이터 파일이 손상
장애재현:
[root@oracle orcl]# cd /oracle/oradata/orcl/
[root@oracle orcl]# ls
control01.ctl  example01.dbf  guk.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  test.dbf  undotbs01.dbf  users01.dbf


여기서 system01.dbf 파일을 실수로 지워 버렸다.

[root@oracle orcl]# rm system01.dbf 
rm: remove regular file `system01.dbf'? y



복구:


오라클을 종료하면서 파일이 삭제된것을 확인하였다.
SQL> shutdown immediate
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> shutdown abort
ORACLE instance shut down.


SQL> startup mount
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
Database mounted.


확인 

SQL> col tablespace_name format a15
SQL> col name format a40
SQL> col error format a10
SQL> select h.tablespace_name,d.name,h.error from v$datafile d,v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- ----------
                /oracle/oradata/orcl/system01.dbf        FILE NOT
                                                         FOUND

SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
USERS           /oracle/oradata/orcl/users01.dbf
EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.


백업자료로 부터 복사

SQL> !cp /backup/close/20110217/oradata/system01.dbf /oracle/oradata/orcl

recover 명령으로 파일 복구

SQL> set autorecovery on

SQL> recover datafile '/oracle/oradata/orcl/system01.dbf'
ORA-00279: change 945400 generated at 02/17/2011 16:39:57 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_10_6osq55bn_.arc
ORA-00280: change 945400 for thread 1 is in sequence #10


ORA-00279: change 951877 generated at 02/17/2011 17:30:28 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_11_6osq99oz_.arc
ORA-00280: change 951877 for thread 1 is in sequence #11


ORA-00279: change 952160 generated at 02/17/2011 17:32:41 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_12_6osqz7vm_.arc
ORA-00280: change 952160 for thread 1 is in sequence #12


ORA-00279: change 953139 generated at 02/17/2011 17:44:23 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_13_6osr2031_.arc
ORA-00280: change 953139 for thread 1 is in sequence #13


ORA-00279: change 953301 generated at 02/17/2011 17:45:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_14_6ot725sz_.arc
ORA-00280: change 953301 for thread 1 is in sequence #14


Log applied.
Media recovery complete.


DB 오픈

SQL> alter database open;

Database altered.



확인 해 보면 정상적으로 복구된것을 확인할 수 있다.

SQL> select h.tablespace_name,d.name,h.error from v$datafile d,v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- ----------
SYSTEM          /oracle/oradata/orcl/system01.dbf
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
USERS           /oracle/oradata/orcl/users01.dbf
EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.

유저 접속 잘된다.

SQL> connect test/1111
Connected.
SQL> select * from test;

        ID
----------
         1
         2

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret

환경

ORACLE_HOME : /oracle/11g
데이터 디렉토리 : /oracle/oradata/orcl
패스워드 파일, 파라미터 파일 : /oracle/11g/dbs
아카이브 로그 디렉토리 : /arch
백업디렉토리 : /backup/open/oradata -> 데이터 , 컨트롤 파일
 /backup/open/dbs -> 패스워드, 파라미터 파일

 


백업 스크립트

#!/bin/sh


###############
# 디렉토리 경로
###############
BACKUPHOME=/backup/open
ORACLE_HOME=/oracle/11g
ARCHIVEHOME=/arch
ARCHIVEIMSY=/backup/imsy/arch
DBSHOME=$ORACLE_HOME/dbs
ORADATA=/oracle/oradata/orcl

#######################
# 디렉토리 생성 및 삭제
#######################

# 백업 디렉토리 현재 날짜로 생성
DATE=`date '+%Y%m%d'`
BACKUP=$BACKUPHOME/$DATE
mkdir -p $BACKUP
chown oracle.oinstall $BACKUP

# 7일 이전 디렉토리 삭제
DELDATE=`date +%Y%m%d --date 7' days ago'`
rm -rf $BACKUPHOME/$DELDATE

# 테이블 스페이스 데이터 폴더 생성
DATAFILE=$BACKUP/oradata
mkdir -p $DATAFILE
chown oracle.oinstall $DATAFILE

# 패스워드 , 파라미터 파일 폴더 생성
DBS=$BACKUP/dbs
mkdir -p $DBS
chown oracle.oinstall $DBS

# ARCHIVE 임시 디렉토리 생성
mkdir -p $ARCHIVEIMSY
chown oracle.oinstall $ARCHIVEIMSY


su - oracle -c "
sqlplus /nolog  << EOF
set echo off
connect /as sysdba

REM
REM 백업 이전 만들어진 아카이브 로그 파일 임시 디렉토리로 이동
REM 백업중 장애로 인한 백업 실패 대비
REM

host cp -rpv $ARCHIVEHOME/*.* $ARCHIVEIMSY
alter system switch logfile;

REM
REM 패스워드 파일과 파라미터 파일 백업
REM

host cp -rpv $DBSHOME/* $DBS

REM
REM 데이터 파일의 온라인 백업
REM


REM SYSTEM 테이블 스페이스

alter tablespace system begin backup;
host cp -pv $ORADATA/system01.dbf $DATAFILE
alter tablespace system end backup;

REM USERS 테이블 스페이스

alter tablespace users begin backup;
host cp -pv $ORADATA/users01.dbf $DATAFILE
alter tablespace users end backup;


REM UNDOTBS1 테이블 스페이스

alter tablespace undotbs1 begin backup;
host cp -pv $ORADATA/undotbs01.dbf $DATAFILE
alter tablespace undotbs1 end backup;

REM SYSAUX 테이블 스페이스

alter tablespace sysaux begin backup;
host cp -pv $ORADATA/sysaux01.dbf $DATAFILE
alter tablespace sysaux end backup;

REM EXAMPLE 테이블 스페이스

alter tablespace example begin backup;
host cp -pv $ORADATA/example01.dbf $DATAFILE
alter tablespace example end backup;

REM GUK 테이블 스페이스

alter tablespace guk begin backup;
host cp -pv $ORADATA/guk.dbf $DATAFILE
alter tablespace guk end backup;

REM
REM 로그 스위치를 일으킴
REM

alter system switch logfile;
alter system checkpoint;

REM
REM 컨트롤 파일 열린 백업
REM

alter database backup controlfile to '$DATAFILE/control.ctl';

REM
REM 임시 디렉토리에 저장한 아카이브 로그 파일 삭제
REM

host rm -rf $ARCHIVEIMSY/*.*

exit
EOF
"

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret
1. 아카이브 모드에서만 사용할 수 있다.
2. 무정지시스템에서 사용한다.
3. 컨트롤 파일은 별도 백업해야한다.
4. 백업스크립트를 이용하면 작업량,입력에러를 줄인다.

[ref] 오라클 백업과 복구 , 한빛미디어
저작자 표시 비영리 변경 금지
신고

'서버관리' 카테고리의 다른 글

SET ECHO 사용법 [ok]  (0) 2011.01.27
주석 REM [ok]  (0) 2011.01.27
[오라클] 열린 백업에 대한 요약  (0) 2011.01.27
[MYSQL] MYD MYI frm 인 무엇인가? [ok]  (0) 2011.01.27
아카이브 로그 모드 그림.  (0) 2011.01.27
[ORACLE] col 테이블이름 format a(interger)  (0) 2011.01.27

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret

목적
열린 백업하기

환경
   
[oracle@oracle ~]$ echo $ORACLE_HOME
/oracle/11g
/oracle/oradata/orcl : 데이터 디렉토리
/backup : 열린 백업 위치
/arch : 아카이브 디렉토리
* 열린 백업 모든 테이블 스페이스 각각 백업해야함.

주요과정

1. 백업 대상 파일 확인
2. 열린 백업 수행
3. alter system switch logfile 실행

1. 백업 대상 파일 확인


SQL> col tablespace_name format a20       
SQL> col file_name format a45
SQL> select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME  BYTES FILE_NAME
-------------------- ---------- ---------------------------------------------
USERS 5242880 /oracle/oradata/orcl/users01.dbf
UNDOTBS1       89128960 /oracle/oradata/orcl/undotbs01.dbf
SYSAUX      513802240 /oracle/oradata/orcl/sysaux01.dbf
SYSTEM      713031680 /oracle/oradata/orcl/system01.dbf
EXAMPLE      104857600 /oracle/oradata/orcl/example01.dbf
GUK      104857600 /home/app/oradata/orcl/guk.dbf

6 rows selected.


2. 열린 백업 수행


SQL> alter tablespace system begin backup;

Tablespace altered.


SQL> select v$datafile.name, v$backup.status from v$datafile, v$backup where v$datafile.file# = v$backup.file#;

NAME
--------------------------------------------------------------------------------
STATUS
------------------
/oracle/oradata/orcl/system01.dbf
ACTIVE

/oracle/oradata/orcl/sysaux01.dbf
NOT ACTIVE

/oracle/oradata/orcl/undotbs01.dbf
NOT ACTIVE


NAME
--------------------------------------------------------------------------------
STATUS
------------------
/oracle/oradata/orcl/users01.dbf
NOT ACTIVE

/oracle/oradata/orcl/example01.dbf
NOT ACTIVE

/home/app/oradata/orcl/guk.dbf
NOT ACTIVE


6 rows selected.


SYSTEM 테이블 스페이스 열린 백업

SQL> !cp /oracle/oradata/orcl/system01.dbf /backup/

SQL> alter tablespace system end backup;

Tablespace altered.


USERS 테이블 스페이스 열린 백업

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp /oracle/oradata/orcl/users01.dbf /backup/

SQL> alter tablespace users end backup;

Tablespace altered.


UNDOTBS1 테이블 스페이스 열린 백업


SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp /oracle/oradata/orcl/undotbs01.dbf /backup/

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.


SYSAUX 테이블 스페이스 열린 백업

SQL> alter tablespace SYSAUX begin backup;

Tablespace altered.

SQL> !cp /oracle/oradata/orcl/sysaux01.dbf /backup/

SQL> alter tablespace SYSAUX end backup;

Tablespace altered.


EXAMPLE 테이블 스페이스 열린 백업

SQL> alter tablespace EXAMPLE begin backup;

Tablespace altered.

SQL> !cp /oracle/oradata/orcl/example01.dbf /backup/

SQL> alter tablespace EXAMPLE end backup;

Tablespace altered.


GUK 테이블 스페이스 열린 백업

SQL> alter tablespace GUK begin backup;

Tablespace altered.

SQL> !cp /oracle/oradata/orcl/guk.dbf /backup

SQL> alter tablespace GUK end backup;

Tablespace altered.


ALTER SYSTEM SWITCH LOGFILE 실행

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> !ls /backup   ---> 백업 된것 확인
example01.dbf  guk.dbf sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf


컨트롤 파일 열린 백업

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control01.ctl
/oracle/flash_recovery_area/orcl/control02.ctl


SQL> alter database backup controlfile to '/backup/control.ctl';

Database altered.

SQL> !ls -al /backup
total 1506128
drwxr-xr-x  2 oracle oinstall      4096 Jan 27 11:45 .
drwxr-xr-x 27 root   root          4096 Jan 27 04:12 ..
-rw-r-----  1 oracle oinstall   9748480 Jan 27 11:45 control.ctl
-rw-r-----  1 oracle oinstall 104865792 Jan 27 11:41 example01.dbf
-rw-r-----  1 oracle oinstall 104865792 Jan 27 11:42 guk.dbf
-rw-r-----  1 oracle oinstall 513810432 Jan 27 11:40 sysaux01.dbf
-rw-r-----  1 oracle oinstall 713039872 Jan 27 11:29 system01.dbf
-rw-r-----  1 oracle oinstall  89137152 Jan 27 11:38 undotbs01.dbf
-rw-r-----  1 oracle oinstall   5251072 Jan 27 11:33 users01.dbf


[ref] 오라클 백업과 복구 ,출판사:한빛미디어

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret