- logfile (online)
- Moving Logfile in ASM from DATA to DATA_GROUP -link
- Temporary Tablespace (online)
- Moving Temporary Tablespace from DATA to DATA_GROUP -link
- Control File (offline as nomount)
- Moving Controlfile from DATA to DATA_GROUP by using RMAN -link
- Datafile (offline as mount)
- Moving Datafiles within ASM from DATA to DATA_GROUP -link
- spfile
- Moving spfile from DATA to DATA_GROUP -link
- parameter
- Setting up parameter of Database after moving database to another location -link
- CRS or HA
- Setting up parameter of CRS after moving database to another location -link
- Drop old Diskgroup
- Drop diskgroup within ASM after moving data to another diskgroup -link
vi checking_database_files_location.sql
spool checking_database_files_location.log
--to check v$asm_diskgroup
col name format a25
col COMPATIBILITY format a15
col DATABASE_COMPATIBILITY format a15
set line 400
select NAME,BLOCK_SIZE,STATE,TYPE,TOTAL_MB,FREE_MB,USABLE_FILE_MB,REQUIRED_MIRROR_FREE_MB,OFFLINE_DISKS,COMPATIBILITY, DATABASE_COMPATIBILITY
from v$asm_diskgroup where state='CONNECTED';
--to check tempfile
select distinct substr(file_name, 0,60) as TEMPFILES from dba_temp_files;
--to check logfile
select distinct substr(member, 0,60) AS REDOLOGS from v$logfile;
--to check controlfile
select distinct substr(name, 0,60) AS CONTROLFILES from v$controlfile;
--to check datafiles
select distinct substr(file_name, 0,60) AS DATAFILES from dba_data_files;
--to check parameters
select inst_id, NAME, substr(value, 0,20) from gv$parameter where value like '%DATA%';
--to check CRS or Oracle Restart
!srvctl config database -d &srvctl_sid
spool off
@checking_database_files_location.sql
the result
SQL> @checking_database_files_location.sql
NAME BLOCK_SIZE STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB REQUIRED_MIRROR_FREE_MB OFFLINE_DISKS COMPATIBILITY DATABASE_COMPAT
------------------------- ---------- ----------- ------ ---------- ---------- -------------- ----------------------- ------------- --------------- ---------------
DATA 4096 CONNECTED EXTERN 9207 4504 4504 0 0 12.1.0.0.0 10.1.0.0.0
DATA_GRUP 4096 CONNECTED EXTERN 12285 7094 7094 0 0 10.1.0.0.0 10.1.0.0.0
TEMPFILES
------------------------------------------------------------
+DATA_GRUP/AGAP/TEMPFILE/temp1.264.955303641
REDOLOGS
------------------------------------------------------------
+DATA_GRUP/AGAP/ONLINELOG/group_1.258.955300845
+DATA_GRUP/AGAP/ONLINELOG/group_2.261.955300873
+DATA_GRUP/AGAP/ONLINELOG/group_4.257.955300779
+DATA_GRUP/AGAP/ONLINELOG/group_2.260.955300873
+DATA_GRUP/AGAP/ONLINELOG/group_3.262.955301223
+DATA_GRUP/AGAP/ONLINELOG/group_3.263.955301223
+DATA_GRUP/AGAP/ONLINELOG/group_1.259.955300847
+DATA_GRUP/AGAP/ONLINELOG/group_4.256.955300779
8 rows selected.
CONTROLFILES
------------------------------------------------------------
+DATA_GRUP/AGAP/CONTROLFILE/current.265.955304607
+DATA_GRUP/AGAP/CONTROLFILE/current.266.955304615
DATAFILES
------------------------------------------------------------
+DATA_GRUP/AGAP/DATAFILE/sysaux.271.955306735
+DATA_GRUP/AGAP/DATAFILE/system.267.955306645
+DATA_GRUP/AGAP/DATAFILE/undotbs1.275.955306755
+DATA_GRUP/AGAP/DATAFILE/users.277.955306777
INST_ID NAME SUBSTR(VALUE,0,20)
---------- ------------------------- --------------------
1 spfile +DATA_GRUP/AGAP/PARA
1 control_files +DATA_GRUP/AGAP/CONT
1 db_create_file_dest +DATA_GRUP
1 db_create_online_log_dest +DATA_GRUP
_1
1 db_create_online_log_dest +DATA_GRUP
_2
1 db_recovery_file_dest +DATA_GRUP
6 rows selected.
Database unique name: agap
Database name: agap
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA_GRUP/AGAP/PARAMETERFILE/spfileagap.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA_GRUP,DATA
Services:
OSDBA group:
OSOPER group:
Database instance: agap
