Admin 2. 04장 Recovering from Noncritical Losses

--모든 current redolog 그룹 멤버가 손실된 경우.(데이터 lose는 존재함.)  but 인스턴스는 open 할 수 있음.

 

S SYS> select group# from v$log where status='CURRENT';

    GROUP#
----------
         2

S SYS> select member from v$logfile where group#=2;

MEMBER
--------------------------------------------------------------------------------                                                                                                 ------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_50sxsbt9_.log
/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_50sxscdm_.log

S SYS> !rm /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_50sxsbt9_.log

S SYS> !rm /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_50sxscdm_.                                                                                                 log

S SYS> startup force
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              83888372 bytes
Database Buffers           79691776 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:

'/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_50sxscdm_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1:

'/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_50sxsbt9_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

 

 

S SYS> recover database until cancel;
ORA-00279: change 11612881 generated at 06/19/2009 15:00:08 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_19/o1_mf_1_302_%u_.arc
ORA-00280: change 11612881 for thread 1 is in sequence #302

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 11618376 generated at 06/19/2009 16:09:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_19/o1_mf_1_303_%u_.arc
ORA-00280: change 11618376 for thread 1 is in sequence #303
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_19/o1_mf_1_302_53pgdk2o_.arc' no longer needed
for this recovery

ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_19/o1_mf_1_303_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

S SYS> recover database until cancel;
ORA-00279: change 11618376 generated at 06/19/2009 16:09:04 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_19/o1_mf_1_303_%u_.arc
ORA-00280: change 11618376 for thread 1 is in sequence #303

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

 

 위 실패시에는 restore 후 다시 2번 실행

S SYS> alter database open resetlogs;

 Database altered.

 

resetlogs로 open 한 후에는 무조건 백업 할 것.

 

 

 

OS 인증

[root@orcl ~]# adduser dba -g dba

[root@orcl ~]# passwd dba

[root@orcl ~]# usermod -G oinstall dba

[dba@orcl ~]$ su - dba
Password:
[dba@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 22 14:01:18 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

S SYS>

  

 

원격 접속시 인증

 

C:\WINDOWS>sqlplus sys/oracle@192.168.0.121:1521/orcl.oracle.com as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 월 6월 22 14:43:35 2009

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

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

S SYS> exit

 

 

 

[oracle@orcl ~]$ cd $ORACLE_HOME/dbs
[oracle@orcl dbs]$ ls *pw*
orapworcl                                       =>   sysdba의 비번이 들어있음.
[oracle@orcl dbs]$ rm orapworcl

지우면 원격에서 접속이 안됨.

 C:\WINDOWS>sqlplus sys/oracle@192.168.0.121:1521/orcl.oracle.com as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 월 6월 22 14:46:23 2009

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

ERROR:
ORA-01031: insufficient privileges

 

 

다시 고치는 방법

 

[oracle@orcl dbs]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and     force - whether to overwrite existing file (opt),
OPERs (opt),
  There are no spaces around the equal-to (=) character.
[oracle@orcl dbs]$ orapwd file=orapworcl password=oracle entries=5
[oracle@orcl dbs]$

 C:\WINDOWS>sqlplus sys/oracle@192.168.0.121:1521/orcl.oracle.com as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 월 6월 22 14:51:30 2009

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

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

S SYS>

 

 

 

password File Locations

LINUX/UNIX : $ORACLE_HOME/dbs

WINDOWS : %ORACLE_HOME%/database