Redo logs on DR- Need Clarification

[tamil_arasan]

Dear Experts,

We have configured Oracle Dataguard and redo logs are coping to DR however not sure whether the redologs are being applied in DR as files in /oracle/<SID>/sapdata* showing yesterday's date.(we have restored backup on yesterday).

how to check whether the redo logs are being applied in the database. if not applied what needs to be done.

if DR is configured, how to take redologs backup through DB13? I mean what is the option needs to be seleted (-SSD,-SD etc)

Thanks in advance!

[Reagan · Accepted solution]

You can check the alert log of the standby database whether the logs are getting applied.

Also query the view v$log_history

Regards

RB

[tamil_arasan · Accepted solution]

Hi RB,

Thanks! I will check and update but when i give the command RECOVER MANAGED STANDBY DATABASE; it takes quite long time..and still in progress...is it normal?

Thanks!

Pradeep

[Reagan · Accepted solution]

Open a new terminal/session and view the alert log when you recover the standby database.

You will see whether there is some issue or not.

[tamil_arasan · Accepted solution]

Hi RB,

unfortunately, the alert log doesn't get update. The Primary and Secondary was in Sync before. few days back, it got broke due to network problem .

1. took offline backup of the primary and moved to DR

2.Copied ora<SID>.ora and orapw<SID> primary and copied to /oracle/<SID>/112_64/dbs in the DR server

3.created control file from primary by alter database create standby controlfile as '/oracle/<SID>/sapbackup/stdby_controlf.dbf' reuse; and moved to DR then replaced the control file according to parameter.

4.created spfile from pfile

5.mounted secondary DB

6.executed RECOVER MANAGED STANDBY DATABASE..

anything missing?

Kindly suggest!

Kind regards,

Pradeep

[Former Member · Accepted solution]

Hi,

You can check with

SQL>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

or more precisely, selecting the interval which redo logfiles are applied

SQL>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE# BETWEEN 28000 AND 28100 ORDER BY SEQUENCE#;

Regards

Shri

[tamil_arasan · Accepted solution]

Dear Shri,

Thanks! here is the output..i have the logs till 357 in the oraarch drive...it means the logs are being applied? but strange thing is alert log is not updating anything as well as the last modified date of the /oracle/<SID>/sapdata* drives showing 24th June when the backup restored...

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
       352 YES
       353 YES
       354 YES
       355 YES
       356 YES
       357 YES

6 rows selected.

SQL> SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE# BETWEEN 28000 AND 28100 ORDER BY SEQUENCE#;

no rows selected

[Reagan · Accepted solution]

Hello

You need to help us if you want us to help you.

I need to see the alert log of the standby database to know what is happening.

Provide me the output for this SQL query.

select max(recid) from v$log_history;

Stop the MRP

alter database recover managed standby database cancel;

Open a new session and view the alert log.

Start the recovery manually

recover standby database;

Provide the archive log the system is requesting.

Make sure the archive logs are present in the system.

Regards

RB

[Former Member · Accepted solution]

Hi,

First check archive log details

Primary

SQL>archive log list

DR

SQL>archive log list

compare the results from both.. has to be same sequence#

If your archive log list command shows latest file 357 then log shipping is happening and logs are getting applied I suppose

28000 and 28100 is just a sequence number, you can give your sequence like 301 and 400

Regards

[tamil_arasan · Accepted solution]

Hi RB,

Here is the alert log ouput :

Sun Jun 15 22:27:02 2014

RFS[137022]: Assigned to RFS process 6728

RFS[137022]: Database mount ID mismatch [0x61849199:0x606f6714] (1636077977:1617913620)

RFS[137022]: Not using real application clusters

Sun Jun 15 22:28:03 2014

RFS[137023]: Assigned to RFS process 7732

RFS[137023]: Database mount ID mismatch [0x61849199:0x606f6714] (1636077977:1617913620)

RFS[137023]: Not using real application clusters

Sun Jun 15 22:29:03 2014

RFS[137024]: Assigned to RFS process 8734

RFS[137024]: Database mount ID mismatch [0x61849199:0x606f6714] (1636077977:1617913620)

RFS[137024]: Not using real application clusters

