2013-12-23

Saving user passwords - Oracle 11g


Oracle 11.2

password set in USER$
SEC_CASE_SENSITIVE_LOGON

TRUE
FALSE
password only
non-sensitive
non-sensitive
spare4 only
sensitive
access denied
both set
sensitive
non-sensitive


To save all user passwords
#!/bin/bash
export PWDSQL=user_passwords_${ORACLE_SID}_`date +%Y-%m-%d-%H%M`.sql
sqlplus -S / as sysdba <
set echo off termout off trimspool on feedback off heading off linesize 200 pagesize 0
spool $PWDSQL
select 'alter user '||username||' identified by values '||DBMS_LOB.SUBSTR(REGEXP_SUBSTR(DBMS_METADATA.GET_DDL ('USER',USERNAME), '''.*'''))||';' stmt
from dba_users
where password is null -- to exclude EXTERNAL
and username not in ('XS\$NULL','ANONYMOUS') -- use escape char \ for $ if in shell
order by username;
spool off
EOF

For single user
select 'alter user '||username||' identified by values '||DBMS_LOB.SUBSTR(REGEXP_SUBSTR(DBMS_METADATA.GET_DDL ('USER',USERNAME), '''.*'''))||';' stmt
from dba_users where username like upper('syst%');

2013-12-12

ETL, bulk load to Oracle DB guidelines

Environment

Oracle 11.2, Infosphere DataStage 8.7

Steps briefly

Disable constraints
Truncate target table
Disable indexes
Run bulk upload ETL job
Rebuild indexes
Create unique indexes
Enable constraints
Gather optimizer statistics

Details

Disable R constraints

Disable U,P constraints

-          It drops UNIQUE index if it was created by ALTER TABLE ADD CONSTRAINT statement (use KEEP|DROP INDEX to change the behavior)
-          Other indexes remain

Truncate target table

It validates unusable indexes!

Make indexes UNUSABLE

-          UNUSABLE UNIQUE index does not allow DML. It has to be dropped.
ORA-26026: unique index BID.TEST1_I1 initially in unusable state (Direct-path Insert)
ORA-01502: index 'BID.TEST1_I1' or partition of such index is in unusable state (Regular Insert)

-          U,P constraints can also be policed by non-unique indexes

Run bulk upload ETL job

DataStage bulk load is seen as /*+ SYS_DL_CURSOR */ (OCI direct path load).
Target table needs to be NOLOGGING to reduce REDO (unrecoverable operation!)
For direct-path INSERT e.g. /*+ APPEND */,  /*+ SYS_DL_CURSOR */
table mode
DB log_mode
REDO
recovery
logging
noarchivelog
no redo
PITR before insert
nologging
noarchivelog
no redo
PITR before insert
logging
archivelog
redo
to the failure
nologging
archivelog
no redo
PITR before insert

Other DML always generate REDO.
If you do direct-path insert into table with indexes, index update will generate redo whether it is LOGGING or NOLOGGING. (and index is recoverable)

Rebuild indexes

Use NOLOGGING, PARALLEL to speed up. Unrecoverable operation!
After DB/TS recovery you might have:
SELECT COUNT(1) FROM BID.S_CLAIM_STATS_YEARLY WHERE INDUSTRY_ID=18005047
*
Error at line 0
ORA-01578: ORACLE data block corrupted (file # 73, block # 10488)
ORA-01110: data file 73: '/data001/oradata/BIDDEV/biddev_bid_data_test.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Attempt to rebuild index returns the same errors. (ALTER INDEX BID.CLMSTSYR_IND_FK REBUILD;)
Index has to be (dropped and recreated) or (made unusable and then rebuild).

Create unique indexes

If it is not part of constraint or you can leave it for CONSTRAINT ENABLE statements

Enable constraints R,U,P

If there are no proper indexes for U,P constrains, CONSTRAINT ENABLE statement will create the index. By default it will be LOGGING in DEFAULT tablespace.  Use USING INDEX to change it (you don’t have to specify index name), e.g.
ALTER TABLE BID.TEST1 ENABLE CONSTRAINT TEST1_U USING INDEX NOLOGGING TABLESPACE BID_DATA_TEST;

Gather optimizer statistics

Gather statistics for the table. Indexes can be excluded if they were rebuilt (CASCADE=FALSE) as Oracle automatically collects statistics during index creation and rebuild.
Alternatively statistics for table+indexes can be gathered before rebuilding indexes, especially if not all indexes are disabled or dropped.

Documentation

Direct Path Loading
INSERT statement
logging_clause