Move datafile one location to different location online in oracle 12c Version 12.1.0.2 and later

Move datafile one location to different location  online in  oracle 12c Version 12.1.0.2 and later:
==========================================================================================================


Login as sysdba 



to get the file id and datafile loation info using below query 
=========================================================================
select FILE_ID ,FILE_NAME,TABLESPACE_NAME from dba_data_files ;



In OMF (Oracle Managed file name feature)
=====================================================

Error  while move datafile in OMF : 


SQL> ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data21/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data22/abcd_grlw8cvz_.dbf';
ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data1/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data1/abcd_grlw8cvz_.dbf'
*
ERROR at line 1:
ORA-01276: Cannot add file /oracle/oradata/data1/abcd_grlw8cvz_.dbf.  File has an Oracle Managed Files file name.


SQL>

Solution
==============

Set db_create_file_dest before moving the datafile and do not specify the datafile name

SQL> alter session set db_create_file_dest='<DIR_NEW>';
SQL> alter database move datafile 21;




In Normal 
==================

alter database move datafile '/u01/oradata/test/test.dbf' to '/u02/oradata/test/test.dbf';

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR OVER THE LAST WEEK:

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR OVER THE LAST WEEK:

Using below query you can find how many log switch in last week per hour basis. this will help to analyze how much database was busy in last week and what time db is so much busy .

You can set FRA based of log switch analysis .if db is running in archive log mode .




Query :
=============

SET PAGESIZE 500
SET LINESIZE 200
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <8;


Example :


SQL>   2  SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
  3    4  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
  5  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
  6  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
  7  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
  8  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
  9  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
 10   11  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
 12   13   14   15   16  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
 17   18   19  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
 20   21   22   23  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
 24   25  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
 26   27   28   29   30   31    ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <10; 32

DAY   00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
20/11    55   470    51     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0
19/11    54   417   878    83     0     0     4     5   272   516   175     0     2     0     0     0     0     0    12   159     0     1     4     0
18/11    94   380     0   399   577     0     1     3     0   101     0     0     2     0     0     0     0     1     9   150     0     1     1     0
17/11     0     0     0     1     0     0     5     9     0     0     0     0     2     0     0     0     0     0     3    14     0     1     0     0
16/11    16     0     0     0     0     0     0     0     1     0     0     0     2     0     0     0     0     0     0     3     0     0     0     0
15/11    60   505   707    84     0     0     9     8     0    97     0     1     1     0     0     0     0     1    21   167     0    35     0     0
14/11    58   422     0     0     0     0     5    16     0    96    50    32     2     0     1     0     0     0   807   166     0     1     0     0
13/11     0     0    10    35    35   419    19     7     0    98     0     0     2     0     0     0     0   356   953   163     0     1     0     0

8 rows selected.


SQL>

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

Using below query you can find how many redo log switched per hours in oracle database .it will show you how much database is busy .


Login as sys user and run below query .

Query:
============

select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
 from v$log_history 
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
 )
group by Hour
order by Hour;


Example :

SQL> select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
  2    3    4   from v$log_history
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
  5    6    7   )
group by Hour
  8    9  order by Hour;

HO LOG_SWITCHES
-- ------------
00           56
01          439
02          412
03          120
04          306
05          419
06            7
07            8
08          137
09          182
10          113
11           17
12            2
14            1
17          119
18          301
19          117
21            7
22            3

19 rows selected.

SQL>