This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 16, 2013 3:31 PM by EdStevens RSS

Oracle DB offline after server reboot / hardware failure

48a85da4-f058-45e3-bc90-cacab5457884 Newbie
Currently Being Moderated

Hi,

 

We have a server that crashed due to hardware failure and although brought back up OK Oracle DB's are no longer mounting. I am not a DBA but it looks bad to me and it seems there are no backups. Can anyone suggest a course of action? This is Oracle 11G XE running on Centos 6. I have put as much detail in as I can and could really do with some advice.

 

[root@627963 trace]# sqlplus /nolog

 

 

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 12 23:18:26 2013

 

 

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

 

 

SQL> connect sys as sysdba;

Enter password:

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

 

Total System Global Area 1068937216 bytes

Fixed Size                  2233344 bytes

Variable Size             893389824 bytes

Database Buffers          167772160 bytes

Redo Buffers                5541888 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 10571

Session ID: 62 Serial number: 3

 

 

 

 

SQL> startup /nomount

SP2-0714: invalid combination of STARTUP options

SQL> startup nomount

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

SQL>

 

Alert_XE.log:

 

Thu Sep 12 21:26:18 2013

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =19

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora

System parameters with non-default values:

  sessions                 = 180

  memory_target            = 1G

  control_files            = "/u01/app/oracle/oradata/XE/control.dbf"

  compatible               = "11.2.0.0.0"

  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"

  db_recovery_file_dest_size= 20G

  undo_management          = "AUTO"

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=XEXDB)"

  shared_servers           = 4

  job_queue_processes      = 4

  audit_file_dest          = "/u01/app/oracle/admin/XE/adump"

  db_name                  = "XE"

  open_cursors             = 300

  diagnostic_dest          = "/u01/app/oracle"

Thu Sep 12 21:26:20 2013

PMON started with pid=2, OS id=21388

Thu Sep 12 21:26:20 2013

"alert_XE.log" 342L, 17231C

Thu Sep 12 21:26:20 2013

PSP0 started with pid=3, OS id=21390

Thu Sep 12 21:26:21 2013

VKTM started with pid=4, OS id=21392

VKTM running at (100ms) precision

Thu Sep 12 21:26:21 2013

GEN0 started with pid=5, OS id=21396

Thu Sep 12 21:26:21 2013

DIAG started with pid=6, OS id=21398

Thu Sep 12 21:26:21 2013

DBRM started with pid=7, OS id=21400

Thu Sep 12 21:26:21 2013

DIA0 started with pid=8, OS id=21402

Thu Sep 12 21:26:21 2013

MMAN started with pid=9, OS id=21404

Thu Sep 12 21:26:21 2013

DBW0 started with pid=10, OS id=21406

Thu Sep 12 21:26:21 2013

DBW1 started with pid=11, OS id=21408

Thu Sep 12 21:26:21 2013

DBW2 started with pid=12, OS id=21410

Thu Sep 12 21:26:21 2013

LGWR started with pid=13, OS id=21412

Thu Sep 12 21:26:21 2013

CKPT started with pid=14, OS id=21414

Thu Sep 12 21:26:21 2013

SMON started with pid=15, OS id=21416

Thu Sep 12 21:26:21 2013

RECO started with pid=16, OS id=21418

Thu Sep 12 21:26:21 2013

RECO started with pid=16, OS id=21418

Thu Sep 12 21:26:21 2013

MMON started with pid=17, OS id=21420

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Thu Sep 12 21:26:21 2013

MMNL started with pid=18, OS id=21422

starting up 4 shared server(s) ...

ORACLE_BASE not set in environment. It is recommended

that ORACLE_BASE be set in the environment

Reusing ORACLE_BASE from an earlier startup = /u01/app/oracle

Thu Sep 12 21:26:21 2013

ALTER DATABASE   MOUNT

Thu Sep 12 21:26:24 2013

Sweep [inc][21794]: completed

Sweep [inc][21793]: completed

Sweep [inc2][21794]: completed

Successful mount of redo thread 1, with mount id 2707110077

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Thu Sep 12 21:26:25 2013

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

read 93 KB redo, 34 data blocks need recovery

