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=29412328733)

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