How to Move datafile from ASM to ASM

Today got archiver error issue due to our FRA is full. reason for that is some datafile are created inside the +FRA.
My Task now is to move the datafiles from +FRA to +DATA since we already increased it.

Got permission from application to take below datafiles offline for a short period of time.

 
   FILE_ID TABLESPACE_NAME                FILE_NAME
---------- ------------------------------ --------------------------------------------------
       151 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1410.908245675
       152 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1451.908245731
       153 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1487.908245783
       154 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.380.908245805
       102 OPDB_DATA                      +FRA/xxxclpt2/datafile/opdb_data.1938.911645643
       155 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.2026.916987355
       156 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1457.916987395
       157 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1363.916987403
       158 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1862.916987409
       159 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.2063.916987425
       160 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.1822.916987437
       161 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.2077.916987509
       162 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.1848.916987523
       163 INDEX_KYC                      +FRA/xxxclpt2/datafile/index_kyc.1900.916987543
       164 INDEX_KYC                      +FRA/xxxclpt2/datafile/index_kyc.2035.916987549

15 rows selected.

Continue reading

ORA-17502: ksfdcre:4 Failed to create file +FRA

A weekend is about the start and got a call and one of our database is having issue. User is unable to connect and all their activity seems hung.

ISSUE : Instance Terminated and make Node2 inaccessible due to Oracle Sees FRA is Full.
But FRA is not Full ~ 2TB is available and db_recovery_file_dest_size=2.7TB. +FRA is ~18% Utilized only.

What i Did? Drop all existing restore point and restarted all node.

So i check the alert log on node 1 database.

 
*************************************
RVWR encountered an error when writing flashback database logs.
See error stack in alert log.
Since a Guarantee Restore Point was created,
RVWR cannot force to turn off flashback.
Therefore RVWR will terminate this instance.
*************************************
Fri Sep 16 05:17:28 2016
Errors in file /optware/orabase/grid/diag/rdbms/ckyclu3/CXXXLU31/trace/CXXXLU31_rvwr_41392.trc:
ORA-38701: Flashback database log 9 seq 5 thread 1: "+FRA"
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
USER (ospid: 41392): terminating the instance due to error 38701
Fri Sep 16 05:17:28 2016
System state dump requested by (instance=1, osid=41392 (RVWR)), summary=[abnormal instance termination].
System State dumped to trace file /optware/orabase/grid/diag/rdbms/ckyclu3/CXXXLU31/trace/CXXXLU31_diag_50122_20160916051728.trc
Fri Sep 16 05:17:30 2016
License high water mark = 81
Fri Sep 16 05:17:33 2016
Instance terminated by USER, pid = 41392
Fri Sep 16 05:17:34 2016
USER (ospid: 1359): terminating the instance
Fri Sep 16 05:17:34 2016
Instance terminated by USER, pid = 1359
Fri Sep 16 05:17:35 2016 

Continue reading

ERROR OGG-01028 Detect inconsistency in pdata after 6 retries

Last weekend i was alerted of gg abend with error gode ogg-01028.

Been working with oracle gg for almost 6 years but this the first i encounter such issue.

  ***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2016-09-04 00:19:15  WARNING OGG-01027  Detect inconsistency in pdata after 0 retries, expecting tb 1048576, nf 20, got tb 196863, nf 255, bc 1572884, nb 1277498645, on record with seqno 9759
, rba 119140084 for data starts at rba 32480300.

Source Context :
  SourceModule            : [er.redo.ora.rtc]
  SourceID                : [/scratch/aime1/adestore/views/aime1_staxj16/oggcore/OpenSys/src/app/er/redo/oracle/rtc.c]
  SourceFunction          : [RTC_producer(void *)]
  SourceLine              : [2118]

2016-09-04 00:19:44  ERROR   OGG-01028  Detect inconsistency in pdata after 6 retries, expecting tb 1048576, nf 20, got tb 196863, nf 255, bc 1572884, nb 1277498645, on record with seqno 9759
, rba 119140084 for data starts at rba 32480300.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Continue reading

After RMAN Cloning, GG Manager abends on ORA-12154

Recently we refresh our Prodtest database from production using RMAN. now while starting the GG, i’m getting below error.

