Tuesday, December 12, 2017

acfsutil size: ACFS-03006: smallest size, without loss of data



oradev01:NOTSET:/opt/oracle $ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbSys1
                                           30G   24G  4.2G  86% /
tmpfs                 253G  124M  253G   1%    /dev/shm
/dev/sda1             504M   42M  437M   9% /boot
/dev/mapper/VGExaDb-LVDbOra1
                                          198G  152G   37G  81% /u01
/dev/asm/goldengate-303
                                         500G   46G  455G  10% /goldengate
/dev/asm/gg_vol-303    20G   19G  1G 99% /u01/app/oracle/acfsmount/gg_vol
/dev/asm/acfs_expdir-303
                                            2.0T  1.2T  920G  56% /export

Set env to +ASM1 and login to sysasm and check with below sql

SQL> select FS_NAME, VOL_DEVICE, TOTAL_MB , FREE_MB from V$ASM_ACFSVOLUMES;

FS_NAME                           VOL_DEVICE                          TOTAL_MB    FREE_MB
--------------------------------- --------------------------------- ------------------ ----------
/goldengate                       /dev/asm/goldengate-303               512000   46072.625
/u01/app/oracle/acfsmount/gg_vol  /dev/asm/gg_vol-303                    20480    2.19140625
/export                           /dev/asm/acfs_expdir-303             2097152    938916.852

Now, Trying to resize ACFS (removing 500G from 2TB)

oradev01:NOTSET:/opt/oracle $ /sbin/acfsutil size -500G -d  /dev/asm/acfs_expdir-303   /export
acfsutil size: ACFS-03006: smallest size, without loss of data, is: 2193043226624 (2091449MB)


oradev01:NOTSET:/opt/oracle $ acfsutil version
acfsutil version: 12.1.0.2.0


SOLUTION:

As per Oracle DOC ID 1597710.1 it is not possible to resize ACFS so the work around is to delete the filesystem and create with the required storage

Ref more:
ACFSUTIL resize operation fails with "ACFS-03006: smallest size" (Doc ID 1597710.1)


Saturday, November 18, 2017

Shell script to cleanup Goldengate PROD extracts in NON-PROD


PRODUCTION DB: Oracle on linux with Goldengate extracts ( ex: ETESTDBQ2 ) running
LINUX: RHEL6

The below script used for cleanup non-prod databases where goldengate is running when it is refreshed (FULL refresh like SNAP copy, rman clone, any physical clone) from PROD databases where goldengate also running, which means when you refresh non-prod with prod database then you will see goldengate prod extract name in non-prod which will repeat errors in alert log and restricts archivelog from deleting.

You’ll see something like below in non-prod with prod extract group names which needs cleanup

SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME
--------------------------------------------------------------------------------
STATUS   CAPTURED_SCN  APPLIED_SCN   CAPTURE_TY   STATUS_CHAN
------------- -----------------  -------------------    -------------------  --------------
OGG$CAP_ETESTDBQ2
ENABLED    3.5062E+11  3.5062E+11 LOCAL      13-NOV-2017


#!/bin/ksh
####################################################################################
# This script will do the POST refresh activities for Databases having Goldengate
# It will uses the directory defined in the EXPDPDR directory
#
# Syntax:  Script.sh <database_name>
#
# PARAMETERS:
#     Source dbname    : (required) - database name
#
# EX: GG_POST.sh mydev
#
# AUTHOR: Chandra
####################################################################################
echo -e "\n*************************************"
echo  "DATE = `date`"
echo  "HOSTNAME = " `hostname`
echo -e "*************************************\n"

if [ "$1" = "" ]; then
  echo " "
  echo "Syntax: $0 <DB name>"
  exit 1
fi

DBTGT=`echo $1 | tr "[:upper:]" "[:lower:]"`
DBTAIL=`echo $DBTGT |awk '{print substr($0,3)}'`
SOURCE_SERVER=`hostname`
#echo "DBTAIL :" $DBTAIL
#echo "DBTGT : " $DBTGT

