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

Advertisements

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