Wed Jun 25 16:34:02 2014

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 2

ALTER DATABASE CLOSE NORMAL

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ALTER DATABASE DISMOUNT

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Wed Jun 25 16:34:06 2014

Stopping background process VKTM

Wed Jun 25 16:34:08 2014

Instance shutdown complete

Wed Jun 25 16:34:10 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side spfile /oracle/<SID>/112_64/dbs/spfile<SID>.ora

System parameters with non-default values:

  processes                = 80

  sessions                 = 160

  shared_pool_size         = 2176M

  filesystemio_options     = "setall"

  control_files            = "/oracle/<SID>/origlogA/cntrl/cntrl<SID>.dbf"

  control_files            = "/oracle/<SID>/origlogB/cntrl/cntrl<SID>.dbf"

  control_files            = "/oracle/<SID>/sapdata1/cntrl/cntrl<SID>.dbf"

  control_file_record_keep_time= 30

  db_block_size            = 8192

  db_cache_size            = 2176M

  compatible               = "11.2.0"

  log_archive_dest_1       = "LOCATION=/oracle/<SID>/oraarch/<SID>arch"

  log_archive_dest_2       = "SERVICE=PRIM reopen=60 ARCH NOAFFIRM"

  log_archive_dest_state_2 = "defer"

  standby_archive_dest     = "/oracle/<SID>/oraarch/<SID>arch"

  fal_client               = "STDBY"

  fal_server               = "PRIM"

  log_archive_format       = "%t_%s_%r.dbf"

  log_buffer               = 1703936

  db_recovery_file_dest    = "/oracle/<SID>/oraflash"

  db_recovery_file_dest_size= 30000M

  standby_file_management  = "AUTO"

  log_checkpoints_to_alert = TRUE

  replication_dependency_tracking= FALSE

  undo_tablespace          = "PSAPUNDO"

  _in_memory_undo          = FALSE

  recyclebin               = "off"

  remote_os_authent        = TRUE

  remote_login_passwordfile= "EXCLUSIVE"

  parallel_execution_message_size= 16384

  _table_lookup_prefetch_size= 0

  audit_file_dest          = "/oracle/<SID>/saptrace/audit"

  db_name                  = "<SID>"

  db_unique_name           = "<SID>_DR"

  open_cursors             = 2000

  _sort_elimination_cost_ratio= 10

  _b_tree_bitmap_plans     = FALSE

  star_transformation_enabled= "true"

  parallel_threads_per_cpu = 1

  query_rewrite_enabled    = "false"

  _index_join_enabled      = FALSE

  _optim_peek_user_binds   = FALSE

  pga_aggregate_target     = 3028958576

  _optimizer_mjc_enabled   = FALSE

  diagnostic_dest          = "/oracle/<SID>/saptrace"

  max_dump_file_size       = "20000"

Deprecated system parameters with specified values:

  standby_archive_dest

  remote_os_authent

End of deprecated system parameter listing

Wed Jun 25 16:34:11 2014

PMON started with pid=2, OS id=19270

Wed Jun 25 16:34:11 2014

PSP0 started with pid=3, OS id=19272

Wed Jun 25 16:34:12 2014

VKTM started with pid=4, OS id=19274 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Wed Jun 25 16:34:12 2014

GEN0 started with pid=5, OS id=19278

Wed Jun 25 16:34:13 2014

DIAG started with pid=6, OS id=19280

Wed Jun 25 16:34:13 2014

DBRM started with pid=7, OS id=19282

Wed Jun 25 16:34:13 2014

DIA0 started with pid=8, OS id=19284

Wed Jun 25 16:34:13 2014

MMAN started with pid=9, OS id=19286

Wed Jun 25 16:34:13 2014

DBW0 started with pid=10, OS id=19288

Wed Jun 25 16:34:13 2014

LGWR started with pid=11, OS id=19290

Wed Jun 25 16:34:13 2014

CKPT started with pid=12, OS id=19292

Wed Jun 25 16:34:13 2014

SMON started with pid=13, OS id=19294

Wed Jun 25 16:34:13 2014

RECO started with pid=14, OS id=19296

Wed Jun 25 16:34:13 2014

MMON started with pid=15, OS id=19298