if [[ "$DBTAIL" = "prod"]]; then
echo -e "\nThis script CANNOT be run on $DBTGT PRODUCTION databases \n"
exit 1
fi

ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=$DBTGT
#. oraenv $ORACLE_SID
if [[ "${SOURCE_SERVER:0:3}" = "rac" ]] || [[ "${SOURCE_SERVER:0:3}" = "RAC" ]]; then
. /opt/oracle/RAC/oraenv
else
. /usr/local/bin/oraenv
fi

echo "ORACLE_HOME=" $ORACLE_HOME

CAP_NAME=`sqlplus -s /nolog << EOF
connect / as sysdba
set verify off trimspool on feed off echo off termout off head off
col capture_name for a15
col STATE for a33
select capture_name from dba_capture where capture_name like '%PROD%';
exit
EOF
`

CAP_NEW_NAME=$CAP_NAME
echo -e "\n CAP_NEW_NAME = " $CAP_NEW_NAME
C_ENV=`echo $CAP_NAME |awk '{print substr($0,9,14)}'`
echo " CAP_NAME= $CAP_NAME"
echo " C_ENV" =$C_ENV

stop_cap () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG\$CAP_${C_ENV}');
EOF
}

drop_cap () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
exec DBMS_CAPTURE_ADM.DROP_CAPTURE('OGG\$CAP_${C_ENV}');
EOF
}

CHECK_CAP () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
set line 280 pages 999
col CAPTURE_NAME for a22
col STATE for a25
select capture_name, status, captured_scn, applied_scn, capture_type from dba_capture;
select extract_name,CAPTURE_NAME, sid, serial#, STATE from  V\$GOLDENGATE_CAPTURE;
EOF
}


if [[ "$C_ENV" == "ESTPRODP" ]] || [[ "$C_ENV" == "EDVPRODP" ]] ; then
echo -e "\n*****************************************************"
echo -e "Database ${1} has PROD "${CAP_NEW_NAME}" capture name "
echo -e "*************************************************** \n"
echo -e " BEFORE Dropping :"
CHECK_CAP ;
echo -e " \nStopping "${CAP_NEW_NAME}" Capture process "
stop_cap;
echo -e "\nDROPPING "${CAP_NEW_NAME}" Capture process "
drop_cap;
echo -e " AFTER Dropping :"
CHECK_CAP ;
else
echo -e "\n*********************************************************"
echo -e "NOT found PROD extract process name in $1 database "
echo -e "*********************************************************\n"
CHECK_CAP ;
fi

echo -e "\nDATE = `date`"


Another option is to login to ggsci and do unregister, check below link to unregister manually

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process


When trying to delete Database archivelog where goldengate in running using rman i see below Warning  message.

RMAN> Delete archivelog all completed before  'SYSDATE-3';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2907 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3435.12440.956737243 thread=1 sequence=3435
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3436.12438.956746549 thread=1 sequence=3436
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3437.12437.956746819 thread=1 sequence=3437
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3438.12436.956746869 thread=1 sequence=3438


SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME                                                                                                                     STATUS   CAPTURED_SCN    APPLIED_SCN   CAPTURE_TY   STATUS_CHAN
------------------------------------------------------------------------------------------------------------------- -------- ------------ 
 ----------- ---------- -----------
OGG$CAP_ETESTDB                                                                                                                   ENABLED    3.3555E+11  3.3555E+11 LOCAL      27-JUN-2017

OGG$CAP_ETESTDBQ2                                                                                                                 ENABLED    3.5047E+11  3.5047E+11 LOCAL      13-NOV-2017

I see one of the OLD Extract process(ETESTDB) is registered and blocking archivelog deletion using RMAN which is not used any more so I need to delete that to resolve the issue


SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG$CAP_ETESTDB');
  
^CBEGIN DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG$CAP_ETESTDB'); END;

*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 1310
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 90
ORA-06512: at line 1


Hanging here ..............so killed


GGSCI (ORACLEDEV01) 2> dblogin useridalias gguser_dev
ERROR: Unable to connect to database using user GGUSER. Please check privileges.
Unable to initialize database connection because of error ORA-00257: Archiver error. Connect AS SYSDBA only until resolved..

GGSCI (ORACLEDEV01) 3> dblogin useridalias gguser_dev
Successfully logged into database.

GGSCI (ORACLEDEV01 as GGUSER@TESTDB) 4> unregister extract ETESTDB database

