Thursday, March 13, 2014

Create Oracle role with Password

7:21 PM Posted by Dilli Raj Maharjan No comments
A role is a set of privileges that can be granted to users or to other roles. We can add privileges to a role and then grant the role to a user. We can then enable the role and exercise the privileges granted by the role. A role contains all privileges granted to the role and all privileges of other roles granted to it. A new role is initially empty. We add privileges to a role with the GRANT statement.
Create role, grant privileges and grant role to existing user.
CREATE ROLE jr_dba
   IDENTIFIED BY "n(V6VW3x!tFOW!SB"; 

SQL> CREATE ROLE jr_dba
   IDENTIFIED BY "n(V6VW3x!tFOW!SB";   2  

Role created.

SQL> grant select any table, backup any table to jr_dba;

Grant succeeded.

SQL> 

SQL> grant connect, resource, jr_dba to dilli;

Grant succeeded.

SQL> 
Now you can login to with the user. When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.
SQL> conn dilli  
Enter password: 
Connected.
SQL> 

SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

This example would enable the role called jr_dba with a password. You cannot enable role without password.
SQL> set role jr_dba;
set role jr_dba
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'JR_DBA'


SQL> set role jr_dba identified by "n(V6VW3x!tFOW!SB";

Role set.

SQL> 

Now role jr_dba is enabled and you have all privileges that roles jr_dba has. 
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
 14

SQL> 

We can set non-default role to default role for the user with syntax below.
alter user <username> default role <role_list>;
alter user dilli default role jr_dba;

SQL> show user
USER is "SYS"
SQL> 
SQL> alter user dilli default role jr_dba, connect, resource;

User altered.

SQL> 

SQL> conn dilli
Enter password: 
Connected.
SQL> select count(*) from scott.emp;     
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role jr_dba identified by "n(V6VW3x!tFOW!SB";

Role set.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
 14

SQL> 
Any Password protected roles cannot be set to default
Syntax for the setting default role

alter user <username> default role <role_list>;
You can even use keywords instead of a list of roles for <role_list> :

ALL : all roles granted to that user are set by default
NONE : all roles granted have to be set upon login
ALL EXCEPT <excluded_role_list> : all roles granted are set by default except the ones in <excluded_role_list>.

Wednesday, March 12, 2014

ORA-01548: active rollback segment ' SYSSMU13 3758039048$' found, terminate dropping tablespace

8:54 PM Posted by Dilli Raj Maharjan No comments
Error noticed while creating tablespace.
  1  create tablespace tbs_moha
  2  datafile '/MOHA/oradata/orcl/moha_01.dbf' size 5G
  3  autoextend on
  4* maxsize unlimited
