Sys-Admin Information’s

Share Knowledge, Distribute Knowledge, Capture & Modify Knowledge, Create Knowledge

Archive for the ‘Backup & Recovery’ Category

Troubleshooting for startup inconsistent DB

with 2 comments

for e.g. database unable up cause of datafile corruption

SQL> startup
ORACLE instance started.
 
Total System Global Area 1610612736 bytes
Fixed Size                  2177912 bytes
Variable Size             396149896 bytes
DATABASE Buffers         1207959552 bytes
Redo Buffers                4325376 bytes
DATABASE mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: DATA file 2: '/oradata/oracle/ts/undotbs01.

commonly the database unable be startup if UNDO or SYSTEM table space need recovery, if these happen we need correct temporary workaround to handle this, all workaround for each tablespace datafile is similar

do the recovery

SQL> recover DATABASE;
ORA-00279: CHANGE 7516226638 generated at 05/05/2008 12:32:11
needed FOR thread 1
ORA-00289: suggestion : /oradata/oracle/ts/arc/1_42_653916655.dbf
ORA-00280: CHANGE 7516226638 FOR thread 1 IS IN sequence #42
 
Specify log: {ret=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
‘/oradata/oracle/ts/arc/1_42_653916655.dbf’
ORA-27037: unable TO obtain file STATUS
SVR4 Error: 2: No such file OR directory
Additional information: 3

Read the rest of this entry »

Written by Hilman-Lupus

May 27th, 2008 at 10:14 am

Posted in Backup & Recovery, Oracle

Tagged with

Backup Listener log

with one comment

In database production, the log of listener was grow rapidly, some times if you move the “listener.log” while the listener is up and running, listener will stop writing to log file after you move/delete the “listener.log”, it won’t create new log file.
The listener log status will be ‘off’, You need to start the “listener log” again manually. For this reason you must do this procedure.

bash> lsnrctl stat
LSNRCTL for Solaris: Version 9.2.0.6.0 - Production on 09-MAR-2008 12:43:22
 
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 9.2.0.6.0 - Production
Start Date                11-SEP-2007 03:34:17
Uptime                    180 days 9 hr. 9 min. 4 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/app/product/9.2.0/network/admin/listener.ora
Listener Log File         /oracle/app/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOSTNAME)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "DB_SID.domain.com" has 2 instance(s).
Instance "DB_SID", status UNKNOWN, has 1 handler(s) for this service...
Instance "DB_SID", status READY, has 1 handler(s) for this service...
The command completed successfully
bash> lsnrctl
LSNRCTL> SET log_status off
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
LISTENER parameter "log_status" SET TO OFF
The command completed successfully
bash> mv /oracle/app/product/9.2.0/network/log/listener.log /backup/listener.log_9-March-2008
LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
LISTENER parameter "log_status" set to ON
The command completed successfully

Written by Hendrawan

March 9th, 2008 at 12:40 pm

Posted in Backup & Recovery, Oracle

Tagged with

Moving & Recreate Temp Tablespace

without comments

I Recreate the temp tablespace coz my temp tablespace was corrupt,
you can do to the following bellow

  1. create temporary tablespace temp2 tempfile ‘/rdbms/temp01/temp2.dbf’ size 10M extent management local uniform size 1M;
  2. alter database default temporary tablespace temp2;
  3. drop tablespace temp including contents and datafiles;

now change back to temp again with following step bellow:

  1. create temporary tablespace temp tempfile ‘/rdbms/temp01/temp2_01.dbf’ size 1024M extent management local uniform size 1M;
  2. alter database default temporary tablespace temp;
  3. drop tablespace temp2 including contents and datafiles;
  4. alter tablespace temp add tempfile ‘/rdbms/temp01/temp2_02.dbf’ size 1024M;

Written by Hendrawan

December 13th, 2007 at 12:05 am

Posted in Backup & Recovery, Oracle

Split Busy Datafile to Another Mountpoint

without comments

If you want to load balance your datafile, you can split your datafile into another mount point,
here’s detail step.

1. lsnrctl stop
2. shutdown immediate
3. copy your datafile

cp /$ora_home/$SID/undo/undo01.dbf /$ora_home/$SID/undo1/
cp /$ora_home/$SID/undo/undo02.dbf /$ora_home/$SID/undo2/
cp /$ora_home/$SID/index1/idx01.dbf /$ora_home/$SID/index2/
cp /$ora_home/$SID/index1/idx02.dbf /$ora_home/$SID/index3/


4. startup exclusive mount
5. rename your old datafile path with alter command

alter db rename file ‘/$ora_home/$SID/undo/undo01.dbf’ to ‘/$ora_home/$SID/undo1/undo1.dbf’;
alter db rename file ‘/$ora_home/$SID/undo/undo02.dbf’ to ‘/$ora_home/$SID/undo2/undo2.dbf’;
alter db rename file ‘/$ora_home/$SID/index1/idx01.dbf’ to ‘/oradata/$SID/index2/idx01.dbf’;
alter db rename file ‘/$ora_home/$SID/index1/idx02.dbf’ to ‘/oradata/$SID/index3/idx02.dbf’;


6. shutdown immediate
7. startup
8. lsnrctl start

Note:
DB=database

Written by Hendrawan

October 9th, 2007 at 12:35 pm

Posted in Backup & Recovery, Oracle