This discussion is archived
2 Replies Latest reply: Jun 24, 2013 9:08 PM by martin.morono RSS

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

martin.morono Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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