Archive log mode Complete Recovery

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 9. 12:09
Non System Tablespace Recovery

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/arc2
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL> create tablespace ts_a
  2  datafile '/home/oracle/oradata/testdb/ts_a01.dbf' size 5m;

Tablespace created.

SQL> create tablespace ts_b
  2  datafile '/home/oracle/oradata/testdb/ts_b01.dbf' size 5m;

Tablespace created.

SQL> create tablespace ts_c
  2  datafile '/home/oracle/oradata/testdb/ts_c01.dbf' size 5m;

Tablespace created.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> create table test1 (no number) tablespace ts_c;

Table created.

SQL> insert into test1 values (1);

1 row created.

SQL> insert into test1 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

        NO
----------
         1
         2

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter tablespace ts_a begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_a01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/open/ts_a01.dbf'

SQL> alter tablespace ts_a end backup;

Tablespace altered.

SQL> alter tablespace ts_b begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_b01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/open/ts_b01.dbf'

SQL> alter tablespace ts_b end backup;

Tablespace altered.

SQL> alter tablespace ts_c begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_c01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/open/ts_c01.dbf'

SQL> alter tablespace ts_c end backup;

Tablespace altered.

SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> !rm -rf /home/oracle/oradata/testdb/ts_c01.dbf

SQL> alter tablespace ts_c offline;

Tablespace altered.

SQL> alter tablespace ts_c online;
alter tablespace ts_c online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/home/oracle/oradata/testdb/ts_c01.dbf'


SQL> select * from test1;
select * from test1
              *
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/home/oracle/oradata/testdb/ts_c01.dbf'


SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                            /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> !cp /data/backup/open/ts_c01.dbf /home/oracle/oradata/testdb/

SQL> recover tablespace ts_c;
Media recovery complete.
SQL> alter tablespace ts_c online;

Tablespace altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> select * from test1;

        NO
----------
         1
         2

SQL> 



System Tablespace Recovery

#1 recover database;
SQL> create table test3 (no number) tablespace system;

Table created.

SQL> insert into test3 values (1);

1 row created.

SQL> insert into test3 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

        NO
----------
         1
         2

SQL> !rm -rf /home/oracle/oradata/testdb/system01.dbf

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

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

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'


SQL> !cp /data/backup/open/system01.dbf /home/oracle/oradata/testdb/

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from test3;

        NO
----------
         1
         2

SQL> 


#2 recover tablespace tablespace_name;
SQL> truncate table test3;

Table truncated.

SQL> select * from test3;

no rows selected

SQL> insert into test3 values (3);

1 row created.

SQL> insert into test3 values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

        NO
----------
         3
         4

SQL> !rm -rf /home/oracle/oradata/testdb/system01.dbf

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

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

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'


SQL> !cp /data/backup/open/system01.dbf /home/oracle/oradata/testdb/

SQL> recover tablespace system;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from test3;

        NO
----------
         3
         4

SQL> 

#3 recover datafile '/home/oracle/oradata/testdb/system01.dbf';
SQL> truncate table test3;

Table truncated.

SQL> insert into test3 values (5);

1 row created.