2017-11-14 11:14:21  INFO    OGG-01750  Successfully unregistered EXTRACT ETESTDB from database.

GGSCI (ORACLEDEV01 as GGUSER@TESTDB) 5> exit
oracledev01:TESTDB:/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 14 11:14:33 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME
--------------------------------------------------------------------------------
STATUS   CAPTURED_SCN APPLIED_SCN CAPTURE_TY STATUS_CHAN
-------- ------------ ----------- ---------- -----------
OGG$CAP_ETESTDBQ2
ENABLED    3.5062E+11  3.5062E+11 LOCAL      13-NOV-2017



Refer Oracle Doc:
RMAN-08137: WARNING: Archived Log Not Deleted, Needed For Standby Or Upstream Capture Proces (Doc ID 1993799.1)


Friday, October 6, 2017

ORA-29701: unable to connect to Cluster Synchronization Service during ASM startup

ORACLE  Version : 12.2 standalone with ASM

Trying to start ASM instance after server reboot and it is failing with error
    
oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 6 16:19:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL> exit
Disconnected

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.asm
               OFFLINE OFFLINE      oradev.domain.com                   Instance Shutdown,STABLE
ora.ons
               OFFLINE OFFLINE      oradev.domain.com                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       oradev.domain.com                   STABLE

                 


oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl start resource ora.cssd
CRS-4995:  The command 'Start  resource' is invalid in crsctl. Use srvctl for this command

oradev.domain.com:NOTSET:/opt/oracle/12.1.0/grid/bin $ ps -ef | grep cssd.bin
oracle   12249     1  0 16:21 ?        00:00:26 /opt/oracle/12.1.0/grid/bin/ocssd.bin
oracle   21138 20798  0 21:49 pts/0    00:00:00 grep cssd.bin

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl start resource ora.cssd -unsupported
CRS-2672: Attempting to start 'ora.cssd' on 'oradev.domain.com'
CRS-2672: Attempting to start 'ora.diskmon' on 'oradev.domain.com'
CRS-2676: Start of 'ora.diskmon' on 'oradev.domain.com' succeeded
CRS-2676: Start of 'ora.cssd' on 'oradev.domain.com' succeeded


oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.asm
               OFFLINE OFFLINE      oradev.domain.com                   Instance Shutdown,STABLE
ora.ons
               OFFLINE OFFLINE      oradev.domain.com                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       oradev.domain.com                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       oradev.domain.com                   STABLE

                 
                 
oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 6 16:23:58 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size            2111002744 bytes
ASM Cache                  33554432 bytes
ASM diskgroups mounted


ASM successfully started …………………

Here "AUTO_START" is set to "NEVER" and that is the reason for not starting after reboot but we can change to "ALWAYS" to avoid this Manual intervention after every reboot

$ ./crsctl stat res ora.cssd -p -init | grep AUTO_START
AUTO_START=never

Refer:
ASM Instance doesn't start in Oracle Restart (Standalone GI) environment after node reboot (Doc ID 1917176.1)

Friday, September 22, 2017

Goldengate OGG-03533 Conversion from character set



Oracle DB : 12.1.0.2
OGG : 12.2

My replicat ABEBDED with below error and I did research in Oracle support and internet but couldn’t able to find the exact solution so after spending lot of time troubleshooting finally I got a solution and I want to share it with you today

Error from replicat report file or ggserr.log:

2017-09-22 14:53:27  ERROR   OGG-03533  Conversion from character set us7ascii of source column DESCRIPTION to character set UTF-8 of target column DESCRIPTION failed because the source column contains a character 'a0' at offset 21 that is not available in the target character set.

Solution:

Add “REPLACEBADCHAR SPACE” parameter in replicat
START replicat
Once Issue resolved Remove the parameter from the replicat


Sunday, August 6, 2017

Deinstall 12c Goldengate


GG Version : 12.2.0.1.1
DB version: 12.1.0.2.0
Linux Version : RHEL 6

Before Deinstalling goldengate from your server first stop all Extract/Replicat/manager processes

oracledev01:/goldengate/home $ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.


GGSCI (oracledev01) 1> stop ER *

GGSCI (oracledev01) 2> stop mgr

Make sure all stopped gracefully and proceed to below

