Easy to Learn Oracle Database and Solve Your Problem. : DB File

Showing posts with label DB File. Show all posts

Friday, September 18, 2015

ORA-03297: file contains used data beyond requested RESIZE value

Cause : Some portion of datafile are using by database Segment which you trying to trim.

Solution : Move / Drop all Segment extends from the region prior that you want to trim.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> SELECT TABLESPACE_NAME
2 FROM DBA_DATA_FILES
3 WHERE FILE_NAME ='/u01/app/oracle/oradata/orcln1/tbs_data01.dbf';

TABLESPACE_NAME
------------------------------
TBS_DATA

SQL> SELECT DISTINCT SEGMENT_TYPE,SEGMENT_NAME
2 FROM DBA_EXTENTS
3 WHERE TABLESPACE_NAME = 'TBS_DATA';

SEGMENT_TYPE SEGMENT_NAME
------------------ ---------------------------------------------------------------------------------
TABLE TEST_RECLAIM_SPACE
INDEX IND_SERIAL_NO_TEST_RECL

SQL> ALTER TABLE TEST_RECLAIM_SPACE MOVE;

Table altered.

SQL> ALTER INDEX IND_SERIAL_NO_TEST_RECL REBUILD TABLESPACE TBS_DATA;

Index altered.

SQL> purge recyclebin;

Recyclebin purged.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M;

Database altered.

SQL>

Thursday, July 17, 2014

Creating Control File in oracle database.

We can create control file using backup scrips of current control file.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:\APP\CONTROL_FILE.SQL';

Now open your CONTROL_FILE.SQL and copy the following line and run from isqlplus.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\APP\ORADATA\ORCL\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\APP\ORADATA\ORCL\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'D:\APP\ORADATA\ORCL\REDO03.LOG' SIZE 50M BLOCKSIZE 512
DATAFILE
'D:\APP\ORADATA\ORCL\SYSTEM01.DBF',
'D:\APP\ORADATA\ORCL\SYSAUX01.DBF',
'D:\APP\ORADATA\ORCL\UNDOTBS01.DBF',
'D:\APP\ORADATA\ORCL\USERS01.DBF',
'D:\APP\ORADATA\ORCL\EXAMPLE01.DBF',
'D:\APP\ORADATA\SPFTL\TBACNTS.DBF',
'D:\APP\ORADATA\SPFTL\TRBFES.DBF',
'D:\APP\ORADATA\SPFTL\TBFES.DBF',
'D:\APP\ORADATA\SPFTL\TBAML.DBF',
'D:\APP\ORADATA\SPFTL\DATA.DBF',
'D:\APP\ORADATA\SPFTL\TBSTRAN .DBF',
'D:\APP\ORADATA\SPFTL\MIG_BCBL01.DBF',
'D:\APP\ORADATA\SPFTL\STLBAS01.DBF',
'D:\APP\ORADATA\STLBAS\ORBITS01.DBF',
'D:\APP\ORADATA\STLBAS\ORBMAS01.DBF',
'D:\APP\ORADATA\STLBAS\ORBSYS01.DBF',
'D:\APP\ORADATA\STLBAS\ORBDT101.DBF',
'D:\APP\ORADATA\STLBAS\ORBDT201.DBF',
'D:\APP\ORADATA\STLBAS\ORBDT301.DBF',
'D:\APP\ORADATA\STLBAS\ORBLOG01.DBF',
'D:\APP\ORADATA\STLBAS\ORBMAV01.DBF',
'D:\APP\ORADATA\STLBAS\ORBIND01.DBF',
'D:\APP\ORADATA\STLBAS\ORBIMG01.DBF',
'D:\APP\ORADATA\STLBAS\ORBBBR01.DBF',
'D:\APP\ORADATA\ORCL\APEX_DATA01.DBF',
'D:\APP\ORADATA\STLBAS\MYBANK01.DBF',
'D:\APP\ORADATA\SPFTL\SHRINK_TEST.DBF',
'D:\APP\ORADATA\ORCL\RECLAIM01.DBF',
'D:\APP\ORADATA\ORCL\RND_TBS.DBF'
CHARACTER SET WE8MSWIN1252
;

Another way to create control file you need to collect control file information form data dictionary view.

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE; 
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

After collect information create control file from nomount mode.