SQL> insert into test3 values (6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

        NO
----------
         5
         6

SQL> !rm -rf /home/oracle/oradata/testdb/system01.dbf

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

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

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'


SQL> !cp /data/backup/open/system01.dbf /home/oracle/oradata/testdb/

SQL> recover datafile '/home/oracle/oradata/testdb/system01.dbf';
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from test3;

        NO
----------
         5
         6

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Incomplete Recovery - using backup controlfile  (0) 2010.03.11
Incomplete Recovery - Time Base  (0) 2010.03.10
No Archive log mode Recovery  (0) 2010.03.08
Cold Backup & Hot Backup  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05

No Archive log mode Recovery

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 8. 12:49
No Archive log mode Recovery #1

SQL> !rm -rf /home/oracle/oradata/testdb/users01.dbf 

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'


SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE STATUS    CONTENTS
---------- --------- ---------
SYSTEM     ONLINE    PERMANENT
UNDOTBS1   ONLINE    UNDO
SYSAUX     ONLINE    PERMANENT
TEMP       ONLINE    TEMPORARY
USERS      OFFLINE   PERMANENT
EXAMPLE    ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:11:10:04
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:11:11:07
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:11:10:38
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:11:12:16

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /data/backup/close/*.dbf /home/oracle/oradata/testdb/
`/data/backup/close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> !cp -av /data/backup/close/*.ctl /home/oracle/oradata/testdb/
`/data/backup/close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'
`/data/backup/close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'
`/data/backup/close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

SQL> !cp -av /data/backup/close/*.log /home/oracle/oradata/testdb/
`/data/backup/close/redo01.log' -> `/home/oracle/oradata/testdb/redo01.log'
`/data/backup/close/redo02.log' -> `/home/oracle/oradata/testdb/redo02.log'
`/data/backup/close/redo03.log' -> `/home/oracle/oradata/testdb/redo03.log'

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE STATUS    CONTENTS
---------- --------- ---------
SYSTEM     ONLINE    PERMANENT
UNDOTBS1   ONLINE    UNDO
SYSAUX     ONLINE    PERMANENT
TEMP       ONLINE    TEMPORARY
USERS      ONLINE    PERMANENT
EXAMPLE    ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:10:55:11
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> 


No Archive log mode Recovery #2 (해당 Tablespace가 지워져도 괜찮을 경우에 한하여 사용한다.)
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col name format a50
SQL> select tablespace_name, status, contents from dba_tablespaces;                      

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:10:55:11
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> !rm /home/oracle/oradata/testdb/users01.dbf

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'


SQL> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> 


No Archive log mode Recovery #3
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> !rm -rf /home/oracle/oradata/testdb/*

SQL> !ls /home/oracle/oradata/testdb/

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir -p /data/temp 

SQL> !cp -av /data/close/*.dbf /data/temp/
cp: cannot stat `/data/close/*.dbf': 그런 파일이나 디렉토리가 없음

SQL> !cp -av /data/backup/close/*.dbf /data/temp/
`/data/backup/close/example01.dbf' -> `/data/temp/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/data/temp/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/data/temp/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/data/temp/temp01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/data/temp/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/data/temp/users01.dbf'

SQL> !cp -av /data/backup/close/*.ctl /home/oracle/oradata/testdb/
`/data/backup/close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'
`/data/backup/close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'
`/data/backup/close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

SQL> !cp -av /data/backup/close/*.log /home/oracle/oradata/testdb/
`/data/backup/close/redo01.log' -> `/home/oracle/oradata/testdb/redo01.log'
`/data/backup/close/redo02.log' -> `/home/oracle/oradata/testdb/redo02.log'
`/data/backup/close/redo03.log' -> `/home/oracle/oradata/testdb/redo03.log'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select name from v$datafile; 

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/data/temp/system01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/sysaux01.dbf'
  3  to '/data/temp/sysaux01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/undotbs01.dbf'
  3  to '/data/temp/undotbs01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/users01.dbf'
  3  to '/data/temp/users01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/example01.dbf'
  3  to '/data/temp/example01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME                        MB FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS                                     /data/temp/users01.dbf
SYSAUX                                240 /data/temp/sysaux01.dbf
UNDOTBS1                               35 /data/temp/undotbs01.dbf
SYSTEM                                480 /data/temp/system01.dbf
EXAMPLE                               100 /data/temp/example01.dbf

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09
Cold Backup & Hot Backup  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Privileges(권한) 관리  (0) 2010.03.05

Restore & Recovery

Oracle 10g/10g - Admin I
posted by I유령I 2010. 3. 8. 11:30
Restore : Backup 받았던 파일을 복원 시키는 것
Recovery : 복원된 파일을 Redo log file을 적용시켜서 복구하는 것

복구 원리
Recover 명령 -> Control file 정보 확인 -> Data file 정보 확인 -> Control file  과 Data file 정보가 다를 경우 Redo log file 또는 Archived log file의 정보를 적용시켜 복구를 완료한다.

복구 명령어
SQL> recover database; ※ mount 단계에서만 사용이 가능한 명령어
SQL> recover tablespace tablespace_name;
SQL> recover datafile '/home/oracle/oradata/testdb/system01.dbf';
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - Admin I' 카테고리의 다른 글

Export / Import  (0) 2010.03.15
Backup  (0) 2010.03.08
sysdba 권한 로그인시 패스워드 지정  (0) 2010.03.05
프로파일 & 유저 관리 & 권한 관리  (0) 2010.03.05
Constraints (제약 조건)  (0) 2010.03.05

Cold Backup & Hot Backup

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 8. 11:05
Cold Backup

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 8 10:28:54 2010

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col name format a50
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/control01.ctl
/home/oracle/oradata/testdb/control02.ctl
/home/oracle/oradata/testdb/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/home/oracle/oradata/testdb/redo03.log
/home/oracle/oradata/testdb/redo02.log
/home/oracle/oradata/testdb/redo01.log

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/close/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/close/example01.dbf'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/close/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/close/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/close/temp01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/close/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/close/users01.dbf'

SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/close/
`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/close/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/close/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/close/control03.ctl'

SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/close/
`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/close/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/close/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/close/redo03.log'

SQL> !cp -av $ORACLE_HOME/dbs /data/backup/close/
`/home/oracle/product/10g/dbs' -> `/data/backup/close/dbs'
`/home/oracle/product/10g/dbs/orapwtestdb' -> `/data/backup/close/dbs/orapwtestdb'
`/home/oracle/product/10g/dbs/init.ora' -> `/data/backup/close/dbs/init.ora'
`/home/oracle/product/10g/dbs/initdw.ora' -> `/data/backup/close/dbs/initdw.ora'
`/home/oracle/product/10g/dbs/spfiletestdb.ora' -> `/data/backup/close/dbs/spfiletestdb.ora'
`/home/oracle/product/10g/dbs/lkTESTDB' -> `/data/backup/close/dbs/lkTESTDB'
`/home/oracle/product/10g/dbs/hc_testdb.dat' -> `/data/backup/close/dbs/hc_testdb.dat'

SQL> 



Hot Backup

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col tablespace_name format a10
SQL> col file_name format a50
SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE STATUS    CONTENTS
---------- --------- ---------
SYSTEM     ONLINE    PERMANENT
UNDOTBS1   ONLINE    UNDO
SYSAUX     ONLINE    PERMANENT
TEMP       ONLINE    TEMPORARY
USERS      ONLINE    PERMANENT
EXAMPLE    ONLINE    PERMANENT

6 rows selected.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE         MB FILE_NAME
---------- ---------- --------------------------------------------------
USERS               5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           100 /home/oracle/oradata/testdb/example01.dbf

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:11:10:04
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:11:11:07
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:11:10:38
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:11:11:53
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:11:12:16

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Archive log mode Complete Recovery  (0) 2010.03.09
No Archive log mode Recovery  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Privileges(권한) 관리  (0) 2010.03.05
Profile(프로파일) 생성과 관리  (0) 2010.03.05

Backup

Oracle 10g/10g - Admin I
posted by I유령I 2010. 3. 8. 10:45
1. 실습 환경 설정
1) 사용 O/S : Red Hat Enterprise Linux 4
2) Oracle Database : Oracle Database 10g R2 (10.2.0.1)
3) 각 원본 파일 위치
- Data file : /home/oracle/oradata/testdb/*.dbf
- Redo log file : /home/oracle/oradata/testdb/*.log
- Control file : /home/oracle/oradata/testdb/*.ctl
4) Archived log file 위치 : /data/arc1, /data/arc2
5) 백업 경로
- /data/backup/close : Cold Backup 경로
- /data/backup/open : Hot Backup 경로

2. 백업 대상 파일
1) Data file
2) Redo log file
3) Control file
4) Parameter file
5) Password file
※ 1), 2), 3) 은 필수적인 백업 파일이며4), 5) 는 추후 복구할 때 없으면 불편하기 때문에 백업을 받는 파일이다.

3. 오라클 백업 관련 운영 모드
Archive log mode
- Online Redo log 파일을 다른 장소로 하니 더 Archive 해서 혹시 발생할 지 모르는 Online redo log 파일의 장애를 대비하는 방법이다.
- 관리자가 별도로 설정해야 하며 Archived log가 저장될 별도의 저장 공간이 필요하다.
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - Admin I' 카테고리의 다른 글

Export / Import  (0) 2010.03.15
Restore & Recovery  (0) 2010.03.08
sysdba 권한 로그인시 패스워드 지정  (0) 2010.03.05
프로파일 & 유저 관리 & 권한 관리  (0) 2010.03.05
Constraints (제약 조건)  (0) 2010.03.05

Archive log mode 설정 및 활성화/비활성화

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 5. 12:32
■ Archive log mode 사용을 위한 설정
[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 12:10:55 2010

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.

#Archive log의 기본 저장 영역은 $ORACLE_BASE/flash_recovery_area 이며, 사용 가능한 기본
#용량은 2GB 이다.
#flash_recovery_area는 archive log 뿐만 아니라 flashback log, rman 등 Backup & Recovery
#관련 데이터가 저장되어 Archive log mode를 활성화하여 사용할 경우 디스크 용량 부족으로 인해
#Database에 문제가 발생될 수 있다.
#디스크 용량 부족으로 인한 문제를 방지하기 위해 아래와 같이 Archive log가 저장될 저장 장소를
#별도로 지정한다.
#spfile 사용시
#log_archive_dest_1 디렉토리 지정
SQL> alter system set log_archive_dest_1='location=/data/arc1' scope=spfile;

System altered.

#log_archive_dest_2 디렉토리 지정
SQL> alter system set log_archive_dest_2='location=/data/arc2' scope=spfile;

System altered.

#log_archive_format 지정
SQL> alter system set log_archive_format='%s_%t_%r.arc' scope=spfile;

System altered.

#pfile 사용시
SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.log_archive_dest_1='location=/data/arc1'
*.log_archive_dest_2='location=/data/arc2'
*.log_archive_format='%s_%t_%r.arc'

SQL> 

#위에서 지정한 log_archive 디렉토리 및 포맷 적용을 위한 재 시작
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

#Archive log mode 활성화를 위해 mount 단계로 startup 한다.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.

#Archive log mode 상태 확인
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data/arc2
Oldest online log sequence     2
Current log sequence           4

#Archive log mode를 활성화 한다.
SQL> alter database archivelog;

Database altered.

#Archive log mode 상태 재 확인
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/arc2
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.

#Archive log mode를 비활성화 한다.
SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data/arc2
Oldest online log sequence     2
Current log sequence           4
SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

No Archive log mode Recovery  (0) 2010.03.08
Cold Backup & Hot Backup  (0) 2010.03.08
Privileges(권한) 관리  (0) 2010.03.05
Profile(프로파일) 생성과 관리  (0) 2010.03.05
User(유저) 생성과 관리  (0) 2010.03.05

sysdba 권한 로그인시 패스워드 지정

Oracle 10g/10g - Admin I
posted by I유령I 2010. 3. 5. 11:36
[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 11:35:00 2010

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

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@ghost ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

# sqlnet.ora Network Configuration File: /home/oracle/product/10g/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.AUTHENTICATION_SERVICES=(NONE)
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"product/10g/network/admin/sqlnet.ora" 6L, 218C 저장 했습니다 
[oracle@ghost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 11:37:51 2010

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

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ghost ~]$ 
[oracle@ghost ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 11:38:04 2010

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

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@ghost ~]$ 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - Admin I' 카테고리의 다른 글

Restore & Recovery  (0) 2010.03.08
Backup  (0) 2010.03.08
프로파일 & 유저 관리 & 권한 관리  (0) 2010.03.05
Constraints (제약 조건)  (0) 2010.03.05
Index (인덱스)  (0) 2010.03.05

Backup & Recovery용 Data 디스크 추가

O/S/LiNUX
posted by I유령I 2010. 3. 5. 10:24
Last login: Sun Feb 28 21:25:28 2010 from 192.168.0.1
[oracle@ghost ~]$ su -
Password: 
[root@ghost ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14         650     5116702+  83  Linux
/dev/sda3             651         911     2096482+  82  Linux swap
/dev/sda4             912        2610    13647217+   5  Extended
/dev/sda5             912        1038     1020096   83  Linux
/dev/sda6            1039        2610    12627058+  83  Linux

Disk /dev/sdb: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table
[root@ghost ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 2610.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-2610, default 2610): 
Using default value 2610

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ghost ~]# mkfs.ext3 /dev/sdb1
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
2621440 inodes, 5241198 blocks
262059 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
160 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000

Writing inode tables: done                            
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 21 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@ghost data]# vi /etc/fstab 

/dev/sdb1               /disk1                  ext3    defaults        1 2

[root@ghost data]# exit
logout

[oracle@ghost ~]$ mkdir -p /data/arc1 /data/arc2
[oracle@ghost ~]$ mkdir -p /data/backup/close /data/backup/open /data/backup/rman
[oracle@ghost ~]$ df -kh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             4.9G  2.8G  1.9G  60% /
/dev/sda1              99M  8.5M   86M  10% /boot
none                  506M     0  506M   0% /dev/shm
/dev/sda6              12G  3.1G  8.2G  28% /home
/dev/sda5             981M  100M  832M  11% /var
/dev/sdb1              20G   77M   19G   1% /data
[oracle@ghost ~]$ 
저작자표시 비영리 변경금지 (새창열림)

'O/S > LiNUX' 카테고리의 다른 글

RHEL4 디스크 추가  (0) 2010.02.28

Privileges(권한) 관리

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 5. 03:37

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Cold Backup & Hot Backup  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Profile(프로파일) 생성과 관리  (0) 2010.03.05
User(유저) 생성과 관리  (0) 2010.03.05
Constraints 실습 (제약 조건 테스트)  (0) 2010.03.05

Profile(프로파일) 생성과 관리

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 5. 03:35

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Privileges(권한) 관리  (0) 2010.03.05
User(유저) 생성과 관리  (0) 2010.03.05
Constraints 실습 (제약 조건 테스트)  (0) 2010.03.05
Table : Partitioned Table  (0) 2010.03.04

티스토리툴바