This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Dec 9, 2012 9:09 PM by Athurumithuru RSS

Recreate Standby using full datafile backup

Athurumithuru Newbie
Currently Being Moderated
Dear All,

I have to recreate my standby database due to huge archive gap. I have full datafile backup of primary. Pls help me.

Thank you
  • 1. Re: Recreate Standby using full datafile backup
    Niket Kumar Pro
    Currently Being Moderated
    http://shivanandarao.wordpress.com/2012/03/26/roll-forward-physical-standby-database-using-rman-incremental-backup/
  • 2. Re: Recreate Standby using full datafile backup
    CKPT Guru
    Currently Being Moderated
    Athurumithuru wrote:
    Dear All,

    I have to recreate my standby database due to huge archive gap. I have full datafile backup of primary. Pls help me.

    Thank you
    if there is an huge GAP, you can go with Incremental roll forward.
    I didnt understand the second sentence you mentioned "I have full datafile backup of primary", You mean to say FULL database backup? If your database size is too big then to recreate standby its again time taking. If your database size is less and you are comfortable then you can go ahead or else go with incremental, you can also check the below link very detailed with screenshots.

    http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/
  • 3. Re: Recreate Standby using full datafile backup
    Athurumithuru Newbie
    Currently Being Moderated
    Dear All,

    Thanx for your helps. But I don't have any incremental level backups. I have data files which were taken after shutting down the database. Can I do something using those data files. Hope you will understand my situation.

    Rgds,
    Athurumithuru.
  • 4. Re: Recreate Standby using full datafile backup
    CKPT Guru
    Currently Being Moderated
    Athurumithuru wrote:
    Dear All,

    Thanx for your helps. But I don't have any incremental level backups. I have data files which were taken after shutting down the database. Can I do something using those data files. Hope you will understand my situation.

    Rgds,
    Athurumithuru.
    Have you reviewed referred link? Because you have to perform backup again from primary database.
    If you have cold backup of database, then simply you can create standby control file from primary and mount it. However all the data files already exist. So you can start MRP. Hope this helps.

    commands from primary SQL> alter database create standby controlfile as 'location';
  • 5. Re: Recreate Standby using full datafile backup
    Athurumithuru Newbie
    Currently Being Moderated
    Dear CKPT,

    Thanks for your reply. I did as you instructed. All the commands were success. but see the alert.log


    alter database recover managed standby database using current logfile disconnect from session
    Attempt to start background Managed Standby Recovery process (cstdby)
    MRP0 started with pid=26, OS id=17657
    MRP0: Background Managed Standby Recovery process started (cstdby)
    2012-12-06 12:27:17.805000 +05:30
    started logmerger process
    Managed Standby Recovery starting Real Time Apply
    Parallel Media Recovery started with 16 slaves
    2012-12-06 12:27:18.877000 +05:30
    Waiting for all non-current ORLs to be archived...
    All non-current ORLs have been archived.
    Media Recovery Waiting for thread 1 sequence 28550 (in transit)
    Completed: alter database recover managed standby database using current logfile disconnect from session
    2012-12-06 12:27:39.012000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:28:39.024000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:29:37.725000 +05:30
    db_recovery_file_dest_size of 128000 MB is 0.60% used. This is a
    user-specified limit on the amount of space that will be used by this
    database for recovery-related files, and does not reflect the amount of
    space available in the underlying filesystem or ASM diskgroup.
    2012-12-06 12:29:39.027000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:29:43.821000 +05:30
    RFS[7]: Possible network disconnect with primary database
    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/CSTDBY/archivelog/2012_12_06/o1_mf_1_28562_8d0j5gnv_.arc
    RFS[35]: Assigned to RFS process 17710
    RFS[35]: Identified database type as 'physical standby': Client is ARCH pid 6030
    RFS[36]: Assigned to RFS process 17712
    RFS[36]: Identified database type as 'physical standby': Client is ARCH pid 6032
    2012-12-06 12:29:45.072000 +05:30
    RFS[36]: Opened log for thread 1 sequence 28562 dbid -336253692 branch 767896455
    2012-12-06 12:29:50.815000 +05:30
    RFS[8]: Possible network disconnect with primary database
    2012-12-06 12:32:39.043000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:33:39.055000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:34:39.066000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:34:46.788000 +05:30
    RFS[36]: Possible network disconnect with primary database
    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/CSTDBY/archivelog/2012_12_06/o1_mf_1_28562_8d0jm0v5_.arc
    2012-12-06 12:34:49.183000 +05:30
    RFS[37]: Assigned to RFS process 17761
    RFS[37]: Identified database type as 'physical standby': Client is ARCH pid 6448
    RFS[37]: Opened log for thread 1 sequence 28562 dbid -336253692 branch 767896455

    Rgds,
    Athurumithuru.
  • 6. Re: Recreate Standby using full datafile backup
    CKPT Guru
    Currently Being Moderated
    Athurumithuru wrote:
    Dear CKPT,

    Thanks for your reply. I did as you instructed. All the commands were success. but see the alert.log


    alter database recover managed standby database using current logfile disconnect from session
    Attempt to start background Managed Standby Recovery process (cstdby)
    MRP0 started with pid=26, OS id=17657
    MRP0: Background Managed Standby Recovery process started (cstdby)
    2012-12-06 12:27:17.805000 +05:30
    started logmerger process
    Managed Standby Recovery starting Real Time Apply
    Parallel Media Recovery started with 16 slaves
    2012-12-06 12:27:18.877000 +05:30
    Waiting for all non-current ORLs to be archived...
    All non-current ORLs have been archived.
    Media Recovery Waiting for thread 1 sequence 28550 (in transit)
    Completed: alter database recover managed standby database using current logfile disconnect from session
    2012-12-06 12:27:39.012000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:28:39.024000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:29:37.725000 +05:30
    db_recovery_file_dest_size of 128000 MB is 0.60% used. This is a
    user-specified limit on the amount of space that will be used by this
    database for recovery-related files, and does not reflect the amount of
    space available in the underlying filesystem or ASM diskgroup.
    2012-12-06 12:29:39.027000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:29:43.821000 +05:30
    RFS[7]: Possible network disconnect with primary database
    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/CSTDBY/archivelog/2012_12_06/o1_mf_1_28562_8d0j5gnv_.arc
    RFS[35]: Assigned to RFS process 17710
    RFS[35]: Identified database type as 'physical standby': Client is ARCH pid 6030
    RFS[36]: Assigned to RFS process 17712
    RFS[36]: Identified database type as 'physical standby': Client is ARCH pid 6032
    2012-12-06 12:29:45.072000 +05:30
    RFS[36]: Opened log for thread 1 sequence 28562 dbid -336253692 branch 767896455
    2012-12-06 12:29:50.815000 +05:30
    RFS[8]: Possible network disconnect with primary database
    2012-12-06 12:32:39.043000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:33:39.055000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:34:39.066000 +05:30
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    2012-12-06 12:34:46.788000 +05:30
    RFS[36]: Possible network disconnect with primary database
    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/CSTDBY/archivelog/2012_12_06/o1_mf_1_28562_8d0jm0v5_.arc
    2012-12-06 12:34:49.183000 +05:30
    RFS[37]: Assigned to RFS process 17761
    RFS[37]: Identified database type as 'physical standby': Client is ARCH pid 6448
    RFS[37]: Opened log for thread 1 sequence 28562 dbid -336253692 branch 767896455

    Rgds,
    Athurumithuru.
    I cant see any errors except possible network disconnect with primary database, It happens eventually if there is any network delay between two sites.
    Perform couple of logswitches and use below two queries as below and post the results.

    Primary:- select max(sequence#) from v$archived_log;
    SQL>select severity,error_code,message,timestamp from v$dataguard_status where dest_id=2;

    Standby: select max(sequence#) from v$archived_log where applied='YES';
  • 7. Re: Recreate Standby using full datafile backup
    Athurumithuru Newbie
    Currently Being Moderated
    Dear CKPT,

    Thanx for your advice..

    At Primary Site
    ==========
    1. select max(sequence#) from v$archived_log;
    28595

    2. select severity,error_code,message,timestamp from v$dataguard_status where dest_id=2;

    Error     12541     FAL[server, ARCb]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCk]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCf]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCs]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCq]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCi]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC5]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCj]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCp]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCm]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCd]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCl]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCg]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCt]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCo]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC4]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC3]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC8]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC6]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC0]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCn]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC1]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARC7]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCe]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     FAL[server, ARCa]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:44:06 PM
    Error     12541     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12541.     12/6/2012 12:44:22 PM
    Error     12541     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12541.     12/6/2012 12:45:22 PM
    Error     12541     Error 12541 for archive log file 2 to 'cstdby'     12/6/2012 12:45:56 PM
    Error     12541     FAL[server, ARCc]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:45:56 PM
    Error     12541     FAL[server, ARCe]: Error 12541 creating remote archivelog file 'cstdby'     12/6/2012 12:45:57 PM
    Error     12541     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12541.     12/6/2012 12:46:25 PM
    Error     12541     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12541.     12/6/2012 12:47:25 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 17929) hung on a network operation     12/6/2012 12:55:35 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 17031) hung on a network operation     12/6/2012 12:55:36 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 6032) hung on a network operation     12/6/2012 1:00:43 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 18578) hung on a network operation     12/6/2012 1:04:58 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 6030) hung on a network operation     12/6/2012 1:05:00 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 5391) hung on a network operation     12/6/2012 1:05:01 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 6448) hung on a network operation     12/6/2012 1:05:02 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 19209) hung on a network operation     12/6/2012 1:05:04 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 6904) hung on a network operation     12/6/2012 1:10:47 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 13268) hung on a network operation     12/6/2012 1:13:17 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 6906) hung on a network operation     12/6/2012 1:13:19 PM
    Error     12514     FAL[server, ARCj]: Error 12514 creating remote archivelog file 'cstdby'     12/6/2012 1:13:25 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 7386) hung on a network operation     12/6/2012 1:15:52 PM
    Error     3113     FAL[server, ARCq]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARCj]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     16198     WARN: ARCq: Terminating ARCH (pid 18233) hung on a network operation     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARC2]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARCg]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARC1]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARC9]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARC0]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     NSA: Error 3113 archiving log 2 to 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARCf]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARC8]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3135     FAL[server, ARCt]: FAL archival, error 3135 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     3113     FAL[server, ARCs]: FAL archival, error 3113 closing archivelog file 'cstdby'     12/6/2012 1:20:12 PM
    Error     12514     FAL[server, ARCj]: Error 12514 creating remote archivelog file 'cstdby'     12/6/2012 1:20:17 PM
    Error     12514     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12514.     12/6/2012 1:20:17 PM
    Error     12541     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12541.     12/6/2012 1:24:02 PM
    Error     12514     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12514.     12/6/2012 1:25:05 PM
    Error     12514     PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12514.     12/6/2012 1:26:05 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 18810) hung on a network operation     12/6/2012 1:32:13 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 8224) hung on a network operation     12/6/2012 1:33:19 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 7836) hung on a network operation     12/6/2012 1:35:26 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 9182) hung on a network operation     12/6/2012 1:36:33 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 9260) hung on a network operation     12/6/2012 1:39:42 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 9468) hung on a network operation     12/6/2012 1:39:44 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 7653) hung on a network operation     12/6/2012 1:39:45 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 6910) hung on a network operation     12/6/2012 1:39:46 PM
    Error     16198     WARN: ARC9: Terminating ARCH (pid 5361) hung on a network operation     12/6/2012 1:40:14 PM
    Error     16198     WARN: ARCd: Terminating ARCH (pid 5361) hung on a network operation     12/6/2012 1:40:15 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 9665) hung on a network operation     12/6/2012 1:40:56 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 9665) hung on a network operation     12/6/2012 1:40:58 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 19673) hung on a network operation     12/6/2012 1:44:04 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 9940) hung on a network operation     12/6/2012 1:44:05 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 10044) hung on a network operation     12/6/2012 1:45:13 PM
    Error     16198     WARN: ARCf: Terminating ARCH (pid 10044) hung on a network operation     12/6/2012 1:45:15 PM
    Error     16198     WARN: ARCr: Terminating ARCH (pid 9938) hung on a network operation     12/6/2012 1:46:00 PM
    Error     16198     WARN: ARCh: Terminating ARCH (pid 9938) hung on a network operation     12/6/2012 1:46:03 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 18371) hung on a network operation     12/6/2012 1:47:31 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 20131) hung on a network operation     12/6/2012 1:48:37 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 10341) hung on a network operation     12/6/2012 1:48:38 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 10425) hung on a network operation     12/6/2012 1:51:55 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 9934) hung on a network operation     12/6/2012 1:53:02 PM
    Error     16198     WARN: ARC2: Terminating ARCH (pid 10786) hung on a network operation     12/6/2012 1:53:03 PM

    At Standby Site
    ===========

    1. select max(sequence#) from v$archived_log where applied='YES';

    28549

    Thanx & Rgds,
    Athurumithuru.
  • 8. Re: Recreate Standby using full datafile backup
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    Is listener up and running on the standby server ? From the standby server please post:

    $lsnrctl status




    Regards,

    Shivananda
  • 9. Re: Recreate Standby using full datafile backup
    CKPT Guru
    Currently Being Moderated
    Error 12541 PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12541. 12/6/2012 12:46:25 PM
    Error 12541 PING[ARC2]: Heartbeat failed to connect to standby 'cstdby'. Error is 12541. 12/6/2012 12:47:25 PM
    Error 16198 WARN: ARCr: Terminating ARCH (pid 17929) hung on a network operation 12/6/2012 12:55:35 PM
    What is status of listener from standby? Are you able to connect from primary to standby? I dont think your listener is up and running and allowing for any incoming connections.

    Post from standbyb
    $lsnrctl status
    $lsnrctl reload
    SQL> alter system register;
    SQL> lsnrctl status
    12541, 00000, "TNS:no listener"
    // *Cause: The connection request could not be completed because the listener
    // is not running.
    // *Action: Ensure that the supplied destination address matches one of
    // the addresses used by the listener - compare the TNSNAMES.ORA entry with
    // the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
    // go by way of an Interchange). Start the listener on the remote machine.
  • 10. Re: Recreate Standby using full datafile backup
    Athurumithuru Newbie
    Currently Being Moderated
    Dear All,

    Pls find the details..



    [oracle@dbcdslstdby ~]$ lsnrctl status

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-DEC-2012 13:54:18

    Copyright (c) 1991, 2009, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbcdslstdby.test.com)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 06-DEC-2012 12:59:04
    Uptime 0 days 0 hr. 55 min. 14 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/dbcdslstdby/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbcdslstdby.test.com)(PORT=1521)))
    Services Summary...
    Service "cstdby" has 1 instance(s).
    Instance "cstdby", status READY, has 1 handler(s) for this service...
    Service "cstdby.expolanka.com" has 1 instance(s).
    Instance "cstdby", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    [oracle@dbcdslstdby ~]$
    [oracle@dbcdslstdby ~]$
    [oracle@dbcdslstdby ~]$
    [oracle@dbcdslstdby ~]$
    [oracle@dbcdslstdby ~]$ lsnrctl reload

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-DEC-2012 13:55:45

    Copyright (c) 1991, 2009, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbcdslstdby.test.com)(PORT=1521)))
    The command completed successfully





    [oracle@dbcdslstdby ~]$ lsnrctl status

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-DEC-2012 13:59:11

    Copyright (c) 1991, 2009, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbcdslstdby.test.com)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 06-DEC-2012 12:59:04
    Uptime 0 days 1 hr. 0 min. 6 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/dbcdslstdby/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbcdslstdby.test.com)(PORT=1521)))
    Services Summary...
    Service "cstdby" has 1 instance(s).
    Instance "cstdby", status READY, has 1 handler(s) for this service...
    Service "cstdby.expolanka.com" has 1 instance(s).
    Instance "cstdby", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    [oracle@dbcdslstdby ~]$
    [oracle@dbcdslstdby ~]$


    Thank you very much for your support again...

    Rgds,

    Athurumithuru.

    Edited by: Athurumithuru on Dec 6, 2012 2:33 PM
  • 11. Re: Recreate Standby using full datafile backup
    CKPT Guru
    Currently Being Moderated
    the servcies are registered, Now tell me can you able to connect from primary to standby using Oracle NET service as below from primary?

    $sqlplus sys/****@cstdby as sysdba
    and
    $tnsping cstdby


    THere seems to be network hung between two sites, By above commands based on that you might have to work in terms of network/bandwidth
  • 12. Re: Recreate Standby using full datafile backup
    Athurumithuru Newbie
    Currently Being Moderated
    Dear CKPT,

    Details requested as bellow:

    [oracle@dbcdsl ~]$ sqlplus sys@cstdby as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:40:38 2012

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

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL>
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@dbcdsl ~]$
    [oracle@dbcdsl ~]$
    [oracle@dbcdsl ~]$ tnsping cstdby

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-DEC-2012 14:41:11

    Copyright (c) 1997, 2009, Oracle. All rights reserved.

    Used parameter files:
    /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbcdslstdby.test.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cstdby)))
    OK (150 msec)
    [oracle@dbcdsl ~]$




    Also find the status monitor report from both sides - Hope this will help you more....



    SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:35:36 2012

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

    ==============
    At Production
    ==============

    Archive Log List - Production(Before Switch Logfile)
    ----------------------------------------------------
    Database log mode     Archive Mode
    Automatic archival     Enabled
    Archive destination     USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 28596
    Next log sequence to archive 28598
    Current log sequence     28598

    Alter System Switch Logfile
    ---------------------------

    System altered.


    System altered.


    System altered.


    Archive Log List - Production(After Switch Logfile)
    ---------------------------------------------------
    Database log mode     Archive Mode
    Automatic archival     Enabled
    Archive destination     USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 28599
    Next log sequence to archive 28600
    Current log sequence     28601

    View Archive Destinations - Production
    -------------------------

    ERROR                                        STATUS
    ----------------------------------------------------------------- ---------
                                            VALID
                                            VALID


    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:36:39 2012

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options


    ===========
    At Standby
    ===========

    Archive Log List - Standby
    --------------------------
    Database log mode     Archive Mode
    Automatic archival     Enabled
    Archive destination     USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 28596
    Next log sequence to archive 0
    Current log sequence     28601

    Select Archive Gap
    ------------------

    THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
    ---------- ------------- --------------
         1     28579     28580


    View Archive Destinations - Standby
    -----------------------------------

    ERROR                                        STATUS
    ----------------------------------------------------------------- ---------
                                            VALID
                                            VALID



    SEQUENCE# APPLIED ARC
    ---------- --------- ---
    28600 NO     YES
    28599 NO     YES
    28596 NO     YES
    28594 NO     YES
    28593 NO     YES
    28591 NO     YES
    28590 NO     YES
    28588 NO     YES
    28587 NO     YES
    28586 NO     YES

    10 rows selected.


    Thank you,

    Athurumithuru

    Edited by: Athurumithuru on Dec 6, 2012 2:37 PM
  • 13. Re: Recreate Standby using full datafile backup
    CKPT Guru
    Currently Being Moderated
    Looks everything perfect,
    >
    SEQUENCE# APPLIED ARC
    --------- ---
    28600 NO YES
    28599 NO YES
    28596 NO YES
    28594 NO YES
    28593 NO YES
    28591 NO YES
    28590 NO YES
    28588 NO YES
    28587 NO YES
    28586 NO YES
    >

    This output is from primary or standby?
    Have you started MRP, Can you stop and start MRP once again?

    If still there are no updates on standby even in alert log file, can you please use below script and post output from primary and standby. http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
  • 14. Re: Recreate Standby using full datafile backup
    Athurumithuru Newbie
    Currently Being Moderated
    Dear CKPT,

    Thats generated from stand by site.

    here is the results of your wonderful scripts..

    Primary

    SQL> spool dg_Primary_output.log
    SQL> set feedback off
    SQL> set trimspool on
    SQL> set line 500
    SQL> set pagesize 50
    SQL> column name for a30
    SQL> column display_value for a30
    SQL> column ID format 99
    SQL> column "SRLs" format 99
    SQL> column active format 99
    SQL> col type format a4
    SQL> column ID format 99
    SQL> column "SRLs" format 99
    SQL> column active format 99
    SQL> col type format a4
    SQL> col PROTECTION_MODE for a20
    SQL> col RECOVERY_MODE for a20
    SQL> col db_mode for a15
    SQL> SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
    col name for a10

    NAME DISPLAY_VALUE
    ------------------------------ ------------------------------
    db_file_name_convert dbcdsl_stby, dbcdsl
    db_name dbcdsl
    db_unique_name dbcdsl
    dg_broker_config_file1 /u01/app/oracle/product/11.2.0
    /db_1/dbs/dr1dbcdsl.dat

    dg_broker_config_file2 /u01/app/oracle/product/11.2.0
    /db_1/dbs/dr2dbcdsl.dat

    dg_broker_start FALSE
    fal_client
    fal_server
    local_listener
    log_archive_config dg_config=(dbcdsl,cstdby)
    log_archive_dest_2 service=cstdby async
    valid_for=(online_logfile,prim
    ary_role)
    db_unique_name=cstdby

    log_archive_dest_state_2 ENABLE
    log_archive_max_processes 30
    log_file_name_convert dbcdsl_stby, dbcdsl
    remote_login_passwordfile EXCLUSIVE
    standby_archive_dest ?/dbs/arch
    standby_file_management MANUAL
    SQL> SQL> col DATABASE_ROLE for a10
    SQL> SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database;
    select thread#,max(sequence#) from v$archived_log group by thread#;
    SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
    FROM
    (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

    NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE SWITCHOVER_STATUS
    ---------- ------------------------------ -------------------- ---------- -------------------- --------------------
    DBCDSL dbcdsl MAXIMUM PERFORMANCE PRIMARY READ WRITE RESOLVABLE GAP
    SQL> (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
    WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
    col severity for a15
    col message for a70
    col timestamp for a20
    select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;
    select ds.dest_id id
    , ad.status
    , ds.database_mode db_mode
    , ad.archiver type

    THREAD# MAX(SEQUENCE#)
    ---------- --------------
    1 28601
    SQL> 2 3 4 5 , ds.recovery_mode
    , ds.protection_mode
    , ds.standby_logfile_count "SRLs"
    , ds.standby_logfile_active active
    , ds.archived_seq#
    from v$archive_dest_status ds
    , v$archive_dest ad
    where ds.dest_id = ad.dest_id
    and ad.status != 'INACTIVE'
    order by
    ds.dest_id;
    column FILE_TYPE format a20
    col name format a60
    select name
    , floor(space_limit / 1024 / 1024) "Size MB"
    , ceil(space_used / 1024 / 1024) "Used MB"
    from v$recovery_file_dest
    order by name;
    spool off


    Stand BY
    [oracle@dbcdslstdby ~]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:33:45 2012

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL>
    SQL>
    SQL>
    SQL> spool dg_standby_output.log
    set feedback off
    set trimspool on
    set line 500
    set pagesize 50
    set linesize 200
    column name for a30
    column display_value for a30
    col value for a10
    col PROTECTION_MODE for a15
    SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> col DATABASE_Role for a15
    SQL> SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
    col name for a10
    col DATABASE_ROLE for a10
    SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database;
    select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
    select process, status,thread#,sequence# from v$managed_standby;
    col name for a30
    select * from v$dataguard_stats;

    NAME DISPLAY_VALUE
    ------------------------------ ------------------------------
    db_file_name_convert /dbcdsl/, /cstdby/
    db_name dbcdsl
    db_unique_name cstdby
    dg_broker_config_file1 /u01/app/oracle/product/11.2.0
    /db_1/dbs/dr1cstdby.dat

    dg_broker_config_file2 /u01/app/oracle/product/11.2.0
    /db_1/dbs/dr2cstdby.dat

    dg_broker_start FALSE
    fal_client cstdby
    fal_server dbcdsl
    local_listener
    log_archive_config dg_config=(dbcdsl,cstdby)
    log_archive_dest_2 service=dbcdsl ASYNC valid_for
    =(ONLINE_LOGFILE,PRIMARY_ROLE)
    db_unique_name=dbcdsl

    log_archive_dest_state_2 ENABLE
    log_archive_max_processes 5
    log_file_name_convert /dbcdsl/, /cstdby/
    remote_login_passwordfile EXCLUSIVE
    select * from v$archive_gap;
    standby_archive_dest ?/dbs/arch
    standby_file_management AUTO
    SQL> SQL> SQL> col name format a60
    select name
    , floor(space_limit / 1024 / 1024) "Size MB"

    NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE
    ---------- ------------------------------ --------------- ---------- --------------------
    DBCDSL cstdby MAXIMUM PERFORM PHYSICAL S MOUNTED
    ANCE TANDBY

    SQL> , ceil(space_used / 1024 / 1024) "Used MB"

    THREAD# MAX(SEQUENCE#)
    ---------- --------------
    1 28549
    SQL> from v$recovery_file_dest
    order by name;

    PROCESS STATUS THREAD# SEQUENCE#
    --------- ------------ ---------- ----------
    ARCH CLOSING 1 28586
    ARCH CONNECTED 0 0
    ARCH CLOSING 1 28596
    ARCH CONNECTED 0 0
    ARCH CLOSING 1 28597
    RFS IDLE 1 28555
    MRP0 WAIT_FOR_GAP 1 28550
    RFS IDLE 1 28595
    RFS IDLE 1 28560
    RFS IDLE 1 28561
    RFS IDLE 1 28550
    RFS IDLE 1 28552
    RFS IDLE 1 28551
    RFS IDLE 1 28553
    RFS IDLE 0 0
    RFS IDLE 1 28580
    RFS IDLE 1 28554
    RFS IDLE 1 28556
    RFS IDLE 1 28601
    RFS IDLE 1 28579
    RFS IDLE 1 28564
    RFS IDLE 0 0
    RFS IDLE 0 0
    RFS IDLE 1 28557
    RFS IDLE 0 0
    SQL> SQL> spool off
    NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
    ------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------
    transport lag day(2) to second(0) interval 12/06/2012 14:33:49
    apply lag day(2) to second(0) interval 12/06/2012 14:33:49
    apply finish time day(2) to second(3) interval 12/06/2012 14:33:49
    estimated startup time 23 second 12/06/2012 14:33:49
    SQL>
    THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
    ---------- ------------- --------------
    1 28579 28580
    SQL> SQL> 2 3 4 5
    NAME Size MB Used MB
    ------------------------------------------------------------ ---------- ----------
    /u01/app/oracle/flash_recovery_area 128000 49
    SQL>
    SQL>
1 2 Previous Next

Legend

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