Standby archive apply hang due to space issue

Ideally at designing stage we should have allocated sufficient space for both PROD and STANDBY and they should match. For example, if a file system U05 is 500GB on PROD you should size the file system to 500GB on STANDBY as well. 

Well but not always. :)

We hang on issue when a DBA had added a datafile in Prod at U05 file system but due to lack space at DR Side for file system U05, archive apply stopped.

Standby_file_management is set to auto.

It was noted when archive apply was stopped due to space issue. 

Because is the steps taken to carried away: 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; 

SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/11.2.0/db_1/dbs/UNNAMED00143' AS '/u06/realfilename/gdata05.dbf'; 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

or, If you are using real time apply (it makes use of standby redo log file) 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 

Hope this helps...

RMAN-08137: WARNING: archive log not deleted as it is still needed

Recently my friend raised a discussion for “RMAN-08137: WARNING: archived log not deleted “ .
That make me remind of couple of issues I have faced with archive deletion policy when standby is configured.

Issue discussed is on 3 node RAC database with  on version 11gR2 with 3 node RAC on standby configuration.

We have set Archive deletion to

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Case (1) is for the ticket when error RMAN-08137 raised when user is trying to delete archive log using command  “backup archivelog all delete input”.

The error is very informative and it is because archive is not yet applied at Standby Database. Same can be checked with below command on production database:

select dest_id, thread#, sequence#, first_time, completion_time, registrar, archived, applied, deleted, status from v$archived_log where standby_dest='YES' and status='A';

Case (2) is user facing the FRA space, tried to deleted the archive log with below command and surprising archive logs are deleted before even shipped at standby database.

rman> backup archivelog all not backed up delete all input skip inaccessible;

Issue is stated as Bug:16692232 in MOS and One of patch is provided.

I have also faced couples of more cases for the rman-08137 when database is configured with streams replication  and those are because archived logs where still to be used by logminer .

query to check what query running into specific user at given time

SET HEADING ON

SET LINESIZE 300

SET PAGESIZE 60

COLUMN Sample_Time FOR A12

COLUMN username FOR A20

COLUMN sql_text FOR A40

COLUMN program FOR A25

COLUMN module FOR A25

SELECT

   to_char(sample_time,'DD Mon HH24:MI') as Sample_Time,

   u.username,

   h.program,

   h.module,

   s.sql_text

FROM

   DBA_HIST_ACTIVE_SESS_HISTORY h,

   DBA_USERS u,

   DBA_HIST_SQLTEXT s

WHERE  sample_time between '11-JUL-13 05.45.03.793 PM' and '11-JUL-13 06.45.04.455 PM'

   AND h.user_id=u.user_id

   AND h.sql_id = s.sql_iD

   AND u.username='SCOTT'

order by 1;

To check given query performance for any specific table:-

select a. snap_id, begin_interval_time,c.sql_text,

executions_delta,(elapsed_time_delta /executions_delta )/1000000,

plan_hash_value,(cpu_time_delta /executions_delta )/1000000,

rows_processed_delta/executions_delta ,(iowait_delta /executions_delta )/1000000

,(fetches_delta/ executions_delta)

,(disk_reads_delta/ executions_delta)

,(buffer_gets_delta/ executions_delta)

from dba_hist_sqlstat a, dba_hist_snapshot b ,

dba_hist_sqltext c where sql_text like '%table_name%' and a. snap_id= b. snap_id  and executions_delta ! =0 and a.sql_id=c.sql_id order by snap_id desc

Excess redo generation in begin backup mode: test case

