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

Advertisements

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!

ORA-00245: control file backup failed; target is likely on a local file system

Basically one day at work, my colleague handed over an issue related to RMAN.

 
Starting backup at 14-FEB-2016 06:02:44
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1444 instance=CXXXEU22 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/14/2016 06:02:46
ORA-00245: control file backup failed; target is likely on a local file system
 Continue reading 

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

RMAN-20020: database incarnation not set

usually it happens when you do alter database open resetlogs..

RMAN> resync catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 04/04/2014 05:25:29
RMAN-20020: database incarnation not set

RMAN> reset database;

database incarnation already registered
starting full resync of recovery catalog
full resync complete

RMAN>

Resync Physical standby using RMAN Incremental backup

Just another day at the office, our production database has 2 physical standby and it lagging for more that 1000 archives.
the reason is the password was changed and the password file was not copied to standby.

below are the steps i did to resync the standby. for the first standby i tried to restore archive but its taking time .

NOTE:
– Primay and Standby filesystem is the same.
– Shared NFS is mounted on both server.

Continue reading

RMAN-05533: LIST FAILURE is not supported on RAC database

i’m trying to recover a datafile with our production database. 3 node cluster database so i tried to use the new feature of RMAN repair command.

unfortunately encountered an error : “…is not supported on RAC database

for the example below i’m using my test environment.

[oracle@lnxrac01 bin]$ ./srvctl status database -d bairac1
Instance bairac11 is running on node lnxrac01
Instance bairac12 is running on node lnxrac02

 

SQL> set lines 150;
SQL> select file_name, file_id, online_status from dba_data_files;

FILE_NAME                               FILE_ID         ONLINE_
———————————————————————————————————————————-
+DATA01/bairac1/datafile/system.274.726941717         1             SYSTEM
+DATA01/bairac1/datafile/sysaux.275.726941737         2            RECOVER
+DATA01/bairac1/datafile/undotbs1.276.726941773     3             ONLINE
+DATA01/bairac1/datafile/undotbs2.278.726941805     4             ONLINE
+DATA01/bairac1/datafile/users.279.726941825        5             ONLINE

Recovery Manager complete.
[oracle@lnxrac01 bin]$ ./rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 26 02:05:50 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: BAIRAC1 (DBID=749250110)

RMAN> list failure;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/26/2011 02:05:57
RMAN-05533: LIST FAILURE is not supported on RAC database

RMAN> exit

with the above error means we cannot use the new features of RMAN. so we need to do the old fashion way.


[oracle@lnxrac01 bin]$ ./rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 26 02:15:32 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BAIRAC1 (DBID=749250110)

RMAN> restore datafile 2;

Starting restore at 26-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 instance=bairac11 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +DATA01/bairac1/datafile/sysaux.275.726941737
channel ORA_DISK_1: reading from backup piece /backup/backup_BAIRAC1_S_4_P_1_T_757476175
channel ORA_DISK_1: piece handle=/backup/backup_BAIRAC1_S_4_P_1_T_757476175 tag=TAG20110726T020255
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 26-JUL-11

RMAN> recover datafile 2;

Starting recover at 26-JUL-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 26-JUL-11

RMAN> sql ‘alter database datafile 2 online’;

sql statement: alter database datafile 2 online

RMAN> exit

check the new status of datafile.

SQL> set lines 150;
SQL> select file_name, file_id, online_status from dba_data_files;

FILE_NAME                               FILE_ID         ONLINE_
———————————————————————————————————————————-
+DATA01/bairac1/datafile/system.274.726941717         1             SYSTEM
+DATA01/bairac1/datafile/sysaux.275.726941737         2            ONLINE
+DATA01/bairac1/datafile/undotbs1.276.726941773     3             ONLINE
+DATA01/bairac1/datafile/undotbs2.278.726941805     4             ONLINE
+DATA01/bairac1/datafile/users.279.726941825        5             ONLINE

Thanks!

bai