Pages

Thursday, October 13, 2016

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 

Adding a disk to diskgroup > ASM

Wednesday, October 12, 2016

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

Monday, October 10, 2016

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) 

Saturday, October 8, 2016

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