December 9, 2015

How To: Move Oracle Database Files in 12c

If it's required to change/setup db_unique_name (if not done during database creation which I always forgot to do so), then changing the db_unique_name will create another directory in ASM and start keeping all the new files into the new directory.

For Example:
-- If the db_name=DB and no db_unique_name is set: then in ASM, the database will create files like: +DATA/DB/*/
-- If the db_name=DB and db_unique_name is set, i.e. db_unique_name=DBPR, then in ASM, the database will create files like: +DATA/DBPR/*/

As a result, we will have two directory in ASM (+DATA/DB & +DATA/DBPR) which I don't want.
This post discusses above relocate/move different oracle database files, which are -
-- Parameter file
-- Password file in ASM (Oracle Database 12c New Features)
-- Redo/standby log files
-- Control files
-- Tempfiles
-- Datafiles: Online (Oracle Database 12c New Features)
-- PDB$SEED datafiles (Oracle Database 12c New Features)
-- Other Files

-- Move SPFILE in ASM in Different Location

Current Location: SPFILE='+DATA/DB/PARAMETERFILE/spfile.280.897390043'
Move To: SPFILE='+DATA/DB/spfileDB.ora'
 SQL> create pfile='/u01/stage/initDB.ora' from spfile;  
 File created.  
 SQL> !ls -ltr  
 total 4  
 -rw-r--r-- 1 oracle dba 1127 Dec 2 11:26 initDB.ora  
 SQL> !pwd  
 /u01/stage  
 SQL> create SPFILE='+DATA/DBPR/spfileDB.ora' from pfile='/u01/stage/initDB.ora';  
 File created.  
 -- Update the parameter file in /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs  
 Before -  
 oracle@db12c-primary(DB):/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cat initDB.ora  
 SPFILE='+DATA/DB/PARAMETERFILE/spfile.280.897390043'  
 After -  
 oracle@db12c-primary(DB):/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cat initDB.ora  
 SPFILE='+DATA/DBPR/spfileDB.ora'  
 -- View the current spfile parameter in OCR  
 oracle@db12c-primary(DB):/home/oracle# srvctl config database -d DBPR  
 Database unique name: DBPR  
 Database name: TEST  
 Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1  
 Oracle user: oracle  
 Spfile: +DATA/DB/PARAMETERFILE/spfile.280.897390043  
 Password file:  
 Domain:  
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Disk Groups: DATA,RECO  
 Services:  
 OSDBA group:  
 OSOPER group:  
 Database instance: DB  
 -- Update the spfile parameter in OCR  
 # srvctl modify database -db DBPR -p '+DATA/DBPR/spfileDB.ora'  
 -- Confirm if it's updated  
 oracle@db12c-primary(DB):/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# srvctl config database -d DBPR  
 Database unique name: DBPR  
 Database name: TEST  
 Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1  
 Oracle user: oracle  
 Spfile: +DATA/DBPR/spfileDB.ora  
 Password file:  
 Domain:  
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Disk Groups: DATA,RECO  
 Services:  
 OSDBA group:  
 OSOPER group:  
 Database instance: DB   

-- Move Password File in ASM for Oracle Database 12c
 -- Create the new password file (Skip this for existing password file)  
 oracle@db12c-primary(DB):/home/oracle# orapwd file='/home/oracle/orapwDB' password=welcome1 entries=3 format=12  
 oracle@db12c-primary(+ASM):/home/oracle# ls -ltr  
 total 4  
 -rw-r----- 1 oracle oinstall 4096 Dec 2 11:37 orapwDB  
 -- Set environment variables for ASM  
 oracle@db12c-primary():/home/oracle# . oraenv  
 ORACLE_SID = [oracle] ? +ASM  
 The Oracle base has been set to /u01/app/oracle  
 -- Using ASMCMD, move the password file  
 oracle@db12c-primary(+ASM):/home/oracle# asmcmd  
 ASMCMD> pwmove --dbuniquename DBPR /home/oracle/orapwDB +DATA/DBPR  
 moving /home/oracle/orapwDB -> +DATA/DBPR/orapwDB  
 -- Update the OCR  
 ASMCMD> pwset --dbuniquename DBPR +DATA/DBPR/orapwDB  
 -- Check/Show the password file  
 ASMCMD> pwget --dbuniquename DBPR  
 +DATA/DBPR/orapwDB  
 -- Check the database configuration  
 oracle@db12c-primary(+ASM):/home/oracle# srvctl config database -d DBPR  
 Database unique name: DBPR  
 Database name: TEST  
 Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1  
 Oracle user: oracle  
 Spfile: +DATA/DBPR/spfileDB.ora  
 Password file: +DATA/DBPR/orapwDB  
 Domain:  
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Disk Groups: DATA,RECO  
 Services:  
 OSDBA group:  
 OSOPER group:  
 Database instance: DB  
 -- Check the cluster resource  
 oracle@db12c-primary(+ASM):/home/oracle# crsctl stat res ora.dbpr.db -p | grep PWFILE  
 PWFILE=+DATA/DBPR/orapwDB  