CREATE CONTROLFILE
   SET DATABASE prod
   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', 
                    '/u01/oracle/prod/redo01_02.log'),
           GROUP 2 ('/u01/oracle/prod/redo02_01.log', 
                    '/u01/oracle/prod/redo02_02.log'),
           GROUP 3 ('/u01/oracle/prod/redo03_01.log', 
                    '/u01/oracle/prod/redo03_02.log') 
   NORESETLOGS
   DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
            '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
            '/u01/oracle/prod/users01.dbs' SIZE 5M,
            '/u01/oracle/prod/temp01.dbs' SIZE 5M
   MAXLOGFILES 50
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 400
   MAXDATAFILES 200
   MAXINSTANCES 6
   ARCHIVELOG;

Monday, July 14, 2014

Moving or Renaming Data Files While the Database is Online.

C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 14 14:46:17 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> CONN SYS /AS SYSDBA
Enter password:
Connected.
SQL>
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\DBRMAN\USERS01.DBF
D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
D:\APP\ORADATA\DBRMAN\TEST01.DBF

7 rows selected.

SQL> ALTER TABLESPACE TEST OFFLINE;

Tablespace altered.

SQL> CONN TEST/TEST;
Connected.
SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
READ_ONLY TABLE
TEST_DATA TABLE

SQL> DESC TEST_DATA;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(300)

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
2 WHERE TABLE_NAME='TEST_DATA';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_DATA TEST

SQL> INSERT INTO TEST_DATA VALUES(10,'RAJIB');
INSERT INTO TEST_DATA VALUES(10,'RAJIB')
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:\APP\ORADATA\TEST01.DBF'

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\rajib.pradhan>move D:\app\oradata\DBRMAN\TEST01.DBF D:\app\oradata\TEST01.DBF
1 file(s) moved.

C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 14 14:52:10 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> CONN SYS /AS SYSDBA
Enter password:
Connected.
SQL>
SQL> ALTER DATABASE RENAME FILE 'D:\APP\ORADATA\DBRMAN\TEST01.DBF' TO 'D:\app\oradata\TEST01.DBF';

Database altered.

SQL> ALTER TABLESPACE TEST ONLINE;

Tablespace altered.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\DBRMAN\USERS01.DBF
D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
D:\APP\ORADATA\TEST01.DBF

7 rows selected.

SQL>

Finding spfile Location from your database.

Using this query you can find your physical spfile location.

SQL> CONN /AS SYSDBA
Connected.
SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------------------
D:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEDBRMAN.ORA

SQL>

Tuesday, May 6, 2014

How to find tablespace size in oralc database.

Using this query you can find out you tablespace size and used percentage.

SQL> SELECT DF.TABLESPACE_NAME, SUM(DF.BYTES)/1024/1024 "Total Size (MB)", SUM(FS.BYTES)/1024/1024 "Total Free (MB)",
2 TRUNC(((SUM(FS.BYTES)/(SUM(DF.BYTES))*100)),1)||'%' "Free Percent"
3 FROM DBA_DATA_FILES DF, DBA_FREE_SPACE FS
4 WHERE FS.TABLESPACE_NAME=DF.TABLESPACE_NAME
5 AND DF.TABLESPACE_NAME NOT IN ('USERS','UNDOTBS1','SYSAUX','SYSTEM')
6 GROUP BY DF.TABLESPACE_NAME;

TABLESPACE_NAME                Total Size (MB) Total Free (MB) Free Percent
------------------------------ --------------- --------------- -----------------------------------------
DATABAK                                    512             511 99.8%
MIG_BCBL                                  1024            1023 99.9%
ORBLOG                                    1024         801.375 78.2%
ORBIND                                 1072104         1163.25 .1%
RECLAIM_TS                                  52          1.5625 3%
DATA                                      9920        758.4375 7.6%

TABLESPACE_NAME                Total Size (MB) Total Free (MB) Free Percent
------------------------------ --------------- --------------- -----------------------------------------
ORBSYS                                    2048         1012.75 49.4%
EXAMPLE                                    300         21.5625 7.1%
ORBDT2                                    1024             626 61.1%
ORBMAV                                    2048         987.625 48.2%
ORBIMG                                    3324             181 5.4%
SHRINK_TEST                                  5               4 80%
ORBMAS                                    1024        788.8125 77%
ORBDT3                                    2248          58.875 2.6%
TBSTRAN                                   3072         1022.25 33.2%
ORBITS                                    9216        262.6875 2.8%

24 rows selected.

Easy to Learn Oracle Database and Solve Your Problem.

Query

Search Blog