Oracle: 2017

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

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

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

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

http://chandu208.blogspot.com/2017/11/rman-08137-warning-archived-log-not.html

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

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

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

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.

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

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

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.

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

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

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

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

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

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

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)