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