Go to $GG_HOME/deinstall
oracledev01:NOTSET:/goldengate/home/deinstall $ ll
total 4
-r-xr-xr-x 1 oracle oinstall 1298 Jul  2  2013 deinstall.sh


oracledev01:NOTSET:/goldengate/home/deinstall $ ./deinstall.sh

ALERT: Ensure all the processes running from the current Oracle Home are shutdown prior to running this software uninstallation script.

Proceed with removing Oracle GoldenGate home: /goldengate/home (yes/no)? [no]
yes
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 24533 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-07-18_07-01-08PM. Please wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Tuesday, July 18, 2017 7:01:14 PM EDT)
............................................................... 100% Done.

Deinstall successful

End of install phases.(Tuesday, July 18, 2017 7:01:35 PM EDT)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2017-07-18_07-01-08PM.log' for more details.


Optionally, if you want you can drop Goldengate database user


Friday, June 30, 2017

Convert Oracle 12c database from Non-ASM to ASM (standalone)

Now from 12c database you can move/relocate datafiles ONLINE menaing users can still able to access the database and objects while the move/relocate is in process, prior to 12c it was not the case

According to oracle
“When you rename or relocate a data file with ALTER DATABASE MOVE DATAFILE statement, Oracle Database creates a copy of the data file when it is performing the operation. Ensure that there is adequate disk space for the original data file and the copy during the operation
When you run the ALTER DATABASE MOVE DATAFILE statement and a file with the same name exists in the destination location, you can specify the REUSE option to overwrite the existing file. When REUSE is not specified, and a file with the same name exists in the destination location, the existing file is not overwritten, and the statement returns an error.
By default, when you run the ALTER DATABASE MOVE DATAFILE statement and specify a new location for a data file, the statement moves the data file. However, you can specify the KEEP option to retain the data file in the old location and copy it to the new location. In this case, the database only uses the data file in the new location when the statement completes successfully.



oracleprod01:NOTSET:/home/oracle $ . dbaenv
ORACLE_SID = [NOTSET] ? PERFDB
The Oracle base remains unchanged with value /opt/oracle
oracleprod01:PERFDB:/home/oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 28 20:45:03 2017

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

connected to target database: PERFDB (DBID=2123458733)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PERFDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1024     SYSTEM               YES     /u01/PERFDB/u01/oradata/PERFDB/system01.dbf
2    1114     SYSAUX               NO      /u01/PERFDB/u02/oradata/PERFDB/sysaux01.dbf
3    31974    UNDOTBS1             YES     /u01/PERFDB/u04/oradata/PERFDB/undotbs01.dbf
4    50       USERS                NO      /u01/PERFDB/u03/oradata/PERFDB/users01.dbf
5    100      STAGING_TS           NO      /u01/PERFDB/u01/oradata/PERFDB/staging_01.dbf

……………..Trimmed …………….


186  100      COMP_INDEX         NO      /u01/PERFDB/u02/oradata/PERFDB/COMP_INDEX_05.dbf
187  100      COMP_INDEX         NO      /u01/PERFDB/u01/oradata/PERFDB/COMP_INDEX_04.dbf
188  100      COMP_INDEX         NO      /u01/PERFDB/u03/oradata/PERFDB/COMP_INDEX_03.dbf
189  100      COMP_INDEX         NO      /u01/PERFDB/u01/oradata/PERFDB/COMP_INDEX_02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    1177     TEMP                 32767       /u01/PERFDB/u03/oradata/PERFDB/temp01.dbf
2    2554     TEMP                 32767       /u01/PERFDB/u03/oradata/PERFDB/temp02.dbf
3    2554     TEMP                 32767       /u01/PERFDB/u03/oradata/PERFDB/temp03.dbf

***********Trimmed ********

29   2554     TEMP                 32767       /u01/PERFDB/u04/oradata/PERFDB/temp29.dbf
30   2554     TEMP                 32767       /u01/PERFDB/u04/oradata/PERFDB/temp30.dbf
31   2554     TEMP                 32767       /u01/PERFDB/u04/oradata/PERFDB/temp31.dbf
32   2554     TEMP                 32767       /u01/PERFDB/u04/oradata/PERFDB/temp32.dbf

RMAN> exit