Started redo application at

Thread 1: logseq 241, block 21190

Recovery of Online Redo Log: Thread 1 Group 1 Seq 241 Reading mem 0

  Mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_8kxhwjco_.log

Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8, _wordcopy_bwd_dest_aligned()+280] [flags: 0x0, count: 1]

Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_21440.trc  (incident=22593):

ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []

Incident details in: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22593/XE_ora_21440_i22593.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70, kcbs_dump_adv_state()+946] [flags: 0x0, count: 2]

Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_21440.trc  (incident=22594):

ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+946] [SIGSEGV] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70] [Address not mapped to object] []

ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []

Incident details in: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22594/XE_ora_21440_i22594.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Dumping diagnostic data in directory=[cdmp_20130912212627], requested by (instance=1, osid=21440), summary=[incident=22593].

Errors in file /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22593/XE_ora_21440_i22593.trc:

ORA-00607: Internal error occurred while making a change to a data block

ORA-00602: internal programming exception

ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+946] [SIGSEGV] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70] [Address not mapped to object] []

ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []

PMON (ospid: 21388): terminating the instance due to error 397

System state dump requested by (instance=1, osid=21388 (PMON)), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_diag_21398.trc

Dumping diagnostic data in directory=[cdmp_20130912212628], requested by (instance=1, osid=21388 (PMON)), summary=[abnormal instance termination].

Instance terminated by PMON, pid = 21388

Thu Sep 12 21:39:03 2013

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =19

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora

System parameters with non-default values:

  sessions                 = 180

  memory_target            = 1G

  control_files            = "/u01/app/oracle/oradata/XE/control.dbf"

  compatible               = "11.2.0.0.0"

  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"

  db_recovery_file_dest_size= 20G

  undo_management          = "AUTO"

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=XEXDB)"

  shared_servers           = 4

  job_queue_processes      = 4

  audit_file_dest          = "/u01/app/oracle/admin/XE/adump"

  db_name                  = "XE"

  open_cursors             = 300

  diagnostic_dest          = "/u01/app/oracle"

Thu Sep 12 21:39:05 2013

PMON started with pid=2, OS id=2483

Thu Sep 12 21:39:05 2013

PSP0 started with pid=3, OS id=2489

Thu Sep 12 21:39:06 2013

VKTM started with pid=4, OS id=2546

VKTM running at (100ms) precision

Thu Sep 12 21:39:06 2013

GEN0 started with pid=5, OS id=2550

Thu Sep 12 21:39:06 2013

DIAG started with pid=6, OS id=2552

Thu Sep 12 21:39:06 2013

DBRM started with pid=7, OS id=2554

Thu Sep 12 21:39:06 2013

DIA0 started with pid=8, OS id=2556

 

And the tracefile:

 

Trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_21440.trc

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe

System name:    Linux

Node name:      <blah>

Release:        2.6.32-358.14.1.el6.x86_64

Version:        #1 SMP Tue Jul 16 23:51:20 UTC 2013

Machine:        x86_64

Instance name: XE

Redo thread mounted by this instance: 1

Oracle process number: 24

Unix process pid: 21440, image: <blah>(TNS V1-V3)

 

 

 

 

*** 2013-09-12 21:26:25.858

*** SESSION ID:(62.3) 2013-09-12 21:26:25.858

*** CLIENT ID:() 2013-09-12 21:26:25.858

*** SERVICE NAME:() 2013-09-12 21:26:25.858

*** MODULE NAME:(<Blah> (TNS V1-V3)) 2013-09-12 21:26:25.858

*** ACTION NAME:() 2013-09-12 21:26:25.858

 

 

Thread 1 checkpoint: logseq 241, block 2, scn 8960531

  cache-low rba: logseq 241, block 21190

    on-disk rba: logseq 241, block 21377, scn 8979221

  start recovery at logseq 241, block 21190, scn 0

 

 

*** 2013-09-12 21:26:25.869

Started writing zeroblks thread 1 seq 241 blocks 21377-21384

 

 

*** 2013-09-12 21:26:25.870

Completed writing zeroblks thread 1 seq 241

==== Redo read statistics for thread 1 ====

