1 2 Previous Next 17 Replies Latest reply on Sep 16, 2013 10:58 PM by jgarry

    Oracle DB offline after server reboot / hardware failure

    1040156

      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

          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
            1040156

            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
              1040156

              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

                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?

                1 person found this helpful
                • 5. Re: Oracle DB offline after server reboot / hardware failure
                  1040156

                  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

                    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
                      1040156

                      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
                        1040156

                        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

                          >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

                            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
                              Selvakumar.Nagulan

                              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
                                1040156

                                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
                                  Selvakumar.Nagulan

                                  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
                                    1040156

                                    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