-- Move the redo log and standby log files
 SQL> select member from v$logfile;  
 MEMBER  
 --------------------------------------------------  
 +DATA/DB/ONLINELOG/group_3.275.897389799  
 +RECO/DB/ONLINELOG/group_3.300.897389799  
 +DATA/DB/ONLINELOG/group_2.274.897389799  
 +RECO/DB/ONLINELOG/group_2.299.897389799  
 +DATA/DB/ONLINELOG/group_1.273.897389799  
 +RECO/DB/ONLINELOG/group_1.298.897389799  
 6 rows selected.  
 SQL> select * from v$log;  
   GROUP#  THREAD# SEQUENCE#   BYTES BLOCKSIZE  MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME  CON_ID  
 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------  
  1   1    16  52428800  512 2 YES INACTIVE    1664230 02-DEC-15   1665435 02-DEC-15    0  
  2   1    17  52428800  512 2 YES INACTIVE    1665435 02-DEC-15   1666708 02-DEC-15    0  
  3   1    18  52428800  512 2 NO CURRENT    1666708 02-DEC-15 2.8147E+14     0  
 -- Adding addition redo log file/group  
 SQL> alter database add logfile group 4 ('+DATA','+RECO') size 50M;  
 Database altered.  
 SQL> select member from v$logfile;  
 MEMBER  
 --------------------------------------------------  
 +DATA/DB/ONLINELOG/group_3.275.897389799  
 +RECO/DB/ONLINELOG/group_3.300.897389799  
 +DATA/DB/ONLINELOG/group_2.274.897389799  
 +RECO/DB/ONLINELOG/group_2.299.897389799  
 +DATA/DB/ONLINELOG/group_1.273.897389799  
 +RECO/DB/ONLINELOG/group_1.298.897389799  
 +DATA/DBPR/ONLINELOG/group_4.283.897392719  
 +RECO/DBPR/ONLINELOG/group_4.304.897392719  
 8 rows selected.  
 -- Drop the redo log group  
 SQL> alter database drop logfile group 1;  
 Database altered.  
 SQL> alter database drop logfile group 2;  
 Database altered.  
 -- Adding back the redo log groups/files  
 SQL> alter database add logfile group 1 ('+DATA','+RECO') size 50M;  
 Database altered.  
 SQL> alter database add logfile group 2 ('+DATA','+RECO') size 50M;  
 Database altered.   
SQL> select * from v$log;  
   GROUP#  THREAD# SEQUENCE#   BYTES BLOCKSIZE  MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME  CON_ID  
 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------  
  1   1    0  52428800  512 2 YES UNUSED   0   0     0  
  2   1    0  52428800  512 2 YES UNUSED   0   0     0  
  3   1    18  52428800  512 2 NO CURRENT    1666708 02-DEC-15 2.8147E+14     0  
  4   1    0  52428800  512 2 YES UNUSED   0   0     0   