Total physical reads (from disk and memory): 4096Kb

-- Redo read_disk statistics --

Read rate (ASYNC): 93Kb in 0.01s => 9.13 Mb/sec

Longest record: 2Kb, moves: 0/242 (0%)

Change moves: 2/26 (7%), moved: 0Mb

Longest LWN: 8Kb, moves: 0/57 (0%), moved: 0Mb

Last redo scn: 0x0000.00890313 (8979219)

----------------------------------------------

----- Recovery Hash Table Statistics ---------

Hash table buckets = 262144

Longest hash chain = 1

Average hash chain = 34/34 = 1.0

Max compares per lookup = 1

Avg compares per lookup = 377/542 = 0.7

----------------------------------------------

 

 

*** 2013-09-12 21:26:25.872

KCRA: start recovery claims for 34 data blocks

 

 

*** 2013-09-12 21:26:25.873

KCRA: blocks processed = 34/34, claimed = 34, eliminated = 0

 

 

*** 2013-09-12 21:26:25.874

Recovery of Online Redo Log: Thread 1 Group 1 Seq 241 Reading mem 0

 

 

*** 2013-09-12 21:26:26.055

Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8, _wordcopy_bwd_dest_aligned()+280] [flags: 0x0, count: 1]

Incident 22593 created, dump file: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22593/XE_ora_21440_i22593.trc

ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []

 

 