Long backup I had asked with Ritesh (http://education.oracle.com/education/otn/rsingh.htm)  about the hidden parameter “_LOG_BLOCKS_DURING_BACKUP’ and he explain what exactly happened when any tablespace in taken in the begin backup mode.

Below are the highlights:

Step 1 : Freeze the Checkpoint for the requested datafiles, this allows the identification of backup mode for the datafile.

Step 2 : Any block writing / updating to the datafiles by DBWn will also copy the same block to Redo Log Files.

Step 1 is required for identifying the backup mode and initiating the image copy to be written to redo log files.

Step 2 is required for Fractured block recovery at a later stage, when the backup copy will be used for restore.

Let’s take a test case

e.g : I started a backup at 9:30 in the morning, backup started by putting USERS tablespace into backup mode, during the backup there were some batch process running simultaneously. Since the block size of my database is 8K, an update to Table (T)will affect a change inblocks of 8K Size, but since the backup is done by CP command, OS will copy the block of 512b as per OS Blocksize in each call.

When cp command will start copying, there would be a possibility that in order to copy one oracle block it has to make 16 calls and in between each call if the entire block of oracle is changed by the batch process, the outcome backup copy will be fractured. To avoid this oracle marks the same copy to Redo log, thus generating lots of redo.

Testing the scenario:

SQL> conn scott/tiger

Connected.

SQL> set autotrace trace stat

SQL> update test set ename = 'test' where empno = 7369;

1 row updated.

Statistics

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

          0  recursive calls

          3  db block gets

          3  consistent gets

          0  physical reads

        348  redo size

        671  bytes sent via SQL*Net to client

        617  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> rollback;

Rollback complete.

We can see, the redo is generated in minimum amount of time.  After then I put the uses tablespace in begin backup mode and executed the same statement:

SQL> update test set ename = 'test' where empno = 7369;

1 row updated.

Statistics

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

          0  recursive calls

          2  db block gets

          3  consistent gets

          0  physical reads

       8636  redo size

        671  bytes sent via SQL*Net to client

        617  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

But this time it generated large amount of redo. But when I executed the same statement again, the redo generation is normal.

SQL> update test set ename = 'test' where empno = 7369;

1 row updated.

Statistics

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

          0  recursive calls

          1  db block gets

          3  consistent gets

          0  physical reads

        348  redo size

        671  bytes sent via SQL*Net to client

        617  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

The first time ORACLE has generated the maximum redo (copying the entire block in the redo ) in order to recover from the fuzziness of data file at time of recovery if happen. But why ORACLE won’t generate the redo for second time, I have executed the same statement but for the different block:

SQL> update test set ename = 'test' where empno = 7934;

1 row updated.

Statistics

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

          1  recursive calls

          1  db block gets

          3  consistent gets

          0  physical reads

        8636  redo size

        673  bytes sent via SQL*Net to client

        617  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

This time, ORACLE has again generated the lage amount of redo. So what I concludes is ORACLE generate of redo by copying the entire block in redo logfile. But time happens for one time only. Even if any row changes in the same block, it won’t be copied again.

http://www.oracledba-in.com/2011/10/backup-generating-redo-disable.html

emcli configuration

/oms/grid/oms/oms/bin > ./emcli status

Oracle Enterprise Manager Cloud Control 12c Release 2.

Copyright (c) 1996, 2012 Oracle Corporation and/or its affiliates. All rights reserved.

Instance Home : /oravl01/oracle

Status        : Not Configured

/oms/grid/oms/oms/bin > ./emcli setup -url=https://suzan.com:7810/em -dir=/oms/grid/oms/oms -username=sysman -nocertvalidate -trustall

Oracle Enterprise Manager Cloud Control 12c Release 2.

Copyright (c) 1996, 2012 Oracle Corporation and/or its affiliates. All rights reserved.

Enter password

Emcli setup successful

/oms/grid/oms/oms/bin > ./emcli status

Oracle Enterprise Manager Cloud Control 12c Release 2.

Copyright (c) 1996, 2012 Oracle Corporation and/or its affiliates. All rights reserved.

Instance Home          : /oms/grid/oms/oms/.emcli

Verb Jars Home         : /oms/grid/oms/oms/.emcli

Status                 : Configured

EMCLI Home             : /oms/grid/oms/oms/bin/.

EMCLI Version          : 12.1.0.2.0

Java Home              : /oms/grid/oms/jdk16/jdk/jre

Java Version           : 1.6.0_24

Log file               : /oms/grid/oms/oms/.emcli/.emcli.log

EM URL                 : https://suzan.com:7810/em

EM user                : sysman

Auto login             : false

Trust all certificates : true

oms/grid/oms/oms/bin >

import error ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1677

When doing import using fromuser touser parameter got below error:

IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 108
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1677
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

Cause : the target schema got snapshot log.

Solution : Either delete the snapshot log, again take the export and re-import.

              Ensure user which got the snapshot log present in target database to prevent the error.

changing SYSMAN password in OEM12c

#./emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 2

Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Successfully Stopped

Oracle Management Server is Down

#./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys -new_pwd octco

Oracle Enterprise Manager Cloud Control 12c Release 2

Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.

Changing passwords in backend ...

Passwords changed in backend successfully.

Updating repository password in Credential Store...

Successfully updated Repository password in Credential Store.

Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.

Successfully changed repository password.

#./emctl stop oms -all

Oracle Enterprise Manager Cloud Control 12c Release 2

Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Already Stopped

AdminServer Successfully Stopped

Oracle Management Server is Down

#./emctl start oms

Oracle Enterprise Manager Cloud Control 12c Release 2

Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.

Starting Oracle Management Server...

Starting WebTier...

WebTier Successfully Started

Oracle Management Server Successfully Started

Oracle Management Server is Up

#