-- Need a log switch to rotate the CURRENT redo logs so that it'll be possible to drop  
 SQL> alter system switch logfile;  
 System altered.  
 SQL> select * from v$log;  
   GROUP#  THREAD# SEQUENCE#   BYTES BLOCKSIZE  MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME  CON_ID  
 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------  
  1   1    19  52428800  512 2 NO CURRENT    1677760 02-DEC-15 2.8147E+14     0  
  2   1    0  52428800  512 2 YES UNUSED   0   0     0  
  3   1    18  52428800  512 2 YES INACTIVE    1666708 02-DEC-15   1677760 02-DEC-15    0  
  4   1    0  52428800  512 2 YES UNUSED   0   0     0  
 SQL> alter database drop logfile group 3;  
 Database altered.  
 SQL> alter database add logfile group 3 ('+DATA','+RECO') size 50M;  
 Database altered.  
 SQL> select member from v$logfile;  
 MEMBER  
 --------------------------------------------------  
 +DATA/DBPR/ONLINELOG/group_3.275.897393087  
 +RECO/DBPR/ONLINELOG/group_3.300.897393087  
 +DATA/DBPR/ONLINELOG/group_1.274.897393037  
 +RECO/DBPR/ONLINELOG/group_1.299.897393037  
 +DATA/DBPR/ONLINELOG/group_2.273.897393043  
 +RECO/DBPR/ONLINELOG/group_2.298.897393043  
 +DATA/DBPR/ONLINELOG/group_4.283.897392719  
 +RECO/DBPR/ONLINELOG/group_4.304.897392719  
 8 rows selected.  
 -- Add standby redo log file (for Data Guard Only)  
 SQL> alter database add standby logfile group 11 '+RECO' size 50M;  
 Database altered.  
 SQL> alter database add standby logfile group 12 '+RECO' size 50M;  
 Database altered.  
 SQL> alter database add standby logfile group 13 '+RECO' size 50M;  
 Database altered.  
 SQL> alter database add standby logfile group 14 '+RECO' size 50M;  
 Database altered.  
 SQL> alter database add standby logfile group 15 '+RECO' size 50M;  
 Database altered.  

-- TEMP Tablespace Movement
 -- Checking the TEMP file information  
 SQL> select file_name,tablespace_name from dba_temp_files;  
 FILE_NAME      TABLESPACE_NAME  
 ------------------------------------------------------------ ------------------------------  
 +DATA/DB/TEMPFILE/temp.276.897389803    TEMP  
 -- Adding a new temp file which will take the new location  
 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 1024M AUTOEXTEND OFF;  
 Tablespace altered.  
 SQL> select file_name,tablespace_name from dba_temp_files;  
 FILE_NAME      TABLESPACE_NAME  
 ------------------------------------------------------------ ------------------------------  
 +DATA/DB/TEMPFILE/temp.276.897389803    TEMP  
 +DATA/DBPR/TEMPFILE/temp.284.897393467    TEMP   
-- Offline the existing/old temp file  
 SQL> ALTER DATABASE TEMPFILE '+DATA/DB/TEMPFILE/temp.276.897389803' OFFLINE;  
 Database altered.  
 SQL> select file_name,tablespace_name,status from dba_temp_files;  
 FILE_NAME      TABLESPACE_NAME   STATUS  
 ------------------------------------------------------------ ------------------------------ -------  
 +DATA/DB/TEMPFILE/temp.276.897389803    TEMP    OFFLINE  
 +DATA/DBPR/TEMPFILE/temp.284.897393467    TEMP    ONLINE  
 -- Now, lets try to drop the temp file  
 SQL> ALTER DATABASE TEMPFILE '+DATA/DB/TEMPFILE/temp.276.897389803' DROP INCLUDING DATAFILES;  
 ALTER DATABASE TEMPFILE '+DATA/DB/TEMPFILE/temp.276.897389803' DROP INCLUDING DATAFILES  
 *  
 ERROR at line 1:  
 ORA-25152: TEMPFILE cannot be dropped at this time  
 Note: It may happen that you are not able to drop the file since it's in use.
 You can find out who/what process is using temp file from here.  
 -- Since, it's a new database, I can just restart and get my work done.  
 oracle@db12c-primary(DB):/home/oracle# srvctl stop database -d DBPR  
 oracle@db12c-primary(DB):/home/oracle# srvctl start database -d DBPR  
 oracle@db12c-primary(DB):/home/oracle# sql+  
 SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 2 12:04:48 2015  
 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> ALTER DATABASE TEMPFILE '+DATA/DB/TEMPFILE/temp.276.897389803' DROP INCLUDING DATAFILES;  
 Database altered.  
 SQL> select file_name,tablespace_name,status from dba_temp_files;  
 FILE_NAME    TABLESPACE_NAME STATUS  
 -------------------------------------------------- -------------------- -------  
 +DATA/DBPR/TEMPFILE/temp.284.897393467   TEMP  ONLINE  

