Monday, November 13, 2017

Flash Recovery Area

Flash Recovery Area (FRA)

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it use, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

The flash recovery area is the most powerful tool available from Oracle 10g, that plays a vital role in performing database backup & recovery operations. From Oracle 11g release2, flash recovery area is called as fast recovery area.

-- Utilisation (MB) du (Size)

select

   name,

  floor(space_limit / 1024 / 1024) "Size MB",

  ceil(space_used / 1024 / 1024) "Used MB"

from v$recovery_file_dest;

-- FRA Occupants

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA

show parameter db_recovery_file_dest

-- Size, used, Reclaimable 

SELECT

  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 

  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 

  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,

  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM

  V$RECOVERY_FILE_DEST A,

  V$FLASH_RECOVERY_AREA_USAGE B

GROUP BY

  SPACE_LIMIT, 

  SPACE_USED , 

  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:

-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location ;

-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';

Friday, November 10, 2017

INCREASE TABLESPACE SIZE

INCREASE TABLESPACE SIZE

We can increase tablespace size three ways of

1) RESIZE EXISTING DATAFILE

2) ENABLE AUTOEXTEND

3) ADD NEW DATAFILE

1) RESIZE EXISTING DATAFILE */

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/acc01.dbf' RESIZE 20M;
Database altered.

2) ENABLE AUTOEXTEND

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/acc01.dbf' AUTOEXTEND ON;

Database altered.

SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 ORG_SIZE_MB,MAXBYTES/1024/1024 MAX_SIZE_MB FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME ORG_SIZE_MB MAX_SIZE_MB
------------- --------------------------------------------- ----------- -----------
USERS /u01/app/oracle/oradata/orcl/users01.dbf 5 32767.9844
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 60 32767.9844
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510 32767.9844
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 680 32767.9844
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100 32767.9844
ACC /u01/app/oracle/oradata/orcl/acc01.dbf 20 32767.9844

6 rows selected.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/acc01.dbf' AUTOEXTEND ON MAXSIZE 1G;

Database altered.

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 ORG_SIZE_MB,MAXBYTES/1024/1024 MAX_SIZE_MB FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME ORG_SIZE_MB MAX_SIZE_MB
------------- --------------------------------------------- ----------- -----------
USERS /u01/app/oracle/oradata/orcl/users01.dbf 5 32767.9844
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 60 32767.9844
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510 32767.9844
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 680 32767.9844
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100 32767.9844
ACC /u01/app/oracle/oradata/orcl/acc01.dbf 20 1024

6 rows selected.

3) ADD NEW DATAFILE

SQL> ALTER TABLESPACE ACC ADD DATAFILE '/u01/app/oracle/oradata/orcl/acc02.dbf' size 1m autoextend on maxsize 1g;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 ORG_SIZE_MB,MAXBYTES/1024/1024 MAX_SIZE_MB FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME ORG_SIZE_MB MAX_SIZE_MB

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

USERS /u01/app/oracle/oradata/orcl/users01.dbf 5 32767.9844

UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 60 32767.9844

SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510 32767.9844

SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 680 32767.9844

EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100 32767.9844

ACC /u01/app/oracle/oradata/orcl/acc01.dbf 20 1024

ACC /u01/app/oracle/oradata/orcl/acc02.dbf 1 1024

BIGFILE TABLESPACE

SQL> ALTER TABLESPACE DUC ADD DATAFILE '/u01/app/oracle/oradata/orcl/duc02.dbf' size 10m;

ALTER TABLESPACE DUC ADD DATAFILE '/u01/app/oracle/oradata/orcl/duc02.dbf' size 10m

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace

Note---We cann't add Bigfile Tablespace size..

ACTIVITY ON TABLESPACE

ACTIVITY ON TABLESPACE

ALTER TABLESPACE ACC READ ONLY;

Tablespace altered.

ALTER TABLESPACE ACC READ WRITE;

Tablespace altered.

ALTER TABLESPACE ACC OFFLINE;

Tablespace altered.

ALTER TABLESPACE ACC ONLINE;

Tablespace altered.

ALTER TABLESPACE ACC RENAME TO ACCOUNT;

Tablespace altered.

SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME
------------- ---------------------------------------------
USERS /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
ACCOUNT /u01/app/oracle/oradata/orcl/acc01.dbf
ACCOUNT /u01/app/oracle/oradata/orcl/acc02.dbf
DUC /u01/app/oracle/oradata/orcl/duc01.dbf
UNDO2 /u01/app/oracle/oradata/orcl/undo02.dbf

9 rows selected.

RENAME OR RELOCATING DATAFILE

ALTER TABLESPACE DUC OFFLINE;

Tablespace altered.

SQL> !mv /u01/app/oracle/oradata/orcl/duc01.dbf /u01/

SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/duc01.dbf' TO '/u01/duc01.dbf';

Database altered.