Create SCRIPTS rename datafile scripts and execute

SQL> set heading off pages 999 line 280

SQL> spool move_dbfiles.sql
SQL> select 'ALTER DATABASE MOVE DATAFILE '''||name||''' TO ''+DATA';' from v$datafile order by con_id;

SQL> @move_dbfiles.sql

Before Running drop Temp tablespace create one temp tablespace in ASM

SQL> alter tablespace TEMP add tempfile '+DATA;

Tablespace altered.

SQL> spool DROP_tempfiles.sql
SQL> select 'ALTER TABLESPACE TEMP drop TEMPFILE '''||name||''';' from v$tempfile order by con_id;

SQL> @DROP_tempfiles.sql
ALTER TABLESPACE TEMP drop TEMPFILE '/u01/PERFDB/u03/oradata/PERFDB/temp01.dbf'
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time



Tablespace altered.


Tablespace altered.

ALTER TABLESPACE TEMP drop TEMPFILE '/u01/PERFDB/u04/oradata/PERFDB/temp28.dbf'
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

You might see some error while dropping temp tablespace its because they might be in use but ignore and proceed

oracleprod01:PERFDB:/home/oracle/CB $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 28 22:56:55 2017

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

connected to target database: PERFDB (DBID=29412321234)

RMAN>  report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PERFDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1024     SYSTEM               YES     +DATA/PERFDB/DATAFILE/system.256.947887307
2    1114     SYSAUX               NO      +DATA/PERFDB/DATAFILE/sysaux.257.947887315
3    31974    UNDOTBS1             YES     +DATA/PERFDB/DATAFILE/undotbs1.258.947887327
4    50       USERS                NO      +DATA/PERFDB/DATAFILE/users.259.947887443


********   TRIMMED  ********

186  100      COMP_INDEX         NO      +DATA/PERFDB/DATAFILE/COMP_INDEX.441.947889881
187  100      COMP_INDEX         NO      +DATA/PERFDB/DATAFILE/COMP_INDEX.442.947889883
188  100      COMP_INDEX         NO      +DATA/PERFDB/DATAFILE/COMP_INDEX.443.947889887
189  100      COMP_INDEX         NO      +DATA/PERFDB/DATAFILE/COMP_INDEX.444.947889889

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    1024     TEMP                 32767       /u01/PERFDB/u03/oradata/PERFDB/temp01.dbf
2    100      TEMP                 32767       +DATA/PERFDB/TEMPFILE/temp.446.947890487
28   1024     TEMP                 32767       /u01/PERFDB/u04/oradata/PERFDB/temp28.dbf
33   100      TEMP                 32767       +DATA/PERFDB/TEMPFILE/temp.445.947890197

RMAN> select member from v$logfile;

MEMBER
---------------------------------------------------------
/u01/PERFDB/u70/redo/redo01a.log
/u01/PERFDB/u72/redo/redo02b.log
/u01/PERFDB/u70/redo/redo02a.log
/u01/PERFDB/u72/redo/redo01b.log
/u01/PERFDB/u70/redo/redo03a.log
/u01/PERFDB/u72/redo/redo03b.log

RMAN>  select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 CURRENT
         5 INACTIVE
         6 INACTIVE

RMAN> alter database drop logfile group 1;

Statement processed

RMAN> alter database add logfile group 1 ('+DATA,'+RECO') size 200M;

Statement processed

RMAN> alter database drop logfile group 2;

Statement processed

RMAN> alter database drop logfile group 3 ;

Statement processed

RMAN> alter database add logfile group 2 ('+DATA,'+RECO') size 200M;

Statement processed

RMAN> alter database add logfile group 3 ('+DATA,'+RECO') size 200M;

Statement processed

RMAN>  select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 UNUSED
         3 UNUSED
         4 CURRENT
         5 INACTIVE
         6 INACTIVE

RMAN> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/PERFDB/ONLINELOG/group_1.447.947890693
+RECO/PERFDB/ONLINELOG/group_1.447.947890693
+DATA/PERFDB/ONLINELOG/group_2.448.947890837
+RECO/PERFDB/ONLINELOG/group_2.448.947890837
+DATA/PERFDB/ONLINELOG/group_3.256.947890863
+RECO/PERFDB/ONLINELOG/group_3.256.947890863
/u01/PERFDB/u72/redo/redo01b.log
/u01/PERFDB/u70/redo/redo03a.log
/u01/PERFDB/u72/redo/redo03b.log


RMAN> alter system switch logfile;

Statement processed

RMAN>  select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED
         4 ACTIVE
         5 INACTIVE
         6 INACTIVE

RMAN> alter system checkpoint;

Statement processed

RMAN>  select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED
         4 INACTIVE
         5 INACTIVE
         6 INACTIVE


RMAN> alter database drop logfile group 4;

Statement processed

RMAN> alter database drop logfile group 5;

Statement processed

RMAN> alter database drop logfile group 6;

Statement processed

ADD remaining logfiles……………

RMAN>


RMAN> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+RECO/PERFDB/ONLINELOG/group_1.260.947891785
+RECO/PERFDB/ONLINELOG/group_2.261.947891801
+RECO/PERFDB/ONLINELOG/group_3.256.947890863
+RECO/PERFDB/ONLINELOG/group_4.257.947891047
+RECO/PERFDB/ONLINELOG/group_5.258.947891057
+RECO/PERFDB/ONLINELOG/group_6.259.947891065


RMAN> exit


Recovery Manager complete.
oracleprod01:PERFDB:/home/oracle/CB $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 28 23:19:22 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> sho parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     10
control_files                        string      /u01/PERFDB/u02/oradata/PERFDB/ctl1PERFDB.ora, /u01/PERFDB
                                                                              /u03/oradata/PERFDB/ctl2PERFDB.ora
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL>  shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size                  2926320 bytes
Variable Size            1778387216 bytes
Database Buffers          285212672 bytes
Redo Buffers               30625792 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracleprod01:PERFDB:/home/oracle/CB $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 28 23:20:32 2017

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

connected to target database: PERFDB (not mounted)

RMAN> restore controlfile to '+DATA from '/u01/PERFDB/u02/oradata/PERFDB/ctl1PERFDB.ora';

Starting restore at 28-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1861 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 28-JUN-17

RMAN> restore controlfile to '+RECO'  from  '/u01/PERFDB/u03/oradata/PERFDB/ctl2PERFDB.ora';

Starting restore at 28-JUN-17
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 28-JUN-17

RMAN> exit

Recovery Manager complete.

Check the newly created control files in ASM

oracleprod01:+ASM:/home/oracle $ asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N        4096   4096  1048576   5722044  5680486                0         5680486              0             N  DATA/
MOUNTED  EXTERN  N        4096   4096  1048576   1907348  1907282                0         1907282              0             N  RECO/

ASMCMD [+DATA/PERFDB] > ls
CONTROLFILE/
DATAFILE/
TEMPFILE/
ASMCMD [+DATA/PERFDB] > cd CONTROLFILE/
ASMCMD [+DATA/PERFDB/CONTROLFILE] > ls
current.448.947892061
ASMCMD [+DATA/PERFDB/CONTROLFILE] > cd +RECO/PERFDB/CONTROLFILE
ASMCMD [+RECO/PERFDB/CONTROLFILE] > ls
current.262.947892111


oracleprod01:PERFDB:/home/oracle/CB $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 28 23:27:16 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/product/12.1.0.2.64/dbs/spfileperfdb.ora

SQL> alter system set control_files='+DATA/PERFDB/CONTROLFILE/current.448.947892061','+RECO/PERFDB/CONTROLFILE/current.262.947892111' scope=spfile;

System altered.

SQL> alter database mount ;

Database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
oracleprod01:PERFDB:/home/oracle/CB $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 28 23:30:25 2017

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

connected to target database: PERFDB (DBID=29412348733, not open)

RMAN> run
2> {
3> BACKUP AS BACKUPSET SPFILE;
4> RESTORE SPFILE TO '+DATA/PERFDB/spfileperfdb.ora';
5> }

Starting backup at 28-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2109 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-JUN-17
channel ORA_DISK_1: finished piece 1 at 28-JUN-17
piece handle=/u01/PERFDB/u100/PERFDB/backupset/2017_06_28/o1_mf_nnsnf_TAG20170628T233117_do90nofm_.bkp tag=TAG20170628T233117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JUN-17

Starting restore at 28-JUN-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/PERFDB/spfileperfdb.ora
channel ORA_DISK_1: reading from backup piece /u01/PERFDB/u100/PERFDB/backupset/2017_06_28/o1_mf_nnsnf_TAG20170628T233117_do90nofm_.bkp
channel ORA_DISK_1: piece handle=/u01/PERFDB/u100/PERFDB/backupset/2017_06_28/o1_mf_nnsnf_TAG20170628T233117_do90nofm_.bkp tag=TAG20170628T233117
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-JUN-17

RMAN> exit


Recovery Manager complete.

Backup the existing spfile and create new pfile pointing to spfile

oracleprod01:PERFDB:/home/oracle/CB $ cd $ORACLE_HOME/dbs
oracleprod01:PERFDB:/opt/oracle/product/12.1.0.2.64/dbs $ ls -l *PERFDB*
-rw-rw---- 1 oracle dba 1544 Jun 28 23:30 hc_perfdb.dat
-rw-r----- 1 oracle dba 3584 Jun 28 23:30 spfileperfdb.ora
oracleprod01:PERFDB:/opt/oracle/product/12.1.0.2.64/dbs $ mv spfileperfdb.ora spfileperdb.ora.bk
oracleprod01:PERFDB:/opt/oracle/product/12.1.0.2.64/dbs $ vi initperfdb.ora
spfile='+DATA/PERFDB/spfileperfdb.ora'

oracleprod01:PERFDB:/opt/oracle/product/12.1.0.2.64/dbs $
oracleprod01:PERFDB:/opt/oracle/product/12.1.0.2.64/dbs $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 28 23:33:04 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size                  2926320 bytes
Variable Size            1778387216 bytes
Database Buffers          285212672 bytes
Redo Buffers               30625792 bytes
Database mounted.
Database opened.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/PERFDB/spfileperfdb.ora

SQL> set lines 200 pages 999
SQL> select name from v$datafile union all select name from v$tempfile union all select member from v$logfile union all select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------
+DATA/PERFDB/DATAFILE/system.256.947887307
+DATA/PERFDB/DATAFILE/sysaux.257.947887315
+DATA/PERFDB/DATAFILE/undotbs1.258.947887327
+DATA/PERFDB/DATAFILE/users.259.947887443

***** Trimmed   ********
+DATA/PERFDB/DATAFILE/COMP_INDEX.442.947889883
+DATA/PERFDB/DATAFILE/COMP_INDEX.443.947889887
+DATA/PERFDB/DATAFILE/COMP_INDEX.444.947889889
/u01/PERFDB/u03/oradata/PERFDB/temp01.dbf
+DATA/PERFDB/TEMPFILE/temp.446.947890487
/u01/PERFDB/u04/oradata/PERFDB/temp28.dbf
+DATA/PERFDB/TEMPFILE/temp.445.947890197
+RECO/PERFDB/ONLINELOG/group_1.260.947891785
+RECO/PERFDB/ONLINELOG/group_2.261.947891801
+RECO/PERFDB/ONLINELOG/group_3.256.947890863
+RECO/PERFDB/ONLINELOG/group_4.257.947891047
+RECO/PERFDB/ONLINELOG/group_5.258.947891057
+RECO/PERFDB/ONLINELOG/group_6.259.947891065
+DATA/PERFDB/CONTROLFILE/current.448.947892061
+RECO/PERFDB/CONTROLFILE/current.262.947892111

201 rows selected.


Since while dropping temp tablespace something is holding it and I was unable to drop before but now after bounce we can able to drop them

SQL> ALTER TABLESPACE TEMP drop TEMPFILE '/u01/PERFDB/u03/oradata/PERFDB/temp01.dbf';

Tablespace altered.


SQL> ALTER TABLESPACE TEMP drop TEMPFILE '/u01/PERFDB/u04/oradata/PERFDB/temp28.dbf';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
-----------------------------------------------------------------------------------------------------------------
+DATA/PERFDB/TEMPFILE/temp.446.947890487
+DATA/PERFDB/TEMPFILE/temp.445.947890197


Reference:
12C New Feature : Move a Datafile Online (Doc ID 1566797.1)


Auto Scroll Stop Scroll