DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, July 30, 2018

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.


Prerequisites:

1. Oracle cluster  need to be installed on that server( for ASM)

2. Create required asm disk groups.

EXAMPLE:

Currently the datafiles are in /u01 mount point. We will move them to ASM DISK GROUP +DATA.

Database Name: TESTDB

Asm disk group: +DATA


oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 28 20:45:03 2018

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

connected to target database: TESTDB (DBID=2123458733)

RMAN> report schema;

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

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

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


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

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

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

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

RMAN> exit


Datafile Move:
==============
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> spool off;

SQL> @move_dbfiles.sql

Tempfile:
=========

SELECT FILE_NAME,BYTES/1024/1024 FROM DBA_TEMP_FILES;

create temporary tablespace TEMP1 tempfile '+DATA' SIZE 100m reuse AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Check the Session that using Temp Tablespace:
==============================================
col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000

SQL>SELECT s.sid,s.serial#, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;

SQL>alter system kill session 'sid,serial#' immediate;

SQL>drop tablespace temp including contents and datafiles;


SQL>create temporary tablespace TEMP tempfile '+DATA' SIZE 100m AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

SQL>ALTER TABLESPACE temp ADD TEMPFILE '+DATA' SIZE 100m  AUTOEXTEND ON NEXT 10M MAXSIZE 1G;


SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

SQL>drop tablespace temp1 including contents and datafiles;



Logfile:
==========
SQL> select member from v$logfile;

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

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

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

SQL>> alter database drop logfile group 1;

Statement processed

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

Statement processed

SQL>> alter database drop logfile group 2;

Statement processed

SQL>> alter database drop logfile group 3 ;

Statement processed

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

Statement processed

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

Statement processed

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

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

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/TESTDB/ONLINELOG/group_1.260.947891785
+DATA/TESTDB/ONLINELOG/group_2.261.947891801
+DATA/TESTDB/ONLINELOG/group_3.256.947890863
/u01/TESTDB/u72/redo/redo01b.log
/u01/TESTDB/u70/redo/redo03a.log
/u01/TESTDB/u72/redo/redo03b.log


SQL> alter system switch logfile;

Statement processed

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

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

SQL> alter system checkpoint;

Statement processed

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

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


SQL> alter database drop logfile group 4;

Statement processed

SQL> alter database drop logfile group 5;

Statement processed

SQL> alter database drop logfile group 6;

Statement processed

SQL> alter database add logfile group 4 ('+DATA') size 200M;

Statement processed

SQL> alter database add logfile group 5 ('+DATA') size 200M;

Statement processed

SQL> alter database add logfile group 6 ('+DATA') size 200M;

Statement processed

SQL> select member from v$logfile;

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


Controlfile;
==============
SQL> sho parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     10
control_files                        string      /u01/TESTDB/u02/oradata/TESTDB/control01.ctl, /u01/TESTDB/u03/oradata/TESTDB/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> ALTER SYSTEM SET control_files='+DATA' scope=spfile;

SQL> select name from v$controlfile;

SQL>shut immediate;

SQL>startup nomount;

$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 28 23:20:32 2018

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

connected to target database: TESTDB (not mounted)

RMAN> restore controlfile to '+DATA' from '/u01/TESTDB/u02/oradata/TESTDB/control01.ctl';

Starting restore at 28-Jul-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-Jul-17

RMAN> restore controlfile to '+DATA'  from  '/u01/TESTDB/u03/oradata/TESTDB/control02.ctl';

Starting restore at 28-Jul-17
using channel ORA_DISK_1

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


MAN> exit

Recovery Manager complete.

Check the newly created control files in ASM

:+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/

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

/home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 28 23:27:16 2018

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/spfileTESTDB.ora

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

System altered.

SQL> alter database mount ;

Database altered.

SQL>select name from v$controlfile;

SQL> shut immediate;

SQL> startup;

No comments: