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

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

Constraints 실습 (제약 조건 테스트)

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 5. 03:30
■ 제약조건 테스트

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 02:12:40 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              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> conn hr/hr
Connected.
SQL> create table testing(no number(5) check (no in (10, 20, 30))
  2  deferrable initially deferred);

Table created.

#commit 시 제약조건 검사
SQL> alter session set constraint = deferred;

Session altered.

SQL> insert into testing values(100);

1 row created.

SQL> insert into testing values(20);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.SYS_C005393) violated


SQL> select * from testing;

no rows selected

#DML 작업시 제약조건 즉시 검사
SQL> alter session set constraint = immediate;

Session altered.

SQL> insert into testing values(100);
insert into testing values(100)
*
ERROR at line 1:
ORA-02290: check constraint (HR.SYS_C005393) violated


SQL> insert into testing values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> 



■ 제약조건 활성화 / 비활성화 테스트
SQL> desc user_constraints;
 Name                                                                                                 Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 OWNER                                                                                                NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                                                                                      NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                                                                       VARCHAR2(1)
 TABLE_NAME                                                                                           NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                                                                      LONG
 R_OWNER                                                                                               VARCHAR2(30)
 R_CONSTRAINT_NAME                                                                                     VARCHAR2(30)
 DELETE_RULE                                                                                           VARCHAR2(9)
 STATUS                                                                                                VARCHAR2(8)
 DEFERRABLE                                                                                            VARCHAR2(14)
 DEFERRED                                                                                              VARCHAR2(9)
 VALIDATED                                                                                             VARCHAR2(13)
 GENERATED                                                                                             VARCHAR2(14)
 BAD                                                                                                   VARCHAR2(3)
 RELY                                                                                                  VARCHAR2(4)
 LAST_CHANGE                                                                                           DATE
 INDEX_OWNER                                                                                           VARCHAR2(30)
 INDEX_NAME                                                                                            VARCHAR2(30)
 INVALID                                                                                               VARCHAR2(7)
 VIEW_RELATED                                                                                          VARCHAR2(14)

SQL> col search_condition format a50;
SQL> select constraint_name, search_condition from user_constraints
  2  where table_name = 'TESTING';

CONSTRAINT_NAME                SEARCH_CONDITION
------------------------------ --------------------------------------------------
SYS_C005393                    no in (10, 20, 30)

#제약조건 비활성화
SQL> alter table testing modify constraint SYS_C005393 disable;

Table altered.

#제약조건 no in (10, 20, 30)에 만족하지 않지만 비활성화 상태이기 때문에 DML이 가능하다.
SQL> insert into testing values(50);

1 row created.

SQL> insert into testing values(30);

1 row created.

SQL> commit;

Commit complete.

#제약조건 활성화
SQL> alter table testing modify constraint SYS_C005393 enable novalidate;

Table altered.

#제약조건 no in (10, 20, 30)이 활성화 상태이기 때문에 DML 작업시 에러가 발생한다.
SQL> insert into testing values(50);
insert into testing values(50)
*
ERROR at line 1:
ORA-02290: check constraint (HR.SYS_C005393) violated


SQL> insert into testing values(20);

1 row created.

SQL> commit;

Commit complete.

SQL> 



■ EXCEPTIONS Table 사용법
SQL> @?/rdbms/admin/utlexcpt   

Table created.

SQL> desc exceptions;
 Name                                                                                                 Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ROW_ID                                                                                                ROWID
 OWNER                                                                                                 VARCHAR2(30)
 TABLE_NAME                                                                                            VARCHAR2(30)
 CONSTRAINT                                                                                            VARCHAR2(30)

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

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

Profile(프로파일) 생성과 관리  (0) 2010.03.05
User(유저) 생성과 관리  (0) 2010.03.05
Table : Partitioned Table  (0) 2010.03.04
Table : 테이블의 공간 할당과 해제  (0) 2010.03.04
Table : Row Migration과 Chaining  (0) 2010.03.04

종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 2. 28. 20:49

아래와 같이 데이터베이스 구성하기 (10GB 디스크 5개 추가)
/disk1/system01.dbf, sysaux01.dbf
/disk2/undotbs01.dbf
/disk3/users01.dbf, insa01.dbf, example01.dbf
/disk4/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/disk5/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log



#디스크 추가 방법은 LiNUX 카테고리의 "RHEL4 디스크 추가" 글의 내용을 참고하기 바란다.

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 28 19:51:09 2010

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

Connected to an idle instance.

#startup한 후 데이터 파일, 리두 로그 그룹/멤버를 확인한다.
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> col member format a50
SQL> col tablespace_name format a20
SQL> col file_name format a50
SQL> select tablespace_name, bytes, file_name
  2  from dba_data_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                   5242880 /home/oracle/disk5/users01.dbf