2016-05-27 19:31:12  ERROR   OGG-01668  Oracle GoldenGate Manager for Oracle, mgr.prm:  PROCESS ABENDING.
2016-05-27 19:49:26  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2016-05-27 19:49:27  ERROR   OGG-00664  Oracle GoldenGate Manager for Oracle, mgr.prm:  OCI Error during OCIServerAttach (status = 12154-ORA-12154: TNS:could not resolve the connect identifier specified).
2016-05-27 19:49:27  ERROR   OGG-01668  Oracle GoldenGate Manager for Oracle, mgr.prm:  PROCESS ABENDING.
bash-4.1$

Continue reading

ORA-06512: at line 1 SQL BEGIN sys.dbms_xstream_gg.SET_FOO_TRIGGER_SESSION_CONTXT (fire=>TRUE); END;.

Just another day n the office where our Development was refresh from production.

Development DB is configured to have GG multi bidirectional replication.
I started the Manager, extract and pump without any issue but when starting the replicat i’m getting below error.

2016-05-09 13:34:41  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, ckn2ure2.prm:  PROCESS ABENDING.
2016-05-09 13:40:18  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start CKL2BRE2.
2016-05-09 13:40:18  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host gcxxxx2s:37774 (START REPLICAT CKL2BRE2 ).
2016-05-09 13:40:18  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT CKL2BRE2 starting.
2016-05-09 13:40:19  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, ckl2bre2.prm:  REPLICAT CKL2BRE2 starting.
2016-05-09 13:40:19  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, ckl2bre2.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2016-05-09 13:40:19  ERROR   OGG-00868  Oracle GoldenGate Delivery for Oracle, ckl2bre2.prm:  ORA-26944: User "GGS_ADMIN" attempted to invoke a procedure without proper permissions.
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 197
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 230
ORA-06512: at "SYS.DBMS_XSTREAM_GG", line 9
ORA-06512: at line 1 SQL BEGIN sys.dbms_xstream_gg.SET_FOO_TRIGGER_SESSION_CONTXT (fire=>TRUE); END;.
2016-05-09 13:40:19  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, ckl2bre2.prm:  PROCESS ABENDING.
oracle@gcxxxx2s[CXXXESIT2] /gg111/112/11.2/11g =>

Continue reading

Tracing with dbms_monitor

I know that dbms_monitor is available since 10g but most of the time we forgot about it. or we are using the older method.

but dbms_monitor is really easy to compared to the old method of tracing.

i’ts sunday today and i’m working (just setting in the office waiting for an issue 😦 )

i’ll just try to trace my session using dbms_monitor.

+------------------------------------------------------------------------+
| Report   : Active User Sessions (All)                                  |
| Instance :  XXXXDIT3                                                    |
+------------------------------------------------------------------------+

Instance       SID Serial ID Status    Oracle User        O/S User           O/S PID  Terminal   Machine                        Session Program
---------- ------- --------- --------- ------------------ ------------------ -------- ---------- ------------------------------ ----------------------------------------
 XXXXDIT3      1251     13207 ACTIVE    GGS_ADMIN          xxxx504            23658    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx           sqlplus.exe

SQL> 

i’ll be tracing the above session. Continue reading

No valid default archive log destination directory found for thread 2

Recently we refresh our dev database from prod using rman cloning, totaling of 4 database representing 4 regions.

Oracle GG is use to replicate the data, multi bidirectional replication.

after the rman cloning, i started the GG using begin now but i’m getting below error with our main extract, pump and replicat is working fine.

2016-04-10 11:27:03  WARNING OGG-01423  Oracle GoldenGate Capture for Oracle, cknm2bet.prm:  No valid default archive log destination directory found for thread 2.
2016-04-10 11:27:03  INFO    OGG-00546  Oracle GoldenGate Capture for Oracle, cknm2bet.prm:  Default thread stack size: 10485760.
2016-04-10 11:27:03  ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, cknm2bet.prm:  The number of Oracle redo threads (2) is not the same as the number of checkpoint threads (1). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT , TRANLOG, THREADS 2, BEGIN...).
2016-04-10 11:27:03  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, cknm2bet.prm:  PROCESS ABENDING.

Continue reading