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%');

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

List databases from OEM with pivoting PROPERTY NAME to columns

Environment: OEM12, Oracle database 11.2


SELECT * FROM 
   (SELECT T.TARGET_NAME, P1.PROPERTY_NAME, P1.PROPERTY_VALUE
    FROM SYSMAN.MGMT_TARGETS T, SYSMAN.MGMT_TARGET_PROPERTIES P1
    WHERE T.TARGET_TYPE='oracle_database'
    AND T.TARGET_GUID = P1.TARGET_GUID
    AND NOT EXISTS (SELECT 1 FROM MGMT_BLACKOUT_FLAT_TARGETS B WHERE T.TARGET_GUID = B.TARGET_GUID) -- excluding blackouts
    AND UPPER(T.TARGET_NAME) LIKE '%' -- filter by database name
    AND P1.PROPERTY_NAME IN ('DBVersion','VersionBanner','CPUCount','MachineName','orcl_gtp_lifecycle_status','OracleHome')
   )
PIVOT
(MAX(PROPERTY_VALUE) FOR PROPERTY_NAME IN ('DBVersion' ,'VersionBanner','CPUCount','MachineName','orcl_gtp_lifecycle_status' "Lifecycle",'OracleHome'))
ORDER BY 1;

NFS mount options for Oracle database

OEM 12c job failed to create standby database for Oracle 10.2 on Oracle Linux.
NFS mounted staging area is used.
Production database alert log shows
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/mnt/software/oracle_CCASEPRD_linux/CCASEPRD_43/CONTROL_FILE_BACKUP_STANDBY_NO_RECOVERY'

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 2944'

which is also confirmed by SQLPLUS running the same command.

The problem is solved by using nolock option for NFS mount.

The following NFS options are successful:

rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,nolock,vers=3,timeo=600

Useful info on Oracle Support:
Mount Options for Oracle files when used with NFS on NAS devices (Doc ID 359515.1)
Howto Optimize NFS Performance with NFS options. (Doc ID 397194.1)
Poor Performance of APPS 11i or 12 When Using Shared APPL_TOP with NAS (Doc ID 802704.1)

OEM 12c Named Credentials - SSH Key Credentials

The following commands can be used on OMS host to configure SSH connection from OMS host to target host. Check/backup authorized_keys on OMS host. sshUserSetup.sh overwrites authorized_keys instead of just adding public key (it created backup though authorized_keys.tmp).
cd /oui/prov/resources/scripts
sshUserSetup.sh -setup -user -hosts

Alternately (if you do not have password for oracle on target host) generate SSH keys on OMS host with ssh-keygen and add id_rsa.pub to authorized_keys on target host (/home/oracle/.ssh) using root or sudo.

Then in OEM navigate to Setup > Security > Named Credentials

and create New Credential with Credential Type = SSH Key Credentials, using private/public keys from OMS host.