-- Relocate/Move Control File
 -- Record the existing control file information  
 SQL> select name from v$controlfile;  
 NAME  
 --------------------------------------------------------------------------------  
 +DATA/DB/CONTROLFILE/current.272.897389799  
 +RECO/DB/CONTROLFILE/current.297.897389799  
 -- Set the control_files parameter to the designated disk group  
 SQL> alter system set control_files='+DATA','+RECO' scope=spfile sid='*';  
 System altered.  
 -- Stop the database and startup in nomount state  
 oracle@db12c-primary(DB):/home/oracle# srvctl stop database -d DBPR  
 oracle@db12c-primary(DB):/home/oracle# rman target /  
 Recovery Manager: Release 12.1.0.2.0 - Production on Wed Dec 2 12:08:25 2015  
 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.  
 connected to target database (not started)  
 RMAN> startup nomount  
 Oracle instance started  
 Total System Global Area  1342177280 bytes  
 Fixed Size           2924160 bytes  
 Variable Size        469762432 bytes  
 Database Buffers       855638016 bytes  
 Redo Buffers         13852672 bytes  
 -- From RMAN, restore the control file (use the existing control file name recorded initially)  
 RMAN> restore controlfile from '+DATA/DB/CONTROLFILE/current.272.897389799';  
 Starting restore at 02-DEC-15  
 using target database control file instead of recovery catalog  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=1 device type=DISK  
 channel ORA_DISK_1: copied control file copy  
 output file name=+DATA/DBPR/CONTROLFILE/current.276.897394145  
 output file name=+RECO/DBPR/CONTROLFILE/current.311.897394145  
 Finished restore at 02-DEC-15   
-- Mount and finally open the database  
 RMAN> alter database mount;  
 Statement processed  
 released channel: ORA_DISK_1  
 RMAN> alter database open;  
 Statement processed  
 RMAN> exit  
 Recovery Manager complete.  
 -- Restart once again using srvctl  
 oracle@db12c-primary(DB):/home/oracle# srvctl stop database -d DBPR  
 oracle@db12c-primary(DB):/home/oracle# srvctl start database -d DBPR  
 -- Check if the control file location change is reflected or not  
 SQL> select name from v$controlfile;  
 NAME  
 --------------------------------------------------------------------------------  
 +DATA/DBPR/CONTROLFILE/current.276.897394145  
 +RECO/DBPR/CONTROLFILE/current.311.897394145  

-- Move Datafiles
 -- Record the existing data file name  
 SQL> select file_name from dba_data_files;  
 FILE_NAME  
 --------------------------------------------------------------------------------  
 +DATA/DB/DATAFILE/system.257.897389713  
 +DATA/DB/DATAFILE/sysaux.262.897389679  
 +DATA/DB/DATAFILE/undotbs1.271.897389761  
 +DATA/DB/DATAFILE/users.270.897389759  
 -- Move the data files online (12c New Feature)  
 SQL> alter database move datafile '+DATA/DB/DATAFILE/users.270.897389759' to '+DATA';  
 Database altered.  
 SQL> alter database move datafile '+DATA/DB/DATAFILE/undotbs1.271.897389761' to '+DATA';  
 Database altered.  
 SQL> alter database move datafile '+DATA/DB/DATAFILE/sysaux.262.897389679' to '+DATA';  
 Database altered.  
 SQL> alter database move datafile '+DATA/DB/DATAFILE/system.257.897389713' to '+DATA';  
 Database altered.  
 -- Check to confirm  
 SQL> select file_name from dba_data_files;  
 FILE_NAME  
 --------------------------------------------------------------------------------  
 +DATA/DBPR/DATAFILE/system.262.897394407  
 +DATA/DBPR/DATAFILE/sysaux.271.897394383  
 +DATA/DBPR/DATAFILE/users.285.897394327  
 +DATA/DBPR/DATAFILE/undotbs1.270.897394363   

