Media Recovery terminated with error 28365 # TDE

>>> >>>> >>>> >>>

scenario # created Oracle Wallet <<

>> MRP stopped on standby # 

SQL> select * from v$dataguard_status where severity in ('Error','Fatal') and timestamp > (sysdate -1);

FACILITY SEVERITY   DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP

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

MESSAGE

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

Log Apply Services Error 0   4         28365 YES  13-OCT-16

MRP0: Background Media Recovery terminated with error 28365

SQL> select * from v$encryption_wallet;

WRL_TYPE  WRL_PARAMETER  STATUS

————————————————————————————————

file     /u02/encrypt/    CLOSED

Resolution # >>>> 
Steps to perform to resolve Issue on standby >>>
shutdown the standby database 
scp cwallet.sso & ewallet.p12 to standby server wallet location from primary 
Mount the database 
Start MRP process 

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

From Primary > scp cwallet.sso and ewallet.p12 

[oracle@dg1 encrypt]$ scp * oracle@192.168.56.104:/u02/encrypt/

oracle@192.168.56.104's password: 

cwallet.sso                                   100% 2923     2.9KB/s   00:00    

ewallet.p12                                   100% 2845     2.8KB/s   00:00 

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2257840 bytes

Variable Size   541068368 bytes

Database Buffers   289406976 bytes

Redo Buffers     2371584 bytes

Database mounted.

SQL> select * from v$encryption_wallet;

WRL_TYPE  WRL_PARAMETER  STATUS

————————————————

file     /u02/encrypt/    OPEN

> Start the MRP on standby< 

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process from v$managed_standby where process like 'MRP%';

PROCESS

---------

MRP0

>>> 

--Nikhil Tatineni--

--Dataguard-- 
Links Related to Datagaurd <
Applying logs with delay # Standby database #
http://www.oracleinmemory.com/2016/07/dataguard-delay-parameter.html
Physical Standby # Active Dataguard 11g
http://www.oracleinmemory.com/2016/07/physical-standby-active-dataguard-11g.html
Snapshot standby # 11g
http://www.oracleinmemory.com/2016/07/dataguard-converting-physical-standby.html
Troubleshooting #
Dataguard # oldest online log sequence 0
http://www.oracleinmemory.com/2016/07/dataguard-oldest-online-log-sequence-0.html

ARBx # Slave Process in ASM

DBA manage databases on ASM & manages ASM disk groups.  When we notice  the disk group holding database is filled, DBA will add Brand new disk to existing disk group. Here I want to elaborate the background process of ASM i.e (ARBx), How ASM will perform rebalancing operation? ASM allocate ARBx( Actual Rebalancing process ) to perform rebalancing operation inside the disk group. ARBx is responsible for data movement between disks inside the disk group. The number of rebalancing process allocated depend on ASM_POWER_LIMIT value. Based on ASM disk group compatibility, we can alter ASM_POWER_LIMIT

if disk group asm  compatibility >= 11.2.0.2 (  alter asm_power_limit from 0 to 1024) 

if disk group asm compatibility < 11.2.0.2 ( alter asm_power_limit from 0 to 11) 

Query to check ASM disk group compatibility using v$asm_diskgroup
col COMPATIBILITY form a10
SQL> col DATABASE_COMPATIBILITY form a10
SQL> col NAME form a20
SQL> select group_number, name,
compatibility, database_compatibility from v$asm_diskgroup;

 Regularly when we will add disk to the disk group by logging into SYSASM (or) using ASMCA 

sql> conn / as sysasm

sql> alter diskgroup stage add disk '/dev/asm-disk8','/dev/asm-disk9';

sql>alter diskgroup DISKGROUP rebalance power 5;

when you say 5, 5 actual rebalancing (ARBx) process are allocated inside the diskgroup to improve performance we can alter this number to higher value, where 0 will disable the Rebalancing process

Query performance of ASM_ Rebalancing operation can be monitored using "v$asm_operation"

 SQL> select group_number,operation,state,power,est_minutes from v$asm_operation;

Note > It is always recommended to add a disk to existing disk group during off hours, rebalancing operation will impact the performance of database 

Related Links to ASM # 
Adding disk to existing diskgroup 
http://www.oracleinmemory.com/search?q=adding+disk+to+diskgroup
ACFS RAC ASM
http://www.oracleinmemory.com/2016/03/acfs-automatic-cluster-filesystem-on.html
ASMCMD> ASM command line utility
http://www.oracleinmemory.com/2016/01/asm-asmcmd.html

Adding a disk to diskgroup > ASM

http://www.oracleinmemory.com/2016/09/adding-disk-to-diskgroup-asm.html

Friendly DBA: Sample Datapump Export and Import Scripts

Based on requirement, modify  Datapump Export and Import scripts 

[oracle@dg1 export]$ cat exp_oxford_schema.sh

TS_EXPORT=/u01/app/export

PWD=oracle

LOGFILE=exp_schema_oxford.log

ST=`date`

echo $PWD | expdp system directory=db_export  dumpfile=exp_schema_oxford.dmp  content=all logfile=$LOGFILE schemas=oxford job_name=exp_oxford

ET=`date`

echo "Start Time=$ST" >>$LOGFILE

echo "End Time=$ET" >>$LOGFILE

[oracle@dg2 export]$ cat impdp_oxford.sh 

DB_EXPORT=/u01/app/export

PWD=oracle

LOGFILE=imp_schema_oxford.log

ST=`date`

echo $PWD | impdp system directory=db_export  dumpfile=exp_schema_oxford.dmp content=all logfile=$LOGFILE schemas=oxford job_name=impdp_oxford remap_tablespace=oracle_tb:delta_tb

ET=`date`

echo "Start Time=$ST" >>$LOGFILE

echo "End Time=$ET" >>$LOGFILE

run your scripts in background using nohup  for example 

$ nohup ./impdp_oxford.sh & 

--Nikhil Tatineni--

--Datapump -- 

enable enable_goldengate_replication from 11.2.0.4

>> >>Scenario # Tried to enable supplemental logging at schema level on 11.2.0.4 database triggered with following error << << 

GGSCI (dg1.localdomain) 2> dblogin userid ggsuser password oracle

Successfully logged into database.

GGSCI (dg1.localdomain as ggsuser@DB11G) 3> add schematrandata oxford

ERROR: Operation not supported because enable_goldengate_replication is not set to true.

Resolution #  logged into the database, enabled parameter 

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

* Note > If you are upgrading database to 11.2.0.4. make sure you enable parameter  "enable_goldengate_replication=true"  to support goldengate replication 

>> >> again it's failed with error >

GGSCI (dg1.localdomain as ggsuser@DB11G) 3> add schematrandata oxford

2016-10-10 12:50:38  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema oxford because of the following SQL error: ORA-06550: line 1, column 7:

PLS-00201: identifier 'SYS.DBMS_CAPTURE_ADM' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('oxford','ALLKEYS_ON'); END;.

Root cause: ggsuser doesn't have enough privileges to run package 

Resolution: logged into the database, grant dba to ggsuser during installation  

sql> grant dba to ggsuser;

>>>>

Nikhil Tatineni--

Oracle DBA -- 

Query's to Monitor Standby Database

Query to check apply lag and transport lag 

Apply lag # is difference in elapsed time between last applied change become visible on standby and when change was first visible on primary 

Transport lag # Amount of data i.e standby has not  received from primary 

>>> >> view # v$dataguard_stats

select name,value,time_computed,datum_time from v$dataguard_stats where name=’%lag’;

Query to check errors triggered in alert log file and trace file

>>> >> view # v$dataguard_status 

select * from v$dataguard_status where 

severity in ('Error','Fatal') and 

timestamp > (sysdate -1);

Query to check Media Recovery Process is currently running

>>> >> view # v$managed_standby

select * from v$managed_standby where process like 'MRP%';

Query to verify logs that are shipped and applied to standby database 

>>> >> view # v$archived_log

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

Query to check missing archive logs i.e not shipped to standby 

SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

Query to Verify that the last sequence# received and the last sequence# applied to standby database

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"

