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
As I website possessor I conceive the written content here is
ReplyDeletevery superb, regards for your efforts.
Great post, I think people should acquire a lot from this web site
ReplyDeleteits rattling user pleasant. So much excellent information on here :D.
Thank you for the good writeup. It in reality used to
ReplyDeletebe a enjoyment account it. Glance complicated to more added
agreeable from you! By the way, how could we keep in touch?
Thank you for sharing your thoughts. I truly appreciate your
ReplyDeleteefforts and I am waiting for your further write ups thank you once again.