Wed Jun 25 16:34:13 2014

MMNL started with pid=16, OS id=19300

ORACLE_BASE from environment = /oracle

Wed Jun 25 16:34:13 2014

ALTER DATABASE   MOUNT

Successful mount of redo thread 1, with mount id 1637097477

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Wed Jun 25 16:39:04 2014

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 1

ALTER DATABASE CLOSE NORMAL

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ALTER DATABASE DISMOUNT

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Wed Jun 25 16:39:08 2014

Stopping background process VKTM

Wed Jun 25 16:39:10 2014

Instance shutdown complete

[tamil_arasan · Accepted solution]

Here is the remaining outputs:

select max(recid) from v$log_history;

MAX(RECID)

----------

      6439

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> SQL> recover standby database;

ORA-00279: change 142961913 generated at 06/27/2014 10:00:53 needed for thread

1

ORA-00289: suggestion : /oracle/<SID>/oraarch/<SID>arch1_359_845663600.dbf

ORA-00280: change 142961913 for thread 1 is in sequence #359

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log

'/oracle/<SID>/oraarch/<SID>arch1_359_845663600.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Please note that the <SID>arch1_359_845663600.dbf has not generated by primary yet.

kindly let me know your suggestions.

Thanks a  lot!

[tamil_arasan · Accepted solution]

Thanks Shri!

Please refer the result below,

Secondary:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/<SID>/oraarch/<SID>arch

Oldest online log sequence     357

Next log sequence to archive   0

Current log sequence           360

Primary:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/<SID>/oraarch/<SID>arch

Oldest online log sequence     357

Next log sequence to archive   360

Current log sequence           360

[tamil_arasan · Accepted solution]

Hi Shri,

Sequence output:

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE# BETWEEN 344 AND 360 ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
       352 YES
       353 YES
       354 YES
       355 YES
       356 YES
       357 YES
       358 YES
       359 NO

8 rows selected.

[Reagan · Accepted solution]

Hello Pradeep

and if you run the query on the primary what is the output ?

select max(recid) from v$log_history;

The standby is asking for sequence number 359 and the primary has not yet created the log 359.

This means the primary and standby are on sync.

You can check using "archive log list" query

You can do a log switch on primary and see whether it is getting applied on the standby by checking the alert log of the standby.

ALTER SYSTEM SWITCH LOGFILE;

Regards

RB

[tamil_arasan · Accepted solution]

Hi RB,

I am getting this output in both primary and secondary,

SQL> select max(recid) from v$log_history;

MAX(RECID)
----------
      6440

I have executed the query in primary ALTER SYSTEM SWITCH LOGFILE; and waiting for the new log generation in primary..

Thanks a lot RB...also please let me know if i scedule backup of archivelogs every 2 hours through DB13, what option need to selected -ssd or -sd? which one is best? since the archive logs backup should not break the sequence copying to secondary..

Thanks a lot once again.

[Reagan · Accepted solution]

Hello Pradeep

You can refer this link for assistance.

https://help.sap.com/saphelp_nw70/helpdata/en/23/1ec04f11124f9e9598dff6d9fbc59e/content.htm

-s|-sc|-ds|-dc|-sd|-scd|-ss|-ssd|-cs|-cds (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

We have systems with CDS option.

Regards

RB

[Former Member · Accepted solution]

Hi,

You can also cross verify with

Primary

SQL> alter system switch logfile;

SQL>archive log list

DR

SQL>archive log list

SQL>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE# BETWEEN 344 AND 370 ORDER BY SEQUENCE#;

You can execute alter system switch logfile command  on Primary, multiple times to cross verify

[Former Member]

Hi,

-cds is for Tape backup.  you can use -s for local disk backup

[tamil_arasan]

Thanks Shri,

I have selected -sd and checked, it works perfectly. I have written script to delete old (oler than 3 days) redo logs in secondary.

Everything looks good. Thanks a lot for your help!

Kind regards,

Pradeep.

[tamil_arasan]

Thanks RB and Shri,

The pproblem is I am taking redologs backup in disk which is not supported by -cds option. the given help portal link is not working. also please let me know how to delete the redologs once it is restored in secondary without manual interaction.

Thanks!,

Pradeep