SQL> ALTER TABLESPACE DUC ONLINE;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME
------------- ---------------------------------------------
USERS /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
ACCOUNT /u01/app/oracle/oradata/orcl/acc01.dbf
ACCOUNT /u01/app/oracle/oradata/orcl/acc02.dbf
DUC /u01/duc01.dbf
UNDO2 /u01/app/oracle/oradata/orcl/undo02.dbf

9 rows selected.

SQL> ALTER TABLESPACE ACCOUNT DROP DATAFILE '/u01/app/oracle/oradata/orcl/acc02.dbf';

Tablespace altered.

SQL> DROP TABLESPACE DUC;

Tablespace dropped.

SQL> DROP TABLESPACE ACCOUNT INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Thursday, November 9, 2017

TABLESPACE TYPE

TABLESPACE TYPE:-

1) PERMANENT TABLESPACE :- Used to store Object data.

A) SMALLFILE TABLESPACE
B) BIGFILE TABLESPACE ---> FROM 10G

2) UNDO TABLESPACE :- Used to Support ROLLBACK, READ CONSISTENCY, FLASHBACK

3) TEMPORARY TABLESPACE .

1) PERMANENT TABLESPACE --

A)SMALLFILE TABLESPACE

CREATE TABLESPACE ACC DATAFILE '/u01/app/oracle/oradata/orcl/acc01.dbf' size 1m;

Tablespace created.

SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 ORG_SIZE_MB,MAXBYTES/1024/1024 MAX_SIZE_MB FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME ORG_SIZE_MB MAX_SIZE_MB
------------- --------------------------------------------- ----------- -----------
USERS /u01/app/oracle/oradata/orcl/users01.dbf 5 32767.9844
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 60 32767.9844
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510 32767.9844
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 680 32767.9844
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100 32767.9844
ACC /u01/app/oracle/oradata/orcl/acc01.dbf 1 0

B) * BIGFILE TABLESPACE ---> 10G

SQL> CREATE BIGFILE TABLESPACE DUC DATAFILE '/u01/app/oracle/oradata/orcl/duc01.dbf' size 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace created.

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 ORG_SIZE_MB,MAXBYTES/1024/1024 MAX_SIZE_MB FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME ORG_SIZE_MB MAX_SIZE_MB
------------- --------------------------------------------- ----------- -----------
USERS /u01/app/oracle/oradata/orcl/users01.dbf 5 32767.9844
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 60 32767.9844
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510 32767.9844
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 680 32767.9844
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100 32767.9844
ACC /u01/app/oracle/oradata/orcl/acc01.dbf 20 1024
ACC /u01/app/oracle/oradata/orcl/acc02.dbf 1 1024
DUC /u01/app/oracle/oradata/orcl/duc01.dbf 1 33554432

8 rows selected.

SQL> SELECT 33554432/1024 FROM DUAL;

33554432/1024
-------------
32768

SQL> SELECT 32768/1024 FROM DUAL;

32768/1024
----------
32

2) UNDO TABLESPACE :- STORE UNDO DATA ( OLD IMAGE OF TRANSACTION )
ROLLBACK
READ CONSISTENCY
FLASHBACK

SHOW PARAMETER UNDO

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> CREATE UNDO TABLESPACE UNDO2 DATAFILE '/u01/app/oracle/oradata/orcl/undo02.dbf' size 10m;

Tablespace created.

If you want change undo file location , use this Parameter

SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDO2';

System altered.

SQL> SHOW PARAMETER UNDO

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO2

3)TEMPORARY TABLESPACE :- ORDER BY
DISTINCT
CREATE INDEX ...
GROUP BY
etc

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10m;

Tablespace created.

If you want change TEMP file location , use this Parameter

SQL> DESC DATABASE_PROPERTIES

Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
PROPERTY_NAME NOT NULL VARCHAR2(30)
PROPERTY_VALUE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)

SQL> COL PROPERTY_VALUE FOR A30

SQL> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT%';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

Database altered.

SQL> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT%';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE

DEFAULT_PERMANENT_TABLESPACE ---> USERS */

SQL> CREATE USER KRISH IDENTIFIED BY KRISH;

User created.

SQL> GRANT CREATE SESSION,CREATE TABLE TO KRISH;

Grant succeeded.

SQL> CONN KRISH/KRISH
Connected.
SQL>
SQL> CREATE TABLE T1(ID INT);

Table created.

If you want change DEFAULT PERMANENT TABLESPACE , use this Parameter

SQL> SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='T1';

TABLESPACE_NAME
------------------------------
USERS

SQL> INSERT INTO T1 VALUES(1)

1 ROW CREATED

ALTER DATABASE DEFAULT TABLESPACE EXAMPLE;

SQL> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT%';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE EXAMPLE
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE

SQL> SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='T2';

TABLESPACE_NAME
------------------------------
EXAMPLE

Swati DBA Blog (Progress,Mongodb,MYsql Oracle,SQL SERVER) and Linux ,Azure

Monday, November 13, 2017