Creating Users in Oracle

Log into sqlplus (as sysdba)

>create user identified by default tablespace temporary tablespace TEMP quota unlimited on   quota untitled on

>grant connect to ;

>grant resource to ;

Check Patchset Level

Log in as an app user

Check for adutconf.sql file

>cd $AD_TOP/sql/adutconf.sql

>sqlplus

>@adutconf

Should create adutconf.lst file or ()

Applying Patches using adpatch

**********************

Steps overview:
**********************

1. bring down instance
2. Enable Maintenance mode
3. Apply Patch
4. Disable Maintenance

**********************
Steps in detail:
**********************

1^st –Research patch (check prereqs and settings)

2^nd- check that it hasn’t been applied already

            SQL> select * from ad_bugs where bug_number = ‘’;

3^rd – FTP the patch from oracle

            >ftp updates.oracle.com (coresys/coredba)

            >cd

            >bin

            > ls –         ex. ls –linux

            >get .zip    ex. get p3948369_11i_linux.zip

            >bye

4^th – go the patch directory

            >cd /patch/patches or solaris or generic

5^th -  unzip the patch in /patch/patches/SOLARIS/XXXX/

6^th -  bring down the instance and email everyone(opsdesk, dbadesk and functional)

            -Run the shut script in home directory

            - Make sure all processes are dead

7^th – go to adpatch (all defaults except size = 12,000 and sys pwd = manager)

            Apply patches in order (‘c,d,g’ in that order – or just ‘u’ if only one)

(“adpatch flags=hidepw options=noprereq” if it doesn’t work successfully? Only sometimes?)

Here are the prompts you will get, step by step:

- Is this the correct APPL_TOP [Yes] ?

- Filename [adpatch.log] :  XXXX_c/d/g/u#.log, i.e. - INST_c3358343.log

- You can be notified by email if a failure occurs.

Do you wish to activate this feature [No] ?

- Please enter the batchsize [1000] : 12000

- Is this the correct database [Yes] ?

- Enter the password for your 'SYSTEM' ORACLE schema: manager

- Enter the ORACLE password of Application Object Library [APPS] : - sometimes apps

- The default directory is [/patch/patches/SOLARIS/INTT/3358343] :

- Please enter the name of your AutoPatch driver file - c/d/g#.drv i.e. - d3358343.drv

ON d DRIVERS:

AD utilities can support a maximum of 999 workers. Your

current database configuration supports a maximum of 222 workers.

Oracle recommends that you use between 20 and 40 workers.

Enter the number of parallel workers [20] :

Watch it go!

            * * if error occurs asking to change the maintenance mode- change mode using

                        adadmin and re-run the patch and press no to the question asking to start

                        where patch last left of and then yes to confirm answer. Once the patch is

                        done- go back to adadmin and disable maintenance mode.

8^th – bring up the instance  :  start

9^th – bounce apache server

            - log into application and submit active users before releasing the instance.

10^th – compile invalid objects after patching

11^th - run the above query and check file version to make sure patch has been applied.

Enabling SQL Trace in Oracle Database

The SQL Trace facility can either be enabled/disabled for an individual
session or the instance.
        *       To enable the SQL trace facility for your session
                issue the following SQL statement:
                      ALTER SESSION
                        SET SQL_TRACE = TRUE; 
        *       To disable the SQL trace facility for your session
                issue the following SQL statement:
                        ALTER SESSION

                        SET SQL_TRACE = FALSE;

To find out if instance is in AutoConfig mode or not?

Log in as appl _ _ _ _.

>cd $APPL_TOP/admin

>ls *.xml (should have SID.xml files)

• If there  is .xml file than (it usually is in auto-config mode) but check the time stamp
• If sid.xml is NOT there than its not in auto-config mode

Check Oracle Database Status (RAC or Standalone)

SQL PLUS into the database and then run the below query

set echo off
set line 150
set pages 100
set serveroutput on
set feedback on
set time on
set timing on

col HOST_NAME for a30
col "UP TIME" for a25

show user

select name,INSTANCE_NAME,OPEN_MODE,HOST_NAME,DATABASE_STATUS,logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,v$instance;


select instance_name,HOST_NAME from gv$instance order by 1;

col "Datafiles Status" for a40
select distinct status "Datafiles Status" from v$datafile;

set echo on

Change Oracle Wallet Keys

1. Backup the current P12 to archive

a. Oracle Wallet keys are stored in ACFS, a cluster file system on top of ASM. On Unix, access to the wallet is be limited to the 'oracle:oinstall' user:group, using proper directory (700) and file permissions (600). 

b. Its back up after each master-rekey operation or changes to a encrypted network drive. (Kee-Pass)

c. The backups are stored away from database backups.

2. Use orapki wallet display -wallet to see master key list and validate the password

[TEST-DB]/ora/wallets/test-db> orapki wallet display -wallet /ora/wallets/test-db/

Oracle PKI Tool : Version 11.2.0.3.0 - Production

Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:  

Requested Certificates:

User Certificates:

Oracle Secret Store entries:

oracle.security.client.connect_string1

oracle.security.client.password1

oracle.security.client.username1

ORACLE.SECURITY.DB.ENCRYPTION.

ORACLE.SECURITY.DB.ENCRYPTION.

ORACLE.SECURITY.DB.ENCRYPTION.

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Trusted Certificates:

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

3. Change Oracle Wallet Password

[test-db1]/ora/wallets/test-db> orapki wallet change_pwd -wallet /ora/wallets/test-db/

Oracle PKI Tool : Version 11.2.0.3.0 - Production

Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:  

New password:

Enter wallet password:  

[test-db1]/ora/wallets/test-db>

4. Use orapki wallet display -wallet to see that a new master key has been added

[test-db1]/ora/wallets/test-db/client> orapki wallet display -wallet /ora/wallets/test-db/

Oracle PKI Tool : Version 11.2.0.3.0 - Production

Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:  

PKI-02002: Unable to open the wallet. Check password.

[test-db1]/ora/wallets/test-db/client> orapki wallet display -wallet /ora/wallets/test-db/

Oracle PKI Tool : Version 11.2.0.3.0 - Production

Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:  

Requested Certificates:

User Certificates:

Oracle Secret Store entries:

oracle.security.client.connect_string1

oracle.security.client.password1

oracle.security.client.username1

ORACLE.SECURITY.DB.ENCRYPTION.

ORACLE.SECURITY.DB.ENCRYPTION.

ORACLE.SECURITY.DB.ENCRYPTION.

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Trusted Certificates:

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

[test-db1]/ora/wallets/test-db/client>

5. Backup the new P12 to archive and the Kee-Pass

6. Close the wallet and reopen

SQL> alter system set encryption wallet open identified by "";

alter system set encryption wallet open identified by ""

*

ERROR at line 1:

ORA-28353: failed to open wallet

SQL> alter system set encryption wallet open identified by "";

System altered.

SQL> exit

7. Run a query that touches TDE encrypted data

--When the wallet is closed

select * from encrypted_table;

ORA-28365: wallet is not open

28365. 0000 -  "wallet is not open"

*Cause:    The security module wallet has not been opened.

*Action:   Open the wallet.

--Opening the wallet

select count(*) from encrypted_table;

17981