Issue cloning user privileges

I was using the script below to clone the user privileges of user1 and assign them to user2:

select 'grant ' || PRIVILEGE || ' on ' || owner || '.' || TABLE_NAME || ' to ' || 'USER2' || decode(GRANTABLE,'YES',' with grant option;','NO',';') from
  dba_tab_privs
where
  GRANTEE = upper('USER1')
union
select
  'grant ' ||GRANTED_ROLE || ' to ' || 'USER2' || ';'
from
  dba_role_privs
where
  GRANTEE = upper('USER1')
union
select
  'grant ' || PRIVILEGE || ' to ' || 'USER2' || ';'
from
  dba_sys_privs
where
  GRANTEE = upper('USER1')
union
select
  'alter user ' || 'USER2' || ' quota ' || decode(MAX_BYTES,-1,'UNLIMITED', to_char(MAX_BYTES/(1024*1024),'9999') || 'M') || ' on ' || TABLESPACE_NAME || ';'
from
  dba_ts_quotas
where
  USERNAME = upper('USER1');

Everything seems to be working fine and both users had identical privileges, yet, USER2 was not able to see all the tables that USER1 could see.

The issue turned out to be that bothe users wer granted two roles ROLE1 and ROLE2, but the only difference was that ROLE2 was a default role for both users, but ROLE1 was default only for USER1.

SQL> SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM dba_role_privs WHERE grantee='USER1';

GRANTED_ROLE                   ADM DEF
------------------------------ --- ---
ROLE1                          NO  YES
ROLE2                          NO  YES


SQL> SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM dba_role_privs WHERE grantee='USER2';

GRANTED_ROLE                   ADM DEF
------------------------------ --- ---
ROLE1                          NO  NO
ROLE2                          NO  YES

Running the sql below fixed the issue

ALTER USER USER2 DEFAULT ROLE ALL;

or

ALTER USER USER2 DEFAULT ROLE ROLE1, ROLE2;

Caution: Running

ALTER USER USER2 DEFAULT ROLE ROLE1;

would set ROLE1 as a default role but remove all other roles (in this case ROLE2) from default role.

IP Addresses and Oracle RAC

There are 3 types of IP addresses used in RAC:

• Private IP - used for internal communication between the cluster nodes
• Public IP - regular IP address used to communicate with the network
• Virtual IP - used by database applications to enable failover when one node fails

Connections to the server are done through listener, and if connection has been established and listener fails, that's not an issue since after connection has been established, there is no need for a listener. Now if the net connection is trying to get a session while listener os down, the user will get an error message as connection fails. That's in a regular environment.

In RAC environment database is in a sharing mode - shared between all conencted nodes thus more than 1 listener is running in various nodes. So if a listener is down and user process is trying to get a connection, RAC will automatically transfer that request to another listener on a different node.

Before physical IP addresses were used in listener configuration which is a disadvantage over using virtual IP addresses because when using physical IP the session will wait for a timeout before failing over to another node. using Virtual IP (VIP) in listener coniguration because Oracle Notifiction Service (OBS) maintains the communication between the nodes and will notify all the nodes and listeners whenever one of the nodes is down, and VIP of the failed node will automatically divert to surviving node and session will be established in that node. There won't be any wait for TCP/IP timeout event.

ORA-00257 archiver error. Connect internal only, until freed.

Error: ORA-00257 archiver error. Connect internal only, until freed.

Details: This error occurs when attempting to login via sqlplus

Cause:One of the drives was out of space, the one that stored archive logs.

Solution:Removing old archive logs by starting RMAN and running the following script:

run {
delete noprompt archivelog all completed before 'sysdate-7';
}

The above will remove all archive logs older than 7 days

How to find archive logs location

Execute from sqlplus:

1) archive log list;

2) show parameter db_recovery_file_dest;

or query V$ARCHIVE_DEST

3) select dest_name, status, destination from v$archive_dest; 

UNIX - clearing out files that are older than 30 days

Useful command for clearing out old files:

find ./* -type f -mtime +30 -exec rm {} \;

The above will clear out files that are older than 30 days in the current directory. find ./* will look at the current directory, grabbing all the files (*), -mtime +30 will specify files that are older than 30 days, and -exec rm {} \ will remove all the files that match the condition.