SQL> /
create tablespace tbs_moha
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Find the name of tablespace associated with the missing datafile.
SQL> select d.name,t.name
  2  from v$datafile d join v$tablespace t
  3  using(ts#);

NAME
--------------------------------------------------------------------------------
NAME
------------------------------
/home/oracle/BACKUP/undotbs02.dbf
UNDOTBS2
Check Undo Parameters.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2
Shutdown the database, change undo_mangement to Manual and start the database
SQL> shutdown abort;
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

Modify pfile and set undo_management='Manual'

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
Create new undo tablespace, shutdown database, set undo management auto and undo tablespace to newly created tablespace
  1  create undo tablespace undotbs01
  2  datafile '/u01/app/oracle/oradata/orcl/undotbs01_01.dbf' size 10m
  3  autoextend on
  4* maxsize unlimited
SQL> /

Tablespace created.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  MANUAL
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

open pfile and modify undo_management='Auto' and undo_tablespace='Undotbs01'

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> show parameter undo

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

Now create tablespace will create successfully
  1  create tablespace tbs_moha
  2  datafile '/MOHA/oradata/orcl/moha01.dbf' size 1G
  3  autoextend on
  4* maxsize unlimited
SQL> /

Tablespace created.
Following command failed due to existing rollback segment on the old undo tablespace.
SQL> grant dba to usr_moha;
grant dba to usr_moha
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 15 cannot be read at this time
ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf'
So tried to drop old undo tablespace
SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU12_3370723747$' found, terminate
dropping tablespace
Shutdown database and add following parameter
This parameter is oracle's hidden and undocumented parameters. So use at your own risk.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.
vi initorcl.ora
*._offline_rollback_segments=(_SYSSMU12_3370723747$)

SQL> create spfile from pfile;

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU13_3758039048$' found, terminate
dropping tablespace
Append _SYSSMU13_3758039048$ to the end of the _offline_rollback_segments. Keep going until the the rollback segment and drop tablespace successfully
*._offline_rollback_segments=(_SYSSMU32_3855527180$,_SYSSMU33_2662784219$,_SYSSMU34_2457069885$,_SYSSMU35_1632431741$,_SYSSMU36_3409971511$,_SYSSMU37_2689280970$,_SYSSMU38_389146150$,_SYSSMU39_3304332257$,_SYSSMU40_2103468367$,_SYSSMU41_2387847232$,
_SYSSMU12_3370723747$
,_SYSSMU13_3758039048$
,_SYSSMU14_2103326955$
,_SYSSMU15_3693451306$
,_SYSSMU16_3316561802$
,_SYSSMU17_2198165518$
,_SYSSMU18_3012613150$
,_SYSSMU19_1884560376$
,_SYSSMU20_2031163871$
,_SYSSMU21_1352513242$
)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

vi initorcl.ora

SQL> create spfile from pfile;

File created.

SQL> startup;

ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.

SQL> drop tablespace UNDOTBS2;

Tablespace dropped.

Finally remove all the parameters and restart the database.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  undotbs01
SQL> show parameter rollback

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
_offline_rollback_segments      string  _SYSSMU32_3855527180$, _SYSSMU
       33_2662784219$, _SYSSMU34_2457
       069885$, _SYSSMU35_1632431741$
       , _SYSSMU36_3409971511$, _SYSS
       MU37_2689280970$, _SYSSMU38_38
       9146150$, _SYSSMU39_3304332257
       $, _SYSSMU40_2103468367$, _SYS
       SMU41_2387847232$, _SYSSMU12_3
       370723747$, _SYSSMU13_37580390
       48$, _SYSSMU14_2103326955$, _S
       YSSMU15_3693451306$, _SYSSMU16

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
       _3316561802$, _SYSSMU17_219816
       5518$, _SYSSMU18_3012613150$,
       _SYSSMU19_1884560376$, _SYSSMU
       20_2031163871$, _SYSSMU21_1352
       513242$


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

vi initorcl.ora

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> show parameter offline_rollback_segments;
SQL> grant dba to usr_moha;

Grant succeeded.

SQL> 

Tuesday, March 11, 2014

Friday, March 7, 2014

FlashBack Archive run out of space

4:42 PM Posted by Dilli Raj Maharjan 1 comment

FlashBack Archive run out of space. 

Whenever you execute any DML statement you will encounter ORA-55617 Error which means Flashback Archive runs out of space and tracking on table is suspended. 

Following is the sample of DML command and the error message.
SQL> update employees set salary=salary*1.1;
update employees set salary=salary*1.1
       *
ERROR at line 1:
ORA-55617: Flashback Archive "FLA1" runs out of space and tracking on
"EMPLOYEES" is suspended
The is because flashback archive has reached its limit, ORA-55617 happens because either flashback archive quota has reached or tablespace has reached it max size. Query the name of the tablespace used for flashback archive and its quota.
SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME         QUOTA_IN_MB
-------------------- ------------------ ------------------------------ ----------------------------------------
FDA1          1 FDA_TBS1         100
FLA1          2 FLA_TBS1         10

SQL> 

Now it is clear that Flashback archive FLA1 has 10MB quota on tablespace FLA_TBS1. Verify the size of tablespace and resize the datafile if required before heading ahead. Find the size of the tablespace
SQL> select tablespace_name,bytes/1024/1024 as "Size MB",maxbytes/1024/1024 as "MaxSize MB" from dba_data_files where tablespace_name='FLA_TBS1';

TABLESPACE_NAME     Size MB MaxSize MB
------------------------------ ---------- ----------
FLA_TBS1         10     0

Find the datafile of tablespace FLA_TBS1.
SQL> select t.name,d.name
  2  from v$tablespace t join v$datafile d
  3  using (ts#)
  4  where t.name='FLA_TBS1';

NAME
------------------------------
NAME
--------------------------------------------------------------------------------
FLA_TBS1
/home/oracle/BACKUP/fla_tbs01.dbf

Resize datafile with alter database command
SQL> alter database datafile '/home/oracle/BACKUP/fla_tbs01.dbf' resize 1000m;

Database altered.

SQL> 
Verify the size of the tablespace now.
SQL> select tablespace_name,bytes/1024/1024 as "Size MB",maxbytes/1024/1024 as "MaxSize MB" from dba_data_files where tablespace_name='FLA_TBS1';

TABLESPACE_NAME     Size MB MaxSize MB
------------------------------ ---------- ----------
FLA_TBS1        1000    0

SQL> 
Increase the quota of the flashback archive fla1 to 50M
SQL> alter flashback archive fla1
  2  modify tablespace FLA_TBS1 quota 50m;

Flashback archive altered.
Now any DML command will execute successfully till the quota exceed.
SQL> update employees
  2  set salary=salary*1.1;

107 rows updated.

SQL>