JP : 2015

Wrongly added datafile in ASM, migrating the non-ASM datafile to ASM datafile:

One of our junior DBA added the datafile to tablespace (which is running on ASM storage), without + symbol before diskgroup name,

File got added in the dbs location, (regular filesystem).

we have to move the datafile from non-asm(regular filesystem) to ASM file.

Here are the steps:

new 1: select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB from dba_data_files where tablespace_name='PROD_DATA'

FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB

---------- ---------------------------------------------------------------------- ---------- --------------------- --------- ----------

130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767

131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767

1062 /ora/app/oracle/product/11.2.0.4/dbs/DATA_01 2048 ONLINE YES 32768

SQL> alter database datafile 1062 offline;

Database altered.

SQL>

SQL> select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB from dba_data_files where tablespace_name='PROD_DATA'

2 ;

FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB

---------- ---------------------------------------------------------------------- ---------- --------------------- --------- ----------

130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767

131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767

1062 /ora/app/oracle/product/11.2.0.4/dbs/DATA_01 RECOVER

DPOTHPRD1:dpqctprd01:oracle$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 15 00:33:57 2015

connected to target database: DPOTHPRD (DBID=2188772963)

RMAN> backup as copy datafile 1062 format '+DATA_01';

Starting backup at 15-OCT-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=762 instance=DPOTHPRD1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=01062 name=/ora/app/oracle/product/11.2.0.4/dbs/DATA_01

output file name=+DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879 tag=TAG20151015T003436 RECID=15 STAMP=893118886

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 15-OCT-15

RMAN>

RMAN> switch datafile 1062 to copy;

datafile 1062 switched to datafile copy "+DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879"

RMAN>

SQL>select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB from dba_data_files where tablespace_name='PROD_DATA';

FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB STATUS

---------- ------------------------------------------------------------------------------------------ ---------- --------------------- --------- ---------- ---------------------------

130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767 AVAILABLE

131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767 AVAILABLE

1062 +DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879 RECOVER AVAILABLE

SQL> recover datafile 1062;

Media recovery complete.

SQL>

SQL> alter database datafile 1062 online;

Database altered.

SQL> select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB,status from dba_data_files where tablespace_name='PROD_DATA';

FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB STATUS

---------- ------------------------------------------------------------------------------------------ ---------- --------------------- --------- ---------- ---------------------------

130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767 AVAILABLE

131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767 AVAILABLE

1062 +DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879 2048 ONLINE YES 32768 AVAILABLE

How to check the last update time on object

1) select * from all_tab_modifications where table_name = '<table_name>'; ( may not be accurate)

2) select to_char(scn_to_timestamp(max(ora_rowscn))) from <table_name>; ( available from 10g).

SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;

Min value that you can convert is :(oldest available SCN).
SQL> select min(SCN) min_scn from sys.smon_scn_time;

MIN_SCN
------------------
10346369869923

*** The values of ORA_ROWSCN available are constrained by the contents of SYS.SMON_SCN_TIME.

If you give try to convert the value which less than this value:

SQL> select scn_to_timestamp(10346369869922) from dual;

select scn_to_timestamp(10346369869922) from dual

ERROR at line 1:

ORA-08181: specified number is not a valid system change number

ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

To check the current change number:

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

10347141818837

SQL> select scn_to_timestamp(10346369869924) from dual;

SCN_TO_TIMESTAMP(10346369869924)

---------------------------------------------------------------------------

11-SEP-15 09.31.02.000000000 PM

TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamp value. You must do to_timestamp convertion for the character value.

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;
SCN
———-
845396

JP

Thursday, October 15, 2015

Wrongly added datafile in ASM, migrating the non-ASM datafile to ASM datafile

Wrongly added datafile in ASM, migrating the non-ASM datafile to ASM datafile:

Friday, September 18, 2015

How to check the last DML time on object

How to check the last update time on object