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

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

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

ORA-17628: Oracle error 19505 returned by remote Oracle server

I have colleague of mine handed over a failed RMAN duplication issue…
after fixing the issue i started again the active duplicate.

but when its almost done… i got below error

 
input datafile file number=00023 name=+DATA/cxxxxsit/datafile/oxxx_data.357.873341887
RMAN-03009: failure of backup command on c8 channel at 03/25/2016 06:57:03
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run

checking the alert log… ASM was exhausted. seems the old files from previous failed Active Duplication was not remove.
my mistake.. 😦

lessons learned… before doing any activity.. do the pre checks… πŸ™‚
and below note is very helpful.

 
Known issues on ORA-17628: Oracle error 19505 returned by remote Oracle server Reported in Rman Active Duplicate (Doc ID 1401333.1)

Thanks!

Transportable Tablespace on 11g ASM to ASM

My current working environment always do the data refresh from production and they are using datapump to move the data from production to SIT/UAT and Pre Prod.

some environment is small so using datapump is an option but some is quite big ~2TB. when doing the import, it usually takes 12hours to do the import. So my task is to look for another way to make it faster. I decided to use TTS.

Once the export and scp to target database is completed. it only took 90mins to import the new tablespaces and move the datafiles to ASM.

The only disadvantage of doing this is that i need more space for both and target.

Next i will do a test in 12c which is much better than 11g.

1. Check if some limitation don’t allow to export the source schema

    SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('INDEX_TBS,DATA_TBS01,DATA_TBS02', TRUE);

    PL/SQL procedure successfully completed.

    SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

    VIOLATIONS
    --------------------------------------------------------------------------------
    ORA-39908: Index DDL_AUTOINSTALL.META_QUEUE_AK1 in tablespace DBASPACE enforces
    primary constraints  of table XXX_RGN_RT.META_QUEUE in tablespace DATA_TBS02.


    SQL> alter index DDL_AUTOINSTALL.META_QUEUE_AK1 rebuild tablespace DATA_TBS02 online;

    Index altered.

    SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('INDEX_TBS,DATA_TBS01,DATA_TBS02', TRUE);

    PL/SQL procedure successfully completed.

    SQL>  SELECT * FROM TRANSPORT_SET_VIOLATIONS;

    no rows selected

    SQL>

Continue reading

RMAN Restore from Tape to Diff Host DBName on ASM

A little bit busy this week … my boss ask me to validate the RMAN tape backup if we can really restore it.

Note : restore database validate is not an option πŸ™‚

Source and target are both clustered database.

Source DB: CNCXXU
Source Server : ncxxdsmwu01/ncxxdsmwu02

Target DB: NCXX2PT
Target Server : ncxxdsgtu21/ncxxdsgtu22

1. Shutdown the target database and delete all files in ASM
2. Start the database in nomount and restore the spfile.
– spfile and controlfile backup is going to disk.
– no rman catalog
Continue reading

PRCR-1065 : Failed to stop resource ora.asm

If you try to stop the ASM using srvctl without the -f option most likely you get the same error.

or maybe you can stop it using sqlplus, but i like using srvctl specially if its clustered.

$ srvctl stop asm

ERROR :

PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.DATA.dg', but the force option was not specified

Continue reading

How to change DBID in RAC

recently i restore a database from tape to a different host and at the same time i need to rename the database.

the dbid of my new database same from my source database. since we are using tape to backup our database i need to change the DB of our new database.

Note : DB is 2 node cluster.

Disable the cluster parameter and restart the database.
Continue reading