from (select thread# thrd, max(sequence#) almax

      from v$archived_log

      where resetlogs_change#=(select resetlogs_change# from v$database)

      group by thread#) al,

     (select thread# thrd, max(sequence#) lhmax

      from v$log_history

      where first_time=(select max(first_time) from v$log_history)

      group by thread#) lh

where al.thrd = lh.thrd;

Query to check log file gap between primary and standby and dest status 

>> View v$Archive_Dest_status

select status, gap_status from v$Archive_Dest_status where dest_id = 2;

Query to check what are the archive logs missing on standby database 

>> View  v$Archive_gap 

select * from v$Archive_gap;

( if this query result's output > 

check missing exists on primary , 

manually copy it and 

register the logs 

and start MRP) 

--Nikhil Tatineni----Dataguard--

--More links --https://jhdba.wordpress.com/2009/07/16/managing-dataguard-monitoring-scripts/

Procedure to set old password

setting password > 

If database user 'oxford' or application user password is expired and he want's to use old password 

Procedure as follows 

SQL> select SPARE4 from sys.user$ where name='OXFORD';

SPARE4

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

S:75C6294AF9823AF42A8BC83BE2FB03A0727988159F34241B4211E71FC1AB

SQL> alter user oxford identified by values 'S:75C6294AF9823AF42A8BC83BE2FB03A0727988159F34241B4211E71FC1AB';

User altered.

SQL> conn oxford/oracle;

Connected.

--Nikhil Tatineni--

--Oracle--

oratop # DBA friendly

The “oratop” displays relevant database activity information presented in four sections. 

Section 1 - HEADER: Global database information ◦ 

Section 2 - DATABASS: Database instance Activity ◦ 

Section 3 - DB EVENTS: AWR like “Top 5 Timed Events“ ◦ 

Section 4 – PROCESS/SQL: Processes/SQL information Specific detail of the various sections data can be obtained using the interactive help (see Runtime usage Sections Detail below) 

[oracle@tnc2 ~]$ tfactl print status

.-------------------------------------------------------------------------------------------.

| Host | Status of TFA | PID  | Port | Version    | Build ID             | Inventory Status |

+------+---------------+------+------+------------+----------------------+------------------+

| tnc2 | RUNNING       | 4280 | 5000 | 12.1.2.8.0 | 12128020160623222219 | COMPLETE         |

'------+---------------+------+------+------------+----------------------+------------------'

[oracle@tnc2 ~]$ tfactl

tfactl> db12c                                                                                        

Usage : /u01/app/12.1.0.2/grid/bin/tfactl <command> [options]

<command> =

          print          Print requested details

          purge          Delete collections from TFA repository

          analyze        List events summary and search strings in alert logs.

          diagcollect    Collect logs from across nodes in cluster

          collection     Manage TFA collections

          directory      Add or Remove or Modify directory in TFA

          toolstatus     Prints the status of TFA Support Tools

          run <tool>     Run the desired support tool

          start <tool>   Starts the desired support tool

          stop <tool>    Stops the desired support tool

For help with a command: /u01/app/12.1.0.2/grid/bin/tfactl <command> -help

tfactl> db db12c                                                                                     

Set db to DB12C

DB12C tfactl> oratop -i 10 -bn10                                                                     

Cycle 1 - oratop: Release 14.1.2 Production on Wed Sep 14 11:00:16 2016

Oracle 12c - db1 11:00:08 up: 0.4h,   2 ins,    0 sn,   0 us, 1.8G mt,  1.9% db

ID %CPU LOAD %DCU   AAS  ASC  ASI  ASW  AST IOPS %FR   PGA UTPS UCPS SSRT  %DBT

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

 1   21    1    0     1    0    0    0    0    4   6  368M    0    2 975u  84.3

 2   11    1    0     1    0    0    0    0    3   4  323M    0    2 462u  15.7

EVENT (C)                        TOT WAITS   TIME(s)  AVG_MS  PCT    WAIT_CLASS

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

PX Deq: Slave Session Stats           2760         453   179.6   36           Other

PX Deq: reap credit                       47402       320     6.7     25         Other

DB CPU                                             188        15              

oracle thread bootstrap                     319       183   580.5   15        Other

reliable message                               890       118   168.9    9          Other

ID   SID     SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T

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

From 

Section 4 – PROCESS/SQL: Processes/SQL information Specific detail of the various sections data 

we will get SQL_ID involved in waits 

check execution plan of SQL statement using dbms_xplan.display_cursor & SQL_TEXT as follows

sql> select * from table(dbms_xplan.display_cursor(‘&sql_id’,’&child_no’,’typical’));

SQL> col SQL_TEXT format a10000
SQL> select SQL_TEXT from v$sqltext where SQL_ID='worldoforacle';

--Nikhil Tatineni--

--RAC--