Incident 22594 created, dump file: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22594/XE_ora_21440_i22594.trc

ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+946] [SIGSEGV] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70] [Address not mapped to object] []

ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF

  • 1. Re: Oracle DB offline after server reboot / hardware failure
    sb92075 Guru
    Currently Being Moderated

    ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

  • 2. Re: Oracle DB offline after server reboot / hardware failure
    48a85da4-f058-45e3-bc90-cacab5457884 Newbie
    Currently Being Moderated

    Hi,

     

    Thanks for the response. I'm not a DBA, I don't have a support contract, this is an app that was built by a third party who isn't on the scene any more. I was hoping someone would be able to simply come up with some ideas.

     

    Failing that, how do you get support from Oracle, do they do fixed price per incident support?

     

    Thanks

  • 3. Re: Oracle DB offline after server reboot / hardware failure
    48a85da4-f058-45e3-bc90-cacab5457884 Newbie
    Currently Being Moderated

    I have checked and can issue "startup mount" without getting any errors.

     

    I have archivelogs for every day dating back to Feb this year, is there any way of restoring / repairing back to a couple of days ago using those?

     

    Thanks

     

    Graham

  • 4. Re: Oracle DB offline after server reboot / hardware failure
    EdStevens Guru
    Currently Being Moderated

    48a85da4-f058-45e3-bc90-cacab5457884 wrote:

     

    I have checked and can issue "startup mount" without getting any errors.

     

    I have archivelogs for every day dating back to Feb this year, is there any way of restoring / repairing back to a couple of days ago using those?

     

    Thanks

     

    Graham

    To use the archivelogs you must begin by restoring from a database backup.  Do you have one?

  • 5. Re: Oracle DB offline after server reboot / hardware failure
    48a85da4-f058-45e3-bc90-cacab5457884 Newbie
    Currently Being Moderated

    A glimmer of hope! Well, the backups were not working but there is an old version of the apex app / db on a different server that could be resurrected...

     

    Thanks

     

    Graham

  • 6. Re: Oracle DB offline after server reboot / hardware failure
    EdStevens Guru
    Currently Being Moderated

    48a85da4-f058-45e3-bc90-cacab5457884 wrote:

     

    A glimmer of hope! Well, the backups were not working but there is an old version of the apex app / db on a different server that could be resurrected...

     

    Thanks

     

    Graham

     

    Hopefully this will compel you or whoever to spend some serious effort improving your backup and recovery procedures.  The current procedures are obviously insufficient.  Database recovery is the number-one must-have skill of a DBA.  If you can't recover a database after any conceivable scenario (and a few inconceivable ones) then nothing else you can do really matters.  And part of being able to recover is being able to implement a robust backup system.  Several months ago (maybe a year now?) we saw a guy's company (sounded like it was a startup) come within hours of going bust because he did not have the foresight to insure proper backups.  I wish now I had bookmarked the thread.

  • 7. Re: Oracle DB offline after server reboot / hardware failure
    48a85da4-f058-45e3-bc90-cacab5457884 Newbie
    Currently Being Moderated

    Hi Ed,

     

    Thanks, I do understand the importance of backups and suffice to say there are reasons that I won't go in to in a public forum as to why these weren't running, What's important now is I find away of getting this DB back up and running and then learn how to implement backups myself and / or find an Oracle DBA who will. I really appreciate your help and opinions and I may well be back for further advice. For now I'm looking at building a new server, importing data from the old DB then trying to restore the data from the archive logs.

     

    Thanks

     

    Graham

  • 8. Re: Oracle DB offline after server reboot / hardware failure
    48a85da4-f058-45e3-bc90-cacab5457884 Newbie
    Currently Being Moderated

    I do have another question, the database was installed as part of APEX, originally 2 apps on two on premise windows servers. In Feb 2013 the apps were transferred to the cloud server on one APEX install. The archive logs are present from this point forward and I still have both servers with the old DB's on.

     

    If I build a replica of that Feb 2013 cloud server copying the DBs from the old boxes will I be able to copy the archive log files across from the cloud server and restore them on my newly built server? If this will work, what exactly has to be identical in order to achieve this?

     

    Finally, is anyone able to point me in the right direction for restoring these archive logs? I assume I need to use RMAN but not sure how to begin getting the files in the right place and what syntax to use! There seems to be a lot of info on RMAN restoration from backups and archive / redo but not for the scenario I am faced with.

     

    Thanks

     

    Graham

  • 9. Re: Oracle DB offline after server reboot / hardware failure
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >In Feb 2013 the apps were transferred to the cloud server on one APEX install. The archive logs are present from this point forward and I still have both servers with the old DB's on.

    If the "transfer to the cloud" was a *physical copy* of the database  (no change in DBID !!),  in theory you might be able to use archivelogs.  But I suspect that there would be a redo gap between the source database and the transferred datadbase, resulting in the inability to use archivelogs generated subsequent to the gap.

    If the "transfer to the cloud" was a data migration or export-import, there is NO way, you can use the archivelogs because Oracle sees the two databases (source and transferred) as two *different* databases.

     

    Archivelogs can be restored using RMAN if they were backed up using RMAN to RMAN BackupSets.  Else, they might be still be on the OS or copied (backed up) using OS commands (like cp, cpio, tar, media management software etc). You'd use the same method (that was used for the OS  copy/backup) to do the "restore".

     

     

    Hemant K Chitale


  • 10. Re: Oracle DB offline after server reboot / hardware failure
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    I suspect that the ORA-7445 errors indicate that there is corruption.   Unfortunately, XE is not covered by Oracle Support and you are "on your own".

     

    Hemant K Chitale


  • 11. Re: Oracle DB offline after server reboot / hardware failure
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    Hi,

     

    You have hit a bug in Oracle which matches the document id 14301592.8, Bug 14301592

    Please do the following to try make it work as per Oracle suggestion.

     

    startup mount;
    recover database allow 1 corruption;

     

    once recover complete, open your database.

     

    alter database open;

     

    Now the affected index may have to be rebuilt.

     

    If there are more then 1 corruption, you may repeat this step or run command with allow many corruption. However, don't forget to note corrupted file and block numbers which is useful in finding the corrupted object.


    Thank you!!



  • 12. Re: Oracle DB offline after server reboot / hardware failure
    48a85da4-f058-45e3-bc90-cacab5457884 Newbie
    Currently Being Moderated

    Hi Nagulan,

     

    Thanks for the tips, I have run the commands you mention and have the following output:

     

    SQL> connect sys as sysdba

    Enter password:

    Connected to an idle instance.

    SQL> startup mount;

    ORACLE instance started.

     

     

    Total System Global Area 1068937216 bytes

    Fixed Size                  2233344 bytes

    Variable Size             893389824 bytes

    Database Buffers          167772160 bytes

    Redo Buffers                5541888 bytes

    Database mounted.

    SQL> recover database allow 1 corruption;

    Media recovery complete.

    SQL> alter database open;

    alter database open

    *

    ERROR at line 1:

    ORA-01092: ORACLE instance terminated. Disconnection forced

    ORA-01578: ORACLE data block corrupted (file # 3, block # 160)

    ORA-01110: data file 3: '/u01/app/oracle/oradata/XE/undotbs1.dbf'

    Process ID: 30495

    Session ID: 62 Serial number: 3

     

    What should I do at this point? Is this where I should continue running the initial recover command again?

     

    Thanks

     

    Graham

  • 13. Re: Oracle DB offline after server reboot / hardware failure
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    Your undo file has corrupted.

    Check the below post for your work around.

    https://forums.oracle.com/thread/1098080?start=0&tstart=0

     

    Thank you!!

  • 14. Re: Oracle DB offline after server reboot / hardware failure
    48a85da4-f058-45e3-bc90-cacab5457884 Newbie
    Currently Being Moderated

    Hi Guys,

     

    I have copied a backup script below that it turns out has been running every night at midnight. Where does this go, how do I know if it has worked and how can I use it to restore?

     

    #!/bin/sh

    #

    # The script assumes that user can connect using "/ as sysdba" and Flash

    # Recovery Area is enabled.

    #

    # =================

    # Backup procedure

    # =================

    #

    #    For database in NoArchiveLog mode, database is shutdown and an offline

    #    backup is done;

    #    For database in Archive log mode, online backup is done.

    #

    #    During the backup procedure, the script stores flash recovery area

    #    location by saving complete initialization parameter to

    #    ?/dbs/spfile2init.ora file. This will be used during restore operation

    #    to find Flash Recovery Area location. If this file is lost, then user must

    #    enter Flash Recovery Area location during restore operation.

    #

    #    Two backups are maintained in Flash Recovery Area and the corresponding

    #    log files for last two backup job are saved in

    #    $HOME/oxe_backup_current.log and $HOME/oxe_backup_previous.log

    #

    user=`/usr/bin/whoami`

    group=`/usr/bin/groups $user | grep dba`

    if test -z "$group"; then

       if [ -f /usr/bin/zenity ]

       then

            /usr/bin/zenity --error --text="$user must be in the DBA OS group to backup the database."

       elif [ -f /usr/bin/kdialog ]

       then

            /usr/bin/kdialog --error "$user must be in the DBA OS group to backup the database."

       elif [ -f /usr/bin/xterm ]

       then

           echo "Operation failed. $user must be in the DBA OS group to backup the database."

           echo -n "Press any key to exit"

           read userinp

       fi

       exit 0

    fi

     

     

    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

    export ORACLE_SID=XE

     

     

    export PATH=$ORACLE_HOME/bin:$PATH

     

     

    TMPDIR=/tmp

    rman_normlog=${TMPDIR}/rman_normlog$$.log

     

     

    #Fix a logfile for current, previous run and spfile2init.ora

    sqlplus /nolog > $rman_normlog << EOF

       connect / as sysdba;

       set echo off;

       set head off;

       set serveroutput on;

       set linesize 515;

       declare

          l1 varchar2(512);

       begin

          l1 := dbms_backup_restore.normalizeFilename('spfile2init.ora');

          dbms_output.put_line('-----------------');

          dbms_output.put_line(l1);

          dbms_output.put_line('-----------------');

       end;

       /

    EOF

    rman_spfile2init=`grep "spfile2init.ora$" $rman_normlog`

    rm -f $rman_normlog

     

     

    rman_backup_current=$HOME/oxe_backup_current.log

    rman_backup_prev=$HOME/oxe_backup_previous.log

     

     

    #Choose a temporary log for this run

    rman_backup=${TMPDIR}/rman_backup$$.log

    echo XE Backup Log > $rman_backup

     

     

    #Check if flash recovery area is enabled

    rman_fra=${TMPDIR}/rman_fra$$.log

    sqlplus /nolog > $rman_fra << EOF

       connect / as sysdba;

       set head off;

       set echo off;

       set trimspool on;

       set linesize 512;

       select '$' || count(*) || '$' from v\$parameter

        where upper(name)='DB_RECOVERY_FILE_DEST'

          and value is not null;

    EOF

    fra=`grep "^$.*$" $rman_fra`

    rm -f $rman_fra

     

     

    if [ X$fra = X\$1\$ ]; then

       failed=false;

    else

       failed=true

       errstr="flash recovery area is not enabled"

    fi;

     

     

    if [ $failed = 'false' ] ; then

       #Check the mode of database

       rman_log_mode=${TMPDIR}/rman_log_mode$$.log

       sqlplus /nolog > $rman_log_mode << EOF

          connect / as sysdba;

          set head off;

          set echo off;

          set trimspool on;

          set linesize 512;

          select '$' || log_mode || '$' from v\$database;

    EOF

       mode=`grep "^$.*$" $rman_log_mode`

       rm -f $rman_log_mode

     

     

       case $mode in

          \$ARCHIVELOG\$)

          echo "Doing online backup of the database."

          rman target / >> $rman_backup << EOF

             set echo on;

             configure retention policy to redundancy 2;

             configure controlfile autobackup format for device type disk clear;

             configure controlfile autobackup on;

             sql "create pfile=''$rman_spfile2init'' from spfile";

             backup as backupset device type disk database;

             configure controlfile autobackup off;

             delete noprompt obsolete;

    EOF

          if [ $? = 0 ]; then

             failed=false;

          else

             failed=true

             errstr="RMAN error: See log for details"

          fi;

          rman target / >> $rman_backup << EOF

             sql 'alter system archive log current';

    EOF

          ;;

     

     

          \$NOARCHIVELOG\$)

          echo "Warning: Log archiving (ARCHIVELOG mode) is currently disabled. If"

          echo "you restore the database from this backup, any transactions that take"

          echo "place between this backup and the next backup will be lost. It is"

          echo "recommended that you enable ARCHIVELOG mode before proceeding so "

          echo "that all transactions can be recovered upon restore. See the section"

          echo "'Enabling ARCHIVELOG Mode...' in the online help for instructions."

     

     

          echo "Backup with log archiving disabled will shut down and restart the"

          echo -n "database. Are you sure [Y/N]?"

          gotit=false

          while ! $gotit; do

            read userinp

            if [ "$userinp" = "Y" -o "$userinp" = "y" -o \

                 "$userinp" = "n" -o "$userinp" = "N" ]; then

              gotit=true

            fi

          done

         

          if [ "$userinp" = "n" -o "$userinp" = "N" ]; then

             rm -f $rman_backup

             exit -1;

          fi

     

     

          echo "Backup in progress..."

     

     

          rman target / >> $rman_backup << EOF

             set echo on;

             shutdown immediate;

             startup mount;

             configure retention policy to redundancy 2;

             configure controlfile autobackup format for device type disk clear;

             configure controlfile autobackup on;

             sql "create pfile=''$rman_spfile2init'' from spfile";

             backup as backupset device type disk database;

             configure controlfile autobackup off;

             alter database open;

             delete noprompt obsolete;

    EOF

          if [ $? = 0 ]; then

             failed=false;

          else

             failed=true

             errstr="RMAN error: See log for details"

          fi;

          ;;

      

          *)

          errstr="Unknown database mode $mode"

          failed=true;

          ;;

       esac;

    fi;

     

     

    #Save the error string in the log

    if [ $failed = 'true' ]; then

       echo ${errstr}. >> $rman_backup

    fi;

     

     

    #Save the last run as previous

    if [ -f $rman_backup_current ]; then

       mv -f $rman_backup_current $rman_backup_prev

    fi;

     

     

    #Save the current run

    mv -f $rman_backup $rman_backup_current

     

     

    #Display the result to user

    if [ $failed = 'true' ] ; then

       echo '==================== ERROR ========================='

       echo '             Backup of the database failed          '

       echo '==================== ERROR ========================='

       echo ${errstr}.

       echo Log file is at $rman_backup_current.

    else

       echo Backup of the database succeeded.

       echo Log file is at $rman_backup_current.

    fi

     

     

    #Wait for user to press any key

    echo -n "Press ENTER key to exit"

    read userinp

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points