-- For PDB$SEED only
 SQL> show pdbs  
   CON_ID CON_NAME   OPEN MODE RESTRICTED  
 ---------- ------------------------------ ---------- ----------  
  2 PDB$SEED   READ ONLY NO  
 SQL> ALTER SESSION SET container=PDB$SEED;  
 SQL> select file_name,tablespace_name,status from dba_temp_files;  
 FILE_NAME            TABLESPACE_NAME  STATUS  
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------  
 +DATA/DB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012015-12-02_10-57-12-am.dbf       TEMP   OFFLINE  
 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 1024M AUTOEXTEND OFF;  
 Tablespace altered.  
 SQL> select file_name,tablespace_name,status from dba_temp_files;  
 FILE_NAME            TABLESPACE_NAME  STATUS  
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------  
 +DATA/DBPR/25E26E5DDB0125DBE053AF00A8C02D26/TEMPFILE/temp.272.897394929         TEMP   OFFLINE  
 +DATA/DB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012015-12-02_10-57-12-am.dbf       TEMP   OFFLINE  
 SQL> ALTER DATABASE TEMPFILE '+DATA/DB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012015-12-02_10-57-12-am.dbf' DROP INCLUDING DATAFILES;  
 Database altered.  
 SQL> select file_name,tablespace_name,status from dba_temp_files;  
 FILE_NAME            TABLESPACE_NAME  STATUS  
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------  
 +DATA/DBPR/25E26E5DDB0125DBE053AF00A8C02D26/TEMPFILE/temp.272.897394929         TEMP   OFFLINE  
 SQL> select file_name from dba_data_files;  
 FILE_NAME  
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 +DATA/DB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.278.897389805  
 +DATA/DB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.277.897389805  
 SQL> alter database move datafile '+DATA/DB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.278.897389805' to '+DATA';  
 Database altered.  
 SQL> alter database move datafile '+DATA/DB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.277.897389805' to '+DATA';  
 Database altered.  
 SQL> select file_name from dba_data_files;  
 FILE_NAME  
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 +DATA/DBPR/25E26E5DDB0125DBE053AF00A8C02D26/DATAFILE/sysaux.278.897395055  
 +DATA/DBPR/25E26E5DDB0125DBE053AF00A8C02D26/DATAFILE/system.279.897395041  

-- Other files to check

SQL> select filename from v$block_change_tracking;

-- Check/Verify from ASM
 ASMCMD> ls -l +DATA/DBPR  
 Type      Redund Striped Time       Sys Name  
                          Y  25E26E5DDB0125DBE053AF00A8C02D26/  
                          Y  CONTROLFILE/  
                          Y  DATAFILE/  
                          Y  ONLINELOG/  
                          Y  PARAMETERFILE/  
                          Y  TEMPFILE/  
 PASSWORD    UNPROT COARSE  DEC 02 11:00:00 N  orapwdb => +DATA/ASM/PASSWORD/pwdasm.282.897392317  
 PARAMETERFILE UNPROT COARSE  DEC 02 12:00:00 N  spfileDB.ora => +DATA/DBPR/PARAMETERFILE/spfile.281.897391855  


4 comments:

  1. As I website possessor I conceive the written content here is
    very superb, regards for your efforts.

    ReplyDelete
  2. Great post, I think people should acquire a lot from this web site
    its rattling user pleasant. So much excellent information on here :D.

    ReplyDelete
  3. Thank you for the good writeup. It in reality used to
    be a enjoyment account it. Glance complicated to more added
    agreeable from you! By the way, how could we keep in touch?

    ReplyDelete
  4. Thank you for sharing your thoughts. I truly appreciate your
    efforts and I am waiting for your further write ups thank you once again.

    ReplyDelete