1 2 Previous Next 24 Replies Latest reply: Dec 9, 2012 11:09 PM by Athurumithuru RSS

    Recreate Standby using full datafile backup

    Athurumithuru
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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