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

MANAGING STORAGE STRUCTURE

MANAGING STORAGE STRUCTURE

TABLESPACE :- ORACLE USE TABLESPACE TO STORE DATA.

TABLESPACE:

Oracle stores Object data logically in tablespaces and physically in datafiles. Oracle databases are logically divided into one or more tablespaces and each Tablespace is made up of one or more physical datafiles. So Oracle use Tablespace to store schema Objects.

WHY TABLESPACE?

Tablespaces perform a number of key functions in an Oracle database, but the concept of a tablespace is not common to all relational databases. For instance, the Microsoft SQL Server database doesn’t use this concept at all.

Brief list of the benefits of using tablespaces:

• Tablespaces make it easier to allocate space quotas to various users in the database.
• Tablespaces enable you to perform partial backups and recoveries based on the tablespace as a unit.
• Because a large object like a data warehouse partitioned table can be spread over several tablespaces, you can increase performance by spanning the tablespace over several disks and controllers.
• You can take a tablespace offline without having to bring down the entire database.
• Tablespaces are an easy way to allocate database space.

• You can import or export specific application data by using the import and export utilities at the tablespace level.

Firstly we have create table.

SQL> CREATE TABLE T2 TABLESPACE ACC AS SELECT * FROM HR.EMPLOYEES;

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

TABLESPACE_NAME

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

ACC

Tablespace Views

SQL> DESC V$TABLESPACE

Name Null? Type

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

TS# NUMBER

NAME VARCHAR2(30)

INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)

BIGFILE VARCHAR2(3)

FLASHBACK_ON VARCHAR2(3)

ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> SELECT * FROM V$TABLESPACE;

TS# NAME INC BIG FLA ENC

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

0 SYSTEM YES NO YES

1 SYSAUX YES NO YES

2 UNDOTBS1 YES NO YES

4 USERS YES NO YES

3 TEMP NO NO YES

6 EXAMPLE YES NO YES

6 rows selected.

SQL> DESC DBA_DATA_FILES

Name Null? Type

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

FILE_NAME VARCHAR2(513)

FILE_ID NUMBER

TABLESPACE_NAME VARCHAR2(30)

BYTES NUMBER

BLOCKS NUMBER

STATUS VARCHAR2(9)

RELATIVE_FNO NUMBER

AUTOEXTENSIBLE VARCHAR2(3)

MAXBYTES NUMBER

MAXBLOCKS NUMBER

INCREMENT_BY NUMBER

USER_BYTES NUMBER

USER_BLOCKS NUMBER

ONLINE_STATUS VARCHAR2(7)

SQL> COL FILE_NAME FOR A45

SQL> COL TABLESPACE_NAME FOR A13

SQL>

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

Wednesday, November 8, 2017

Oracle password profile security

Oracle password profile security

create profile all_users limit
PASSWORD_LIFE_TIME 365
   PASSWORD_GRACE_TIME 10
   PASSWORD_REUSE_TIME UNLIMITED
   PASSWORD_REUSE_MAX 0
   FAILED_LOGIN_ATTEMPTS 3
   PASSWORD_LOCK_TIME UNLIMITED;

Oracle password security profile parameters

Here are the password security parameters:

failed_login_attempts - This is the number of failed login attempts before locking the Oracle user account. The default in 11g is 10 failed attempts.
password_grace_time - This is the grace period after the password_life_time limit is exceeded.
password_life_time - This is how long an existing password is valid. The default in 11g forces a password change every 180 days.
password_lock_time - This is the number of days that must pass after an account is locked before it is unlocked. It specifies how long to lock the account after the failed login attempts is met. The default in 11g is one day.
password_reuse_max - This is the number of times that you may reuse a password and is intended to prevent repeating password cycles (north, south, east, west).
password_reuse_time - This parameter specifies a time limit before a previous password can be re-entered. To allow unlimited use of previously used passwords, set password_reuse_time to UNLIMITED.
password_verify_function - This allows you to specify the name of a custom password verification function.

Dropping Profile

Profiles no longer required can be dropped with the DROP PROFILE command.

The DEFAULT profile cannot be dropped.
The CASCADE clause revokes the profile from any user account to which it was assigned – the CASCADE clause MUST BE USED if the profile has been assigned to any user account.

SQL> DROP PROFILE accountant;

ERROR at line 1:
ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE accountant CASCADE;

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

Monday, November 13, 2017