How to check if RMAN catalog is installed for a database?
There are couple of ways to determine if RMAN database is registered with a catalog.
Using RMAN when you connect to RMAN catalog and try to run a RMAN command like “list backup” it will generate an error as shown below
calora4db01q:/home/oracle $ rman target / catalog rmancataloguser/rmancatalogpassword@catalogdb
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Jul 17 08:33:42 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TESTDB (DBID=1023910334)
connected to recovery catalog database
RMAN> list backup;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/17/2011 08:33:48
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog
Another way would be to connect to the catalog schema through SQL*Plus and check view RC_DATABASE
$ sqlplus rmancataloguser/rmancatalogpassword@catalogdb
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jul 17 08:45:58 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from rc_database where name = ‘TESTDB’;
no rows selected
How to generate a list of exceptions generated due to constraint violations?
The following shows how exceptions can be captured.
– Create exceptions table
SQL> @?/rdbms/admin/utlexcpt.sql
Table created.
SQL> desc exceptions;
Name Null? Type
—————————————– ——– ————————
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
SQL> select * from xx;
X
———-
11
11
– add constraint but disable it
SQL> alter table xx add constraint xx_pk primary key (x) disable;
Table altered.
– enable constraint and generate any exceptions to exceptions table
SQL> alter table xx enable validate constraint xx_pk exceptions into exceptions;
alter table xx enable validate constraint xx_pk exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (SYSTEM.XX_PK) – primary key violated
– List exceptions
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME
—————— —————————— ——————————
CONSTRAINT
——————————
AAAErDAABAAAHy5AAB SYSTEM XX
XX_PK
AAAErDAABAAAHy5AAA SYSTEM XX
XX_PK
SQL> select * from xx where rowid IN ( <AAAErDAABAAAHy5AAB>, <AAAErDAABAAAHy5AAA> );
X
———-
11
11
How to list current active roles for a user in a session?
Using SESSION_ROLES it will retrieve the current roles that are active for a user in a session.
Example:
SQL> connect test/…
SQL> select * from session_roles;
ROLE
——————————
DELETE_CATALOG_ROLE
Using “SET ROLE” one can change the roles active in the current session.
Example:
SQL> select * from session_roles;
ROLE
——————————
TEST11
Enable specific set of roles in the current session
SQL> set role test11, delete_catalog_role;
Role set.
SQL> select * from session_roles;
ROLE
——————————
DELETE_CATALOG_ROLE
TEST11
Enable all roles in the current session
SQL> set role all;
Role set.
DEFAULT role explained
It seems at times when roles are assigned they are not assigned as DEFAULT role to a user.
The reason for seems to be the following: In the table sys.user$ there is a column called “DEFROLE” which indicates if the role will be a default role or not. The possible values that seem to be are as follows:
0 – None (So when a role is assigned to a user the role is not a default) and none of the current roles are default. The value is set to 0 by “ALTER USER test DEFAULT ROLE NONE”.
1 – Roles assigned to users and the ones that will be assigned will be default roles. The value is set to 1 by “ALTER USER test DEFAULT ROLE ALL”.
2 – Specific roles are default roles, the list of roles that are default to a user is stored in the table “DEFROLE$” and also can be seen when querying “DBA_ROLE_PRIVS” column “DEFAULT_ROLE”. The value is set to 2 by “ALTER USER test DEFAULT ROLE <role name>” only if the value was 0, if the value is 1 it can't be changed to 2 unless to 0 (NONE) first.
So once a value of “DEFROLE” is 1, it can only be changed if the default role is set to “NONE” so any roles assigned or will be assigned are DEFAULT roles.
Example: When the value of DEFROLE = 0 (NONE) for a user
SQL> create user test identified by abcd1234;
User created.
SQL> alter user test default role none;
User altered.
SQL> grant dba to test;
Grant succeeded.
SQL> grant imp_full_database to test;
Grant succeeded.
– Shows none of the roles assigned are default roles
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO NO
TEST IMP_FULL_DATABASE NO NO
Example: When the value of DEFROLE = 1 (ALL) for a user
SQL> create user test identified by abcd1234;
User created.
SQL> alter user test default role none;
User altered.
SQL> grant imp_full_database to test;
Grant succeeded.
SQL> grant dba to test;
Grant succeeded.
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO NO
TEST IMP_FULL_DATABASE NO NO
SQL> alter user test default role all;
User altered.
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST IMP_FULL_DATABASE NO YES
SQL> grant exp_full_database to test;
Grant succeeded.
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST EXP_FULL_DATABASE NO YES
TEST IMP_FULL_DATABASE NO YES
Example: When the value of DEFROLE = 2 for a user
SQL> create user test identified by abcd1234;
User created.
SQL> alter user test default role none;
User altered.
SQL> select user#, defrole from sys.user$ where name = 'TEST';
USER# DEFROLE
———- ———-
35 0
SQL> grant dba to test;
Grant succeeded.
SQL> alter user test default role dba;
User altered.
SQL> grant imp_full_database to test;
Grant succeeded.
SQL> select user#, defrole from sys.user$ where name = 'TEST';
USER# DEFROLE
———- ———-
35 2
– Shows the DBA role is default and INP_FULL_DATABASE is not the default role
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST IMP_FULL_DATABASE NO NO
Global temporary tables
– Global temporary table which keeps records till session
create global temporary table temp1( year number ) on commit preserve rows;
SQL> insert into temp1 values ( 11 );
1 row created.
SQL> select * from temp1;
YEAR
———-
11
– reconnect to session and check records
SQL> connect /as sysdba
Connected.
SQL> select * from temp1;
no rows selected
– Global temporary table which keeps records till session does a commit/rollback
Example:
– create global temporary table based on commit
create global temporary table temp2( year number ) on commit delete rows;
SQL> insert into temp2 values ( 12 );
1 row created.
SQL> select * from temp2;
YEAR
———-
12
SQL> commit;
Commit complete.
– shows records disappear after commit/rollback
SQL> select * from temp2;
no rows selected
SQL> insert into temp2 values ( 12 );
1 row created.
SQL> rollback;
Rollback complete.
SQL> select * from temp2;
no rows selected
– By checking the value of DURATION in user_tables one can tell if the table is session based on transaction based, if value is SYS#SESSION then it is session based and if the value is SYS$TRANSACTION then it is transaction based
SQL> select duration from user_tables where table_name = ‘TEMP1′;
DURATION
—————
SYS$SESSION
SQL> select duration from dba_tables where table_name = ‘TEMP2′;
DURATION
—————
SYS$TRANSACTION
How to list indexes and the column names?
– Lists user indexes that are not primary and not unique indexes in SQLServer 2005 and up
select t.name as table_name
, ind.name as index_name
, ic.index_column_id — column order in the index
, col.name
from sys.indexes ind
inner join sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id
inner join sys.tables t on ind.object_id = t.object_id
where ind.is_primary_key = 0
and ind.is_unique = 0
and ind.is_unique_constraint = 0
and t.is_ms_shipped = 0
order by t.name
, ind.index_id
, ic.index_column_id
table_name index_name index_column_id column_name
————— ————— —————— —————-
base_action PK_base_action 1 action_id
base_category PK_base_category 1 type_id
base_category PK_base_category 2 table_id
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON
When a user account is created on SQLServer on 2005 with user account set to “Enforce password policy” and “Enforce password expiration”, once set this policy and expiration can’t be changed using.
ALTER LOGIN test WITH CHECK_EXPIRATION = OFF;
Or
ALTER LOGIN test WITH CHECK_POLICY = OFF;
SQLserver will return the following message
Msg 15128, Level 16, State 1, Line 1
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
To fix this issue which will keep the password the same and it will turn off check policy and expiration.
ALTER LOGIN test WITH PASSWORD = ”
GO
ALTER LOGIN test WITH
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
How to get current session id, process id, client process id?
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’);
SID SERIAL# PROCESSID CLIENTPID
———- ———- ——— ———
43 52612 420734 5852:5460
V$SESSION.SID and V$SESSION.SERIAL# are database process id
V$PROCESS.SPID – Shadow process id on the database server
V$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.
Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES
In 11g the Oracle the hash password is no longer stored in DBA_USERS, it is stored in SYS.USER$ table in the column “PASSWORD” and “SPARE4″. So there are different ways password can be set depending on if “PASSWORD” and “SPARE4″ are set in SYS.USER$ and what you want PASSWORD_VERSIONS to be.
If only “SPARE4″ is used to set the password the password version is 11g and in SYS.USER$ password value becomes NULL after ALTER statement
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
——————————————————————————–
F894844C34402B67
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G 11G
SQL> alter user scott identified by values 'S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346';
User altered.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
———————————————————————–
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346
If both password and SPARE4 is used to restore the password, the PASSWORD_VERSIONS is “10G 11G”, to set this the IDENTIFIED BY VALUES is passed in as “SPARE4;PASSWORD” from sys.user$
SQL> alter user scott identified by values 'S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B;F894844C34402B67';
User altered.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G 11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
——————————————————————–
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B
If the value from PASSWORD is used from SYS.USER$ the password version is set to 10g and column SPARE4 is set to NULL
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
————————————————————————-
F894844C34402B67
Oracle passwords in 11g
In 11g Oracle supports password to be case sensitive. But the password can be case insensitive depending on value of “PASSWORD_VERSIONS” and “sec_case_sensitive_logon”.
In 11g dba_users view has a new column called “PASSWORD_VERSIONS”, it can have 3 possible values “10G”, “10G 11G”, “11G”. Below shows what they mean along with parameter “sec_case_sensitive_logon”.
Scenario when the value of password_versions in DBA_USERS is “10G 11G”, when the value “10G 11G” the column “PASSWORD” and “SPARE4″ has a value
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G 11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
————————————————————————
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B
-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is TRUE, Oracle matches the case of the password.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
———————————— ———– ——————-
sec_case_sensitive_logon boolean TRUE
SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect scott/tiger
Connected.
SQL>
-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is FALSE the password entered does not have to be case-sensitive.
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
SQL> connect scott/TIGER
Connected.
Scenario when password_versions is 11G – In this scenario the password is case-sensitive irrespective what the value of sec_case_sensitive_logon so the password is always case-sensitive. In this case the column “PASSWORD” is NULL and SPARE4 is not NULL
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
——————————————————————-
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346
-- Shows example when sec_case_sensitive_logon is TRUE and password versions is 11G the password is case-sensitive irrespective the value of “sec_case_sensitive_logon”.
SQL> alter system set sec_case_sensitive_logon=TRUE;
System altered.
SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
-- Shows example when set sec_case_sensitive_logon is FALSE when password versions is 11G that it only works when password is case-sensitive
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect scott/tiger
Connected.
Scenario when password_versions is 10G – In this scenario the password is not case-sensitive. In this case in sys.user$ “PASSWORD” is NOT NULL and SPARE4 is NULL.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
—————————————————————–
F894844C34402B67
-- Shows the password is not case sensitive so changing the parameter sec_case_sensitive_logon has no effect when password is entered for SCOTT
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> connect scott/TIGER
Connected.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
———————————— ———– ——-
sec_case_sensitive_logon boolean FALSE
SQL> connect scott/TIGER
Connected.
