2 Replies Latest reply: Jun 24, 2013 4:08 PM by martin.morono RSS

    ORA-38760: This database instance failed to turn on flashback database

    martin.morono

      Hello All,

           We have a 2-node RAC primary database with two physical standby databases (another 2-node RAC and a single instance) running on 11.2.0.2.7 over linux RHEL.


      We had some troubles with the 2-node RAC working as standby. A few days ago we found that the FLASHBACK directory within our ASM structure was consuming almost the 95% of our +FLASH diskgroup. For some reason it was not cleaning up the old flashback files older than the 1440 minutes (flashbackup retention) was set to 720. We disabled and re-enabled the flashback and also reduced the retention with no positive results.

       

      Someone fell in the temptation of dropping all the flashback files and recreate the control file as described in Metalink Note (ID 342160.1), and he failed to recreate the controlfile as described. We still cannot open our standby.

       

      SQL> alter database open;

      alter database open

      *

      ERROR at line 1:

      ORA-38760: This database instance failed to turn on flashback database

       

      SQL> select * from v$restore_point;

      select * from v$restore_point

                    *

      ERROR at line 1:

      ORA-38701: Flashback database log 1 seq 1 thread 1:

      "+FLASH/alephsb1/flashback/log_1.4992.784896269"

      ORA-17503: ksfdopn:2 Failed to open file

      +FLASH/alephsb1/flashback/log_1.4992.784896269

      ORA-15012: ASM file '+FLASH/alephsb1/flashback/log_1.4992.784896269' does not

      exist

       

      The current flashback retention:

      SQL> show parameter flashback_retention

       

       

      NAME                                 TYPE                             VALUE

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

      db_flashback_retention_target        integer                          720

       

      The current free space at each diskgroup and the contents of the +FLASH/ALEPHSB1/FLASHBACK directory:

       

      +ASM1@argalephdbcont1:/u01/app/oracle/diag/rdbms/alephsb1/ALEPHSB1/trace>asmcmd lsdg

      State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

      MOUNTED  EXTERN  N         512   4096  1048576    381479    25086                0           25086              0             N  DATA1/

      MOUNTED  EXTERN  N         512   4096  1048576    381479   369363                0          369363              0             N  FLASH/

      MOUNTED  NORMAL  N         512   4096  1048576      4900     3970              980            1495              0             Y  QUORUM/

      +ASM1@argalephdbcont1:/u01/app/oracle/diag/rdbms/alephsb1/ALEPHSB1/trace>asmcmd ls -lt +FLASH/ALEPHSB1/FLASHBACK

      Type       Redund  Striped  Time             Sys  Name

      FLASHBACK  UNPROT  COARSE   JUN 24 15:00:00  Y    log_10540.10434.818553629

       

      Any suggestion?

       

      Regards.

       

      Martin

        • 1. Re: ORA-38760: This database instance failed to turn on flashback database
          martin.morono

          After setting the cluster_database parameter in false and restarting the database for following the steps detailed in the Metalink noted referenced above, the recreation of the controlfile failed with the following errors:

           

          SQL> startup mount;

          ORACLE instance started.

           

           

          Total System Global Area 2.4586E+10 bytes

          Fixed Size                  2237824 bytes

          Variable Size            1.0737E+10 bytes

          Database Buffers         1.3824E+10 bytes

          Redo Buffers               21716992 bytes

          Database mounted.

          SQL> alter database flashback off;

           

           

          Database altered.

           

           

          SQL> alter database backup controlfile to trace noresetlogs;

           

           

          Database altered.

           

           

          SQL>  shutdown immediate;

          ORA-01109: database not open

           

           

           

           

          Database dismounted.

          ORACLE instance shut down.

          SQL> startup nomount exclusive

          ORACLE instance started.

           

           

          Total System Global Area 2.4586E+10 bytes

          Fixed Size                  2237824 bytes

          Variable Size            1.0737E+10 bytes

          Database Buffers         1.3824E+10 bytes

          Redo Buffers               21716992 bytes

          SQL> @/u01/app/oracle/diag/rdbms/alephsb1/ALEPHSB1/trace/cr_cf_2.sql

          CREATE CONTROLFILE REUSE DATABASE "ALEPHPR" NORESETLOGS FORCE LOGGING ARCHIVELOG

          *

          ERROR at line 1:

          ORA-01503: CREATE CONTROLFILE failed

          ORA-01189: file is from a different RESETLOGS than previous files

          ORA-01517: log member: '+DATA1/alephsb1/redo13a.log'

           

          Thanks in advance.

          • 2. Re: ORA-38760: This database instance failed to turn on flashback database
            martin.morono

            Here the dg_prim_diag.sql script output:

             

             

             

            TIME

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

            JUN-24-2013 17:43:47

             

             

            1 row selected.

             

             

            SQL>

            SQL> -- In the following the database_role should be primary as that is what

            SQL> -- this script is intended to be run on.  If protection_level is different

            SQL> -- than protection_mode then for some reason the mode listed in

            SQL> -- protection_mode experienced a need to downgrade.  Once the error

            SQL> -- condition has been corrected the protection_level should match the

            SQL> -- protection_mode after the next log switch.

            SQL>

            SQL> column role format a7 tru

            SQL> column name format a10 wrap

            SQL>

            SQL> select name,database_role role,log_mode,

              2      protection_mode,protection_level

              3  from v$database;

             

             

            NAME       ROLE    LOG_MODE     PROTECTION_MODE      PROTECTION_LEVEL

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

            ALEPHPR    PRIMARY ARCHIVELOG   MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

             

             

            1 row selected.

             

             

            SQL>

            SQL> -- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the

            SQL> -- archiver failed to archive a log last time, but will try again within 5

            SQL> -- minutes. LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log

            SQL> -- switching is waiting for.  Note that if ALTER SYSTEM SWITCH LOGFILE is

            SQL> -- hung, but there is room in the current online redo log, then value is

            SQL> -- NULL

            SQL>

            SQL> column host_name format a20 tru

            SQL> column version format a9 tru

            SQL>

            SQL> select instance_name,host_name,version,archiver,log_switch_wait

              2  from v$instance;

             

             

            INSTANCE_NAME    HOST_NAME            VERSION   ARCHIVE LOG_SWITCH_WAIT

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

            ALEPHPR1         argalephdbprod1.turn 11.2.0.2. STARTED

             

             

            1 row selected.

             

             

            SQL>

            SQL> -- The following query give us information about catpatch.

            SQL> -- This way we can tell if the procedure doesn't match the image.

            SQL>

            SQL> select version, modified, status from dba_registry

              2  where comp_id = 'CATPROC';

             

             

            VERSION   MODIFIED             STATUS

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

            11.2.0.2. 19-JUN-2013 14:21:04 VALID

             

             

            1 row selected.

             

             

            SQL>

            SQL> -- Force logging is not mandatory but is recommended.  Supplemental

            SQL> -- logging must be enabled if the standby associated with this primary is

            SQL> -- a logical standby. During normal operations it is acceptable for

            SQL> -- SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.

            SQL>

            SQL> column force_logging format a13 tru

            SQL> column remote_archive format a14 tru

            SQL> column dataguard_broker format a16 tru

            SQL>

            SQL> select force_logging,remote_archive,

              2      supplemental_log_data_pk,supplemental_log_data_ui,

              3      switchover_status,dataguard_broker

              4  from v$database;

             

             

            FORCE_LOGGING REMOTE_ARCHIVE SUP SUP SWITCHOVER_STATUS    DATAGUARD_BROKER

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

            YES           ENABLED        NO  NO  TO STANDBY           ENABLED

             

             

            1 row selected.

             

             

            SQL>

            SQL> -- This query produces a list of all archive destinations.  It shows if

            SQL> -- they are enabled, what process is servicing that destination, if the

            SQL> -- destination is local or remote, and if remote what the current mount ID

            SQL> -- is.

            SQL>

            SQL> column destination format a35 wrap

            SQL> column process format a7

            SQL> column archiver format a8

            SQL> column ID format 99

            SQL> column mid format 99

            SQL>

            SQL> select dest_id "ID",destination,status,target,

              2      schedule,process,mountid  mid

              3  from v$archive_dest order by dest_id;

             

             

            ID DESTINATION                         STATUS    TARGET  SCHEDULE PROCESS MID

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

              1 USE_DB_RECOVERY_FILE_DEST           VALID     PRIMARY ACTIVE   ARCH      0

              2 alephsb                             DEFERRED  STANDBY ACTIVE   LGWR      0

              3 ALEPHSB1                            DISABLED  STANDBY ACTIVE   LGWR      0

              4 ATLALEPH                            VALID     STANDBY ACTIVE   LGWR    ###

              5                                     INACTIVE  PRIMARY INACTIVE ARCH      0

              6                                     INACTIVE  PRIMARY INACTIVE ARCH      0

              7                                     INACTIVE  PRIMARY INACTIVE ARCH      0

              8                                     INACTIVE  PRIMARY INACTIVE ARCH      0

              9                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            10                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            11                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            12                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            13                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            14                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            15                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            16                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            17                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            18                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            19                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            20                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            21                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            22                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            23                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            24                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            25                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            26                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            27                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            28                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            29                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            30                                     INACTIVE  PRIMARY INACTIVE ARCH      0

            31                                     INACTIVE  PRIMARY INACTIVE ARCH      0

             

             

            31 rows selected.

             

             

            SQL>

            SQL> -- This select will give further detail on the destinations as to what

            SQL> -- options have been set. Register indicates whether or not the archived

            SQL> -- redo log is registered in the remote destination control file.

            SQL>

            SQL> set numwidth 8

            SQL> column ID format 99

            SQL>

            SQL> select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,

              2      net_timeout net_time,delay_mins delay,reopen_secs reopen,

              3      register,binding

              4  from v$archive_dest order by dest_id;

             

             

            ID ARCHIVER TRANSMIT_MOD AFF    ASYNC NET_TIME    DELAY   REOPEN REG BINDING

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

              1 ARCH     SYNCHRONOUS  NO         0        0        0      300 YES OPTIONAL

              2 LGWR     ASYNCHRONOUS NO     61440       30       30      300 YES OPTIONAL

              3 LGWR     ASYNCHRONOUS NO     61440       30        0      300 YES OPTIONAL

              4 LGWR     ASYNCHRONOUS NO     61440       30        0      300 YES OPTIONAL

              5 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

              6 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

              7 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

              8 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

              9 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            10 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            11 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            12 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            13 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            14 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            15 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            16 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            17 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            18 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            19 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            20 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            21 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            22 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            23 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            24 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            25 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            26 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            27 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            28 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            29 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            30 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

            31 ARCH     SYNCHRONOUS  NO         0        0        0        0 NO  OPTIONAL

             

             

            31 rows selected.

             

             

            SQL>

            SQL> -- The following select will show any errors that occured the last time

            SQL> -- an attempt to archive to the destination was attempted.  If ERROR is

            SQL> -- blank and status is VALID then the archive completed correctly.

            SQL>

            SQL> column error format a55 wrap

            SQL>

            SQL> select dest_id,status,error from v$archive_dest;

             

             

            DEST_ID STATUS    ERROR

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

                   1 VALID

                   2 DEFERRED

                   3 DISABLED  ORA-01034: ORACLE not available

                   4 VALID

                   5 INACTIVE

                   6 INACTIVE

                   7 INACTIVE

                   8 INACTIVE

                   9 INACTIVE

                  10 INACTIVE

                  11 INACTIVE

                  12 INACTIVE

                  13 INACTIVE

                  14 INACTIVE

                  15 INACTIVE

                  16 INACTIVE

                  17 INACTIVE

                  18 INACTIVE

                  19 INACTIVE

                  20 INACTIVE

                  21 INACTIVE

                  22 INACTIVE

                  23 INACTIVE

                  24 INACTIVE

                  25 INACTIVE

                  26 INACTIVE

                  27 INACTIVE

                  28 INACTIVE

                  29 INACTIVE

                  30 INACTIVE

                  31 INACTIVE

             

             

            31 rows selected.

             

             

            SQL>

            SQL> -- The query below will determine if any error conditions have been

            SQL> -- reached by querying the v$dataguard_status view (view only available in

            SQL> -- 9.2.0 and above):

            SQL>

            SQL> column message format a80

            SQL>

            SQL> select message, timestamp

              2  from v$dataguard_status

              3  where severity in ('Error','Fatal')

              4  order by timestamp;

             

             

            MESSAGE

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

            TIMESTAMP

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

            Error 1034 received logging on to the standby

            JUN-24-2013 16:25:02

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 1034.

            JUN-24-2013 16:25:02

             

             

            WARN: ARC1: Terminating pid 7495 hung on an I/O operation

            JUN-24-2013 16:30:02

             

             

            WARN: ARC1: Terminating pid 7497 hung on an I/O operation

            JUN-24-2013 16:30:02

             

             

            WARN: ARC1: Terminating pid 7499 hung on an I/O operation

            JUN-24-2013 16:30:02

             

             

            ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

            JUN-24-2013 16:30:08

             

             

            Error 1034 received logging on to the standby

            JUN-24-2013 16:30:10

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 1034.

            JUN-24-2013 16:30:10

             

             

            WARN: ARC1: Terminating pid 16661 hung on an I/O operation

            JUN-24-2013 16:35:09

             

             

            WARN: ARC1: Terminating pid 16665 hung on an I/O operation

            JUN-24-2013 16:35:09

             

             

             

             

            MESSAGE

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

            TIMESTAMP

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

            WARN: ARC1: Terminating pid 16663 hung on an I/O operation

            JUN-24-2013 16:35:09

             

             

            PING[ARC2]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 16:35:17

             

             

            Error 270 for archive log file 12 to 'ALEPHSB1'

            JUN-24-2013 16:36:09

             

             

            WARN: ARC1: Terminating pid 24385 hung on an I/O operation

            JUN-24-2013 16:40:16

             

             

            WARN: ARC1: Terminating pid 24390 hung on an I/O operation

            JUN-24-2013 16:40:16

             

             

            WARN: ARC1: Terminating pid 24400 hung on an I/O operation

            JUN-24-2013 16:40:17

             

             

            ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

            JUN-24-2013 16:40:23

             

             

            Error 270 for archive log file 13 to 'ALEPHSB1'

            JUN-24-2013 16:40:31

             

             

            WARN: ARC1: Terminating pid 32025 hung on an I/O operation

            JUN-24-2013 16:45:24

             

             

            WARN: ARC1: Terminating pid 32030 hung on an I/O operation

            JUN-24-2013 16:45:24

             

             

             

             

            MESSAGE

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

            TIMESTAMP

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

            WARN: ARC1: Terminating pid 32027 hung on an I/O operation

            JUN-24-2013 16:45:24

             

             

            Error 1034 received logging on to the standby

            JUN-24-2013 16:45:32

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 1034.

            JUN-24-2013 16:45:32

             

             

            WARN: ARC1: Terminating pid 8655 hung on an I/O operation

            JUN-24-2013 16:50:31

             

             

            WARN: ARC1: Terminating pid 8657 hung on an I/O operation

            JUN-24-2013 16:50:31

             

             

            WARN: ARC1: Terminating pid 8659 hung on an I/O operation

            JUN-24-2013 16:50:31

             

             

            ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

            JUN-24-2013 16:50:37

             

             

            PING[ARC2]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 16:50:40

             

             

            WARN: ARC1: Terminating pid 16822 hung on an I/O operation

            JUN-24-2013 16:55:38

             

             

            WARN: ARC1: Terminating pid 16824 hung on an I/O operation

            JUN-24-2013 16:55:39

             

             

             

             

            MESSAGE

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

            TIMESTAMP

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

            WARN: ARC1: Terminating pid 16826 hung on an I/O operation

            JUN-24-2013 16:55:39

             

             

            PING[ARC2]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 16:55:47

             

             

            Error 270 for archive log file 11 to 'ALEPHSB1'

            JUN-24-2013 16:55:57

             

             

            WARN: ARC1: Terminating pid 24820 hung on an I/O operation

            JUN-24-2013 17:00:46

             

             

            WARN: ARC1: Terminating pid 24824 hung on an I/O operation

            JUN-24-2013 17:00:46

             

             

            WARN: ARC1: Terminating pid 24822 hung on an I/O operation

            JUN-24-2013 17:00:46

             

             

            ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

            JUN-24-2013 17:00:52

             

             

            Error 1034 received logging on to the standby

            JUN-24-2013 17:00:54

             

             

            PING[ARC2]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 1034.

            JUN-24-2013 17:00:54

             

             

            Error 12170 for archive log file 11 to 'alephsb'

            JUN-24-2013 17:04:56

             

             

             

             

            MESSAGE

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

            TIMESTAMP

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

            Error 12170 received logging on to the standby

            JUN-24-2013 17:04:56

             

             

            WARN: ARC1: Terminating pid 1192 hung on an I/O operation

            JUN-24-2013 17:05:53

             

             

            WARN: ARC1: Terminating pid 1194 hung on an I/O operation

            JUN-24-2013 17:05:53

             

             

            WARN: ARC1: Terminating pid 1196 hung on an I/O operation

            JUN-24-2013 17:05:53

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 17:06:02

             

             

            WARN: ARC1: Terminating pid 9159 hung on an I/O operation

            JUN-24-2013 17:11:00

             

             

            WARN: ARC1: Terminating pid 9161 hung on an I/O operation

            JUN-24-2013 17:11:01

             

             

            WARN: ARC1: Terminating pid 9163 hung on an I/O operation

            JUN-24-2013 17:11:01

             

             

            ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

            JUN-24-2013 17:11:07

             

             

            PING[ARC2]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 17:11:09

             

             

             

             

            MESSAGE

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

            TIMESTAMP

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

            WARN: ARC1: Terminating pid 17474 hung on an I/O operation

            JUN-24-2013 17:16:08

             

             

            WARN: ARC1: Terminating pid 17476 hung on an I/O operation

            JUN-24-2013 17:16:08

             

             

            WARN: ARC1: Terminating pid 17478 hung on an I/O operation

            JUN-24-2013 17:16:08

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 17:16:16

             

             

            WARN: ARC1: Terminating pid 29329 hung on an I/O operation

            JUN-24-2013 17:21:15

             

             

            WARN: ARC1: Terminating pid 29327 hung on an I/O operation

            JUN-24-2013 17:21:15

             

             

            WARN: ARC1: Terminating pid 29331 hung on an I/O operation

            JUN-24-2013 17:21:15

             

             

            ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

            JUN-24-2013 17:21:21

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 17:21:23

             

             

            WARN: ARC1: Terminating pid 4959 hung on an I/O operation

            JUN-24-2013 17:26:22

             

             

             

             

            MESSAGE

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

            TIMESTAMP

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

            WARN: ARC1: Terminating pid 4961 hung on an I/O operation

            JUN-24-2013 17:26:23

             

             

            WARN: ARC1: Terminating pid 4963 hung on an I/O operation

            JUN-24-2013 17:26:23

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 17:26:31

             

             

            WARN: ARC1: Terminating pid 12882 hung on an I/O operation

            JUN-24-2013 17:31:30

             

             

            WARN: ARC1: Terminating pid 12880 hung on an I/O operation

            JUN-24-2013 17:31:30

             

             

            WARN: ARC1: Terminating pid 12884 hung on an I/O operation

            JUN-24-2013 17:31:30

             

             

            ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

            JUN-24-2013 17:31:36

             

             

            PING[ARC0]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 17:31:38

             

             

            WARN: ARC1: Terminating pid 21918 hung on an I/O operation

            JUN-24-2013 17:36:37

             

             

            WARN: ARC1: Terminating pid 21916 hung on an I/O operation

            JUN-24-2013 17:36:37

             

             

             

             

            MESSAGE

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

            TIMESTAMP

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

            WARN: ARC1: Terminating pid 21920 hung on an I/O operation

            JUN-24-2013 17:36:37

             

             

            PING[ARC2]: Heartbeat failed to connect to standby 'ALEPHSB1'. Error is 16058.

            JUN-24-2013 17:36:45

             

             

            WARN: ARC1: Terminating pid 29974 hung on an I/O operation

            JUN-24-2013 17:41:44

             

             

            WARN: ARC1: Terminating pid 29985 hung on an I/O operation

            JUN-24-2013 17:41:45

             

             

            WARN: ARC1: Terminating pid 29990 hung on an I/O operation

            JUN-24-2013 17:41:45

             

             

             

             

            75 rows selected.

             

             

            SQL>

            SQL> -- The following query will determine the current sequence number

            SQL> -- and the last sequence archived.  If you are remotely archiving

            SQL> -- using the LGWR process then the archived sequence should be one

            SQL> -- higher than the current sequence.  If remotely archiving using the

            SQL> -- ARCH process then the archived sequence should be equal to the

            SQL> -- current sequence.  The applied sequence information is updated at

            SQL> -- log switch time.

            SQL>

            SQL> select ads.dest_id,max(sequence#) "Current Sequence",

              2      max(log_sequence) "Last Archived"

              3  from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads

              4  where ad.dest_id=al.dest_id

              5  and al.dest_id=ads.dest_id

              6  group by ads.dest_id;

             

             

            DEST_ID Current Sequence Last Archived

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

                   1            40871         40619

                   4            40871         40620

                   3            40206         40620

             

             

            3 rows selected.

             

             

            SQL>

            SQL> -- The following select will attempt to gather as much information as

            SQL> -- possible from the standby.  SRLs are not supported with Logical Standby

            SQL> -- until Version 10.1.

            SQL>

            SQL> set numwidth 8

            SQL> column ID format 99

            SQL> column "SRLs" format 99

            SQL> column Active format 99

            SQL>

            SQL> select dest_id id,database_mode db_mode,recovery_mode,

              2      protection_mode,standby_logfile_count "SRLs",

              3      standby_logfile_active ACTIVE,

              4      archived_seq#

              5  from v$archive_dest_status;

             

             

            ID DB_MODE         RECOVERY_MODE           PROTECTION_MODE      SRLs ACTIVE

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

            ARCHIVED_SEQ#

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

              1 OPEN            IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

              2 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                        0

             

             

              3 MOUNTED-STANDBY IDLE                    MAXIMUM PERFORMANCE     0      0

                        0

             

             

              4 OPEN_READ-ONLY  MANAGED                 MAXIMUM PERFORMANCE     8      1

                    40871

             

             

              5 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

              6 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

              7 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

              8 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

              9 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            10 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

             

             

            ID DB_MODE         RECOVERY_MODE           PROTECTION_MODE      SRLs ACTIVE

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

            ARCHIVED_SEQ#

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

            11 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            12 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            13 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            14 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            15 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            16 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            17 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            18 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            19 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            20 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

             

             

            ID DB_MODE         RECOVERY_MODE           PROTECTION_MODE      SRLs ACTIVE

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

            ARCHIVED_SEQ#

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

            21 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            22 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            23 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            24 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            25 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            26 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            27 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            28 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            29 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

            30 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

             

             

            ID DB_MODE         RECOVERY_MODE           PROTECTION_MODE      SRLs ACTIVE

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

            ARCHIVED_SEQ#

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

            31 UNKNOWN         IDLE                    MAXIMUM PERFORMANCE     0      0

                    40619

             

             

             

             

            31 rows selected.

             

             

            SQL>

            SQL> -- Query v$managed_standby to see the status of processes involved in

            SQL> -- the shipping redo on this system.  Does not include processes needed to

            SQL> -- apply redo.

            SQL>

            SQL> select process,status,client_process,sequence#

              2  from v$managed_standby;

             

             

            PROCESS STATUS       CLIENT_P SEQUENCE#

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

            ARCH    CONNECTED    ARCH             0

            ARCH    CLOSING      ARCH         40619

            ARCH    CONNECTED    ARCH             0

            ARCH    CONNECTED    ARCH             0

            LNS     WRITING      LNS          40620

             

             

            5 rows selected.

             

             

            SQL>

            SQL> -- The following query is run on the primary to see if SRL's have been

            SQL> -- created in preparation for switchover.

            SQL>

            SQL> select group#,sequence#,bytes from v$standby_log;

             

             

              GROUP# SEQUENCE#    BYTES

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

                  14         0 1.07E+09

                  15         0 1.07E+09

                  16         0 1.07E+09

                  17         0 1.07E+09

                  24         0 1.07E+09

                  25         0 1.07E+09

                  26         0 1.07E+09

                  27         0 1.07E+09

             

             

            8 rows selected.

             

             

            SQL>

            SQL> -- The above SRL's should match in number and in size with the ORL's

            SQL> -- returned below:

            SQL>

            SQL> select group#,thread#,sequence#,bytes,archived,status from v$log;

             

             

              GROUP#  THREAD# SEQUENCE#    BYTES ARC STATUS

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

                  11        1     40620 3.67E+08 NO  CURRENT

                  12        1     40618 3.67E+08 YES INACTIVE

                  13        1     40619 3.67E+08 YES INACTIVE

                  21        2     40871 3.67E+08 YES INACTIVE

                  22        2     40872 3.67E+08 NO  CURRENT

                  23        2     40870 3.67E+08 YES INACTIVE

             

             

            6 rows selected.

             

             

            SQL>

            SQL> -- Non-default init parameters.

            SQL>

            SQL> set numwidth 5

            SQL> column name format a30 tru

            SQL> column value format a48 wra

            SQL> select name, value

              2  from v$parameter

              3  where isdefault = 'FALSE';

             

             

            NAME                           VALUE

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

            processes                      1000

            sessions                       1528

            event

            resource_manager_plan          FORCE:

            sga_target                     16106127360

            memory_target                  24696061952

            memory_max_target              24696061952

            control_files                  +DATA1/alephpr/control01.ctl, +FLASH/alephpr/con

                                           trol02.ctl

             

             

            db_file_name_convert           +DATA1/ALEPHSB, +DATA1/ALEPHPR, +DATA1/alephsb,

                                           +DATA1/alephpr

             

             

            log_file_name_convert          ALEPHSB, ALEPHPR, alephsb, alephpr

            db_block_size                  8192

            compatible                     11.2.0.0.0

            log_archive_dest_1             LOCATION=USE_DB_RECOVERY_FILE_DEST

            log_archive_dest_2             service="alephsb", delay=30 optional compression

                                           =disable max_failure=0 max_connections=1 reopen=

                                           300 db_unique_name="alephsb" net_timeout=30

             

             

            log_archive_dest_3             SERVICE=ALEPHSB1 OPTIONAL REOPEN=300 VALID_FOR=(

                                           ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ALE

                                           PHSB1

             

             

            log_archive_dest_4             SERVICE=ATLALEPH OPTIONAL REOPEN=300 VALID_FOR=(

                                           ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ATL

                                           ALEPH

             

             

            log_archive_dest_state_2       DEFER

            log_archive_dest_state_3       DEFER

            log_archive_min_succeed_dest   1

             

             

            NAME                           VALUE

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

            fal_client                     ALEPHSB1

            fal_server                     ALEPHPR

            log_archive_trace              0

            log_archive_config             dg_config=(ALEPHPR,ALEPHSB1,ATLALEPH,alephsb)

            log_archive_format             %t_%s_%r.dbf

            log_archive_max_processes      4

            archive_lag_target             0

            cluster_database               TRUE

            db_create_file_dest            +DATA1

            db_recovery_file_dest          +FLASH

            db_recovery_file_dest_size     314572800000

            standby_file_management        AUTO

            thread                         1

            undo_tablespace                UNDOTBS1

            instance_number                1

            remote_login_passwordfile      EXCLUSIVE

            db_domain                      turner.com

            dispatchers                    (PROTOCOL=TCP) (SERVICE=ALEPHPRXDB)

            remote_listener                argalephdbprod.turner.com:1530

            utl_file_dir                   /ocfs/INTERFAZ/SAP/clientes, /ocfs/INTERFAZ/SAP/

                                           contratos, /ocfs/INTERFAZ/SAP/recibos, /ocfs/INT

                                           ERFAZ/SAP/enmiendas, /ocfs/INTERFAZ/SAP, /ocfs/I

                                           NTERFAZ, /ocfs/INTERFAZ/IBOPE, /ocfs/INTERFAZ/Et

                                           iquetas, /ocfs/INTERFAZ/Delta, /ocfs/INTERFAZ/SA

                                           P/GEMDERE/ABAVN, /ocfs/INTERFAZ/SAP/GEMDERE/VALO

                                           R_RESIDUAL, /ocfs/INTERFAZ/SAP/GEMDERE/ABGL, /oc

                                           fs/INTERFAZ/SAP/GEMDERE/AS01, /ocfs/INTERFAZ/SAP

                                           /GEMDERE/AS02, /ocfs/INTERFAZ/SAP/GEMDERE/FB01_C

                                           X38, /ocfs/INTERFAZ/SAP/GEMDERE/FB01_CX39, /ocfs

                                           /INTERFAZ/SAP, /ocfs/aleph_work/work/Exp_de_Gemi

                                           nis, /ocfs/aleph_work/work/Exp_de_Geminis/SCH_Pl

                                           aylists, /ocfs/aleph_work/work/Exp_de_Geminis/Pr

             

             

            NAME                           VALUE

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

                                           og, /ocfs/aleph_work/work/Exp_de_Geminis/TRX_Enc

                                           oda, /ocfs/aleph_work/work/Imp_a_Geminis, /ocfs/

                                           aleph_work/work/Imp_a_Geminis/Prog, /ocfs/aleph_

                                           work/work/Imp_a_Geminis/TRX_Encoda, /ocfs/aleph_

                                           work/work/imp_a_geminis/Logs_Playlists, /ocfs/al

                                           eph_work/work/imp_a_geminis/IBOPE, /ocfs/aleph_w

                                           ork/work/Roswell, /ocfs/aleph_work/work/Exp_de_G

                                           eminis/Multireportes, /ocfs/aleph_work/Pr

             

             

            job_queue_processes            1000

            cursor_sharing                 FORCE

            parallel_max_servers           160

            audit_file_dest                /u01/app/oracle/admin/ALEPHPR/adump

            audit_trail                    DB_EXTENDED

            db_name                        ALEPHPR

            open_cursors                   300

            dg_broker_start                TRUE

            dg_broker_config_file1         +DATA1/alephpr/dr1alephpr.dat

            dg_broker_config_file2         +FLASH/alephpr/dr2alephpr.dat

            diagnostic_dest                /u01/app/oracle

             

             

            50 rows selected.

             

             

            SQL>

            SQL> spool off