SYSAUX                251658240 /home/oracle/disk4/sysaux01.dbf
UNDOTBS1               36700160 /home/oracle/disk4/undotbs01.dbf
SYSTEM                503316480 /home/oracle/disk3/system01.dbf
EXAMPLE               104857600 /home/oracle/disk5/example01.dbf

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01_a.log                            50 NO  INACTIVE
         1 /home/oracle/disk2/redo01_b.log                            50 NO  INACTIVE
         2 /home/oracle/disk1/redo02_a.log                            50 NO  CURRENT
         2 /home/oracle/disk2/redo02_b.log                            50 NO  CURRENT
         3 /home/oracle/disk1/redo03_a.log                            50 NO  INACTIVE
         3 /home/oracle/disk2/redo03_b.log                            50 NO  INACTIVE

6 rows selected.

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

#파리미터 파일(inittestdb.ora) 수정
SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/disk4/control01.ctl',
                '/disk5/control02.ctl'

#파리미터 파일에서 수정한 디렉토리로 파일 이동
SQL> !cp /home/oracle/disk1/control01.ctl /disk4/control01.ctl

SQL> !cp /home/oracle/disk1/control01.ctl /disk5/control02.ctl

#연습문제 내용과 같은 디렉토리로 각 파일 이동
SQL> !mv /home/oracle/disk3/system01.dbf /disk1/system01.dbf

SQL> !mv /home/oracle/disk4/sysaux01.dbf /disk1/sysaux01.dbf

SQL> !mv /home/oracle/disk4/undotbs01.dbf /disk2/undotbs01.dbf

SQL> !mv /home/oracle/disk5/users01.dbf /disk3/users01.dbf

SQL> !mv /home/oracle/disk5/example01.dbf /disk3/example01.dbf

SQL> !mv /home/oracle/disk1/redo01_a.log /disk4/redo01_a.log

SQL> !mv /home/oracle/disk2/redo01_b.log /disk5/redo01_b.log

SQL> !mv /home/oracle/disk1/redo02_a.log /disk4/redo02_a.log

SQL> !mv /home/oracle/disk2/redo02_b.log /disk5/redo02_b.log

SQL> !mv /home/oracle/disk1/redo03_a.log /disk4/redo03_a.log

SQL> !mv /home/oracle/disk2/redo03_b.log /disk5/redo03_b.log

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.
#Control file 내용 수정
SQL> alter database rename
  2  file '/home/oracle/disk3/system01.dbf'
  3  to '/disk1/system01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk4/sysaux01.dbf'
  3  to '/disk1/sysaux01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk4/undotbs01.dbf'
  3  to '/disk2/undotbs01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk5/users01.dbf'
  3  to '/disk3/users01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk5/example01.dbf'
  3  to '/disk3/example01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk1/redo01_a.log'
  3  to '/disk4/redo01_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo01_b.log'
  3  to '/disk5/redo01_b.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk1/redo02_a.log'
  3  to '/disk4/redo02_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo02_b.log'
  3  to '/disk5/redo02_b.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk1/redo03_a.log'
  3  to '/disk4/redo03_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo03_b.log'
  3  to '/disk5/redo03_b.log';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/disk4/control01.ctl
/disk5/control02.ctl

SQL> select tablespace_name, bytes, file_name
  2  from dba_data_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                   5242880 /disk3/users01.dbf
SYSAUX                251658240 /disk1/sysaux01.dbf
UNDOTBS1               36700160 /disk2/undotbs01.dbf
SYSTEM                503316480 /disk1/system01.dbf
EXAMPLE               104857600 /disk3/example01.dbf

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /disk4/redo01_a.log                                        50 NO  INACTIVE
         1 /disk5/redo01_b.log                                        50 NO  INACTIVE
         2 /disk4/redo02_a.log                                        50 NO  CURRENT
         2 /disk5/redo02_b.log                                        50 NO  CURRENT
         3 /disk4/redo03_a.log                                        50 NO  INACTIVE
         3 /disk5/redo03_b.log                                        50 NO  INACTIVE

6 rows selected.

SQL>

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

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Complete Recovery 연습 문제  (0) 2010.03.09
No Archive log mode Recovery 연습문제  (0) 2010.03.09
종합 연습문제 (Controlfile, Redo log file, Tablespace)  (0) 2010.02.27
Controlfile, Redo log files 재구성 #2  (0) 2010.02.25
Controlfile, Redo log files 재구성 #1  (0) 2010.02.25

티스토리툴바