Oracle Database 12c Installation and Creating Database

Oracle Database 12c Release 1 Installation Pre- Requirements / Manual Setup

This post prepearing for Oracle Linux 6 (OEL6) and Oracle Database 12c Release 1 database software. If you prefer automatic setup please refer this document

Oracle Database 12c Release 1 Installation Pre- Requirements / Automatic Setup

       Oracle simplified 12c database installation at Oracle Linux 6 by donwloading and installing oracle-rdbms-server-12cR1-preinstall package from  Public Yum repository.  While installing this package server try to connect on internet yum repository and run wget command neccessary rpms.

http://public-yum.oracle.com/

1- Information :



When it is installed, the Oracle Pre-Install RPM does the following:

■ Automatically downloads and installs any additional RPM packages needed for installing Oracle Grid Infrastructure and Oracle Database, resolves any dependencies

 

■ Creates an oracle user and creates oraInventory(oinstall) and OSDBA (dba) groups for that user.

■ As needed, sets sysctl.conf settings , system startup parameters, driver parameters to values based on recommandations from Oracle RDBMS Pre-Install program.

■Sets hard and soft resource limits

■ Sets other recommended parameters and depending on your kernel version

 

2- Installation :

[root@server rpm]# yum install oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_64.rpm

Loaded plugins: refresh-packagekit, security

file:///iso/repodata/repomd.xml: [Errno 14] Could not open/read file:///iso/repodata/repomd.xml

Trying other mirror.

Setting up Install Process

Examining oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_64.rpm: oracle-rdbms-server-12cR1-preinstall-                                                                                                                                   1.0-8.el6.x86_64

Marking oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_64.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-8.el6 will be installed

--> Processing Dependency: compat-libcap1 for package: oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_                                                                                                                                   64

--> Processing Dependency: compat-libstdc++-33 for package: oracle-rdbms-server-12cR1-preinstall-1.0-8.el6                                                                                                                                   .x86_64

--> Processing Dependency: gcc-c++ for package: oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_64

--> Processing Dependency: ksh for package: oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_64

--> Processing Dependency: libaio-devel for package: oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_64

--> Processing Dependency: libstdc++-devel for package: oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86                                                                                                                                   _64

--> Running transaction check

---> Package compat-libcap1.x86_64 0:1.10-1 will be installed

---> Package compat-libstdc++-33.x86_64 0:3.2.3-69.el6 will be installed

---> Package gcc-c++.x86_64 0:4.4.7-3.el6 will be installed

---> Package ksh.x86_64 0:20100621-19.el6 will be installed

---> Package libaio-devel.x86_64 0:0.3.107-10.el6 will be installed

---> Package libstdc++-devel.x86_64 0:4.4.7-3.el6 will be installed

--> Finished Dependency Resolution

 

Dependencies Resolved

 

==========================================================================================================

 Package              Arch   Version         Repository                                              Size

==========================================================================================================

Installing:

 oracle-rdbms-server-12cR1-preinstall

                      x86_64 1.0-8.el6       /oracle-rdbms-server-12cR1-preinstall-1.0-8.el6.x86_64  37 k

Installing for dependencies:

 compat-libcap1       x86_64 1.10-1          OL64                                                    17 k

 compat-libstdc++-33  x86_64 3.2.3-69.el6    OL64                                                   183 k

 gcc-c++              x86_64 4.4.7-3.el6     OL64                                                   4.7 M

 ksh                  x86_64 20100621-19.el6 OL64                                                   686 k

 libaio-devel         x86_64 0.3.107-10.el6  OL64                                                    13 k

 libstdc++-devel      x86_64 4.4.7-3.el6     OL64                                                   1.6 M

 

Transaction Summary

==========================================================================================================

Install       7 Package(s)

 

Total size: 7.2 M

Total download size: 7.2 M

Installed size: 23 M

Is this ok [y/N]:y

 

Error Downloading Packages:

  gcc-c++-4.4.7-3.el6.x86_64: failure: Packages/gcc-c++-4.4.7-3.el6.x86_64.rpm from OL64: [Errno 256] No more mirrors to try.

  libstdc++-devel-4.4.7-3.el6.x86_64: failure: Packages/libstdc++-devel-4.4.7-3.el6.x86_64.rpm from OL64: [Errno 256] No more mirrors to try.

  ksh-20100621-19.el6.x86_64: failure: Packages/ksh-20100621-19.el6.x86_64.rpm from OL64: [Errno 256] No more mirrors to try.

  compat-libstdc++-33-3.2.3-69.el6.x86_64: failure: Packages/compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm from OL64: [Errno 256] No more mirrors to try.

  compat-libcap1-1.10-1.x86_64: failure: Packages/compat-libcap1-1.10-1.x86_64.rpm from OL64: [Errno 256] No more mirrors to try.

  libaio-devel-0.3.107-10.el6.x86_64: failure: Packages/libaio-devel-0.3.107-10.el6.x86_64.rpm from OL64: [Errno 256] No more mirrors to try.

 

[root@gckvt287 pr]# yum  install libstdc++-devel-4.4.7-3.el6.x86_64.rpm

Loaded plugins: refresh-packagekit, security

file:///iso/repodata/repomd.xml: [Errno 14] Could not open/read file:///iso/repodata/repomd.xml

Trying other mirror.

Setting up Install Process

Examining libstdc++-devel-4.4.7-3.el6.x86_64.rpm: libstdc++-devel-4.4.7-3.el6.x86_64

Marking libstdc++-devel-4.4.7-3.el6.x86_64.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package libstdc++-devel.x86_64 0:4.4.7-3.el6 will be installed

--> Finished Dependency Resolution

 

Dependencies Resolved

 

=============================================================================================================================================================================================================================================

 Package                                                Arch                                          Version                                               Repository                                                                  Size

=============================================================================================================================================================================================================================================

Installing:

 libstdc++-devel                                        x86_64                                        4.4.7-3.el6                                           /libstdc++-devel-4.4.7-3.el6.x86_64                                        9.3 M

 

Transaction Summary

=============================================================================================================================================================================================================================================

Install       1 Package(s)

 

Total size: 9.3 M

Installed size: 9.3 M

Is this ok [y/N]: y

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : libstdc++-devel-4.4.7-3.el6.x86_64                                                                                                                                                                                        1/1

 

  Verifying  : libstdc++-devel-4.4.7-3.el6.x86_64                                                                                                                                                                                        1/1

 

Installed:

  libstdc++-devel.x86_64 0:4.4.7-3.el6

 

Complete!

 

 

[root@server pr]# yum install gcc-c++-4.4.7-3.el6.x86_64.rpm

Loaded plugins: refresh-packagekit, security

file:///iso/repodata/repomd.xml: [Errno 14] Could not open/read file:///iso/repodata/repomd.xml

Trying other mirror.

Setting up Install Process

Examining gcc-c++-4.4.7-3.el6.x86_64.rpm: gcc-c++-4.4.7-3.el6.x86_64

Marking gcc-c++-4.4.7-3.el6.x86_64.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package gcc-c++.x86_64 0:4.4.7-3.el6 will be installed

--> Finished Dependency Resolution

 

Dependencies Resolved

 

=============================================================================================================================================================================================================================================

 Package                                            Arch                                              Version                                                   Repository                                                              Size

=============================================================================================================================================================================================================================================

Installing:

 gcc-c++                                            x86_64                                            4.4.7-3.el6                                               /gcc-c++-4.4.7-3.el6.x86_64                                             11 M

 

Transaction Summary

=============================================================================================================================================================================================================================================

Install       1 Package(s)

 

Total size: 11 M

Installed size: 11 M

Is this ok [y/N]: y

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : gcc-c++-4.4.7-3.el6.x86_64                                                                                                                                                                                                1/1

  Verifying  : gcc-c++-4.4.7-3.el6.x86_64                                                                                                                                                                                                1/1

 

Installed:

  gcc-c++.x86_64 0:4.4.7-3.el6

 

Complete!

 

 

 

 

 

 

SOURCE :  <http://www.oracle-base.com/articles/12c/oracle-db-12cr1-installation-on-oracle-linux-6.php>

Streams Table Instantiation Rules

 Streams Table Instantiation Rules

• If your streams configuration one-way, table-level; Source to Destination replication, you have to instantiate objects at Destination site.
• If your streasms configuration bi-directional, table-level;  Source to Dest and Dest to Source,  you have to instantiate objects both Source and Destination databases.
• If your streams configuration one-way,schema level;  Source to Destination replication, you have to instantiate SCHEMAS at Destination site.
• If your streasms configuration bi-directional, schema level;  Source to Dest and Dest to Source,  you have to instantiate SCHEMAS both Source and Destination databases.

1. If you forget to instantiate tables or schemas you will get this error message.

ORA-26687: no instantiation SCN provided for "HR"."EMPLOYEE" in source database "SOURCE" ;

2. You could check your error messages from  DBA_APPLY_ERROR  view.  You must control this view attentively especially these columns:

 

ERROR_MESSAGE column,
ERROR_CREATION_TIME column,
LOCAL_TRANSACTION_ID column

SELECT * FROM DBA_APPLY_ERROR where error_message like '%ORA-26687%';

 NOTE : save your error messages before re-execute the transactions. You could save dba_apply_error to excel file.

3. Check the tables or schemas that are really not instantiated  Follow the instructions above and check out the rules.

select * from DBA_APPLY_INSTANTIATED_OBJECTS where source_object_name='EMPLOYEE' AND source_object_owner='HR'

Select * from DBA_APPLY_INSTANTIATED_OBJECTS@SOURCE where source_object_name='EMPLOYEE' AND source_object_owner='HR' ;


4. Stop the Streams Services  on both side. Because replication cause a lock on objects and if its still try to working we couldnt instantiate the objects.

• You could stop the service Oracle Enterprise Manager Console , Click the Data Movement and Under Streams Click Manage Replication. DISABLE ALL SERVICES.

        OR

•    You could stop the services with STRMADMIN account with this script.

exec dbms_capture_adm.stop_capturE(capture_name=>'SOURCE');
exec dbms_apply_adm.stop_apply (apply_name=>'APPLY_SOURCE');
exec DBMS_PROPAGATION_ADM.STop_PROPAGATION(propagation_name=>'PROP_SOURCE');

5.  Instantiating objects with this script table-level. IF you want to instantiate schemas you could use DBMS_APPLY_ADM.set_schema_instantiation_scn procedure.

 

DECLARE
v_scn NUMBER;

BEGIN
v_scn := DBMS_FLASHBACK.get_system_change_number@SOURCE ();

DBMS_APPLY_ADM.set_table_instantiation_scn (

source_object_name => 'HR.EMPLOYEE',

source_database_name => 'SOURCE',

instantiation_scn => v_scn);

END;

/

 

6. Back sto Step 2 and check the error messages which are getting  no instantiation SCN provided error message. Execute these messages from LOCAL_TRANSACTION_ID  with this script until no rows retuned  from DBA_APPLY_ERROR.


exec DBMS_APPLY_ADM.EXECUTE_ERROR ('local_transaction_id');

7. Check the invalid objects and compile them.

Change Bash Prompt

Showing server name  at blogger posts is known problem for bloggers. Especially  if you are beginner blogger you have to face lots of screenshots sharing problem.  Best and practical case you can change your server name with simple this command. This change effects only your session.


command :

$echo $PS1

output :

\\u@\h \\W]\\$


run command : PS1="[\d \t \u@server:\w ] $ "

\d : the date in (Thu Jul 18)  format
\t  : the time in (16:48:35) format
\u : user
\w: current working directory

RMAN INCREMENTAL MERGE

1. How to copy ASM datafile from Primary Database to Standby database on ASM using RMAN ID 605234.1

1. Steps to peform for rolling forward a standby database using RMAN incremental when primary and standby are in ASM filesystem ID 836986.1

1. How to use an incremental Backup to Roll forward the Standby when new datafiles are added to Primary [ID 1275762.1]

Advanced RMAN

Rman is powerfull to for Oracle Backup, it has many features but little known. this post covered 11gR1 and 11gR2.

• Rman Recovery manager tool, could be backup up database, datafile, tablespace and archivelog
• All commands necessary to end with semicolon
• Rman can be used for both hot or cold backups. But database must be archivelog mode for host backups  as you know.

• In 11g rman REDUCE the size of backup with :
• skips the undo data for committed transactions.
• 10g backup 
•  at TAPE = once used blocks (ou) +used now blocks (u) +never used (nu)
• at DISK =  once used blocks (ou) +used now blocks (u)
• 11g backup
• at TAPE =  (o.u)+ (u)
• at DISK  = (u)
• Datafiles can  have many to many backup pices , Backup pices can have many to one Backup sets  and a datafile cant be split accross the backupsets
• 11g RMAN Read and Restore parallel.

• Incremental backups checks for used blocks, so it can check it he blocks have been backed up before.
• It can  backup only changed blocks
• Reducing backup size, time, I/O and CPU
• Level 0 Full Backup
• Level 1 changed since last level 1
•  Incremental doesnt care blocks are logging and data exist on undo or redo.
• Can be compressed.

• %d name of the database
• %D day of the Date
• %M month number
• %Y yerar
• %t time from reference point
• %T YYYYMMDD
• %n tablespace name
• %p backup piece number
• %s backupset number
• %U cover all parameters.

• Block Media Recovery
• 11gR1 recover datafile parameter is deprecated so we must use !
• blockrecover datafile DF# block  BLOCK#;

• Flash Recovery Area
• We have to define 2 initialization parameters;
• db_recovery_file_dest='+ORAFLASH' or '/ORAFLASH'
• db_recovery_file_dest_size = 10G

• Image Copies
• copies all blocks used or not
• command includes as copy
• Image copy backup is simple rman doesnt have to read file and restoring is simplier with no reconstruction
• merge is possible 
• !you tahe a imgae copy backup every sunday and incremental merge every day.
• Compression ?
• you cant backup to tape. you can backup only disk.

• Regular backup
• only used blocks are backed up so size is smaller than image copy
• time is short than image copy
• compression possible
• disk and tape backup is possible

• Compression
• how compressi,on do this;Default algorithm method is BZIP2.
• ABC123
• ABC789
• ABC223 SO GET THE ABC AS SYMBOL & and writes data to disk &123, &789 ....
• show compression algorithm;
• CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; #default;
• There is another algorithm avaible in 11gR1-  ZLIB2 ( cpu usage is less then BZIP2)  but it requires advanced compression option.

• Trace and debug and logging
• You can use trace and debugging command at rman
• rman target=/ debug=all trace=rmantrace1.trc
• end of backup  you can read  #vi rmantrace1.trc
• rman target=/ log=rman1.log ( append is possible )
• rman target=/ log=rman1.log append
• rman target=/ log=rman1.log checksyntax
• v$rman_output
• v$rman_backup_job
• v$rman_backup_job_details

• Duplicating Database
• 10g needs connection to primary and backup for duplicate via rman
• 11gR1 needs connect to primary but backup not necessary from active DB
• 11gR2 from backup alone but no connection to primary needed. ***********

• Health checks (11g)
• RMAN >list failure
• RMAN >advise  failure
• RMAN >repair  failure preview

• Security
• Oracle RMAN can encrypt an entire database backup using one of these three methods:
• Local TDE master encryption key
• Passphrase
• Hybrid  : Passphrase and  Local TDE master encryption key
• Wallet ( alter system set encryption wallet open authenticated by "ilknur";
• you can store table encryption key SYS.ENC$ table.

 Make sure directory
$ORACLE_BASE/admin/$ORACLE_SID/wallet exists
set enc key password : key.sql
open the wallet : wallo.sql
close the wallet : wallo.sql


more information rman backup security: http://www.oracle.com/technetwork/database/security/index-095354.html

RMAN DBID

In different situations you need to know your database dbid. You can learn your dbid easily from connect to to RMAN tool.  You must record your dbid to anywhere.

[oracle@server ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jun 21 13:35:02 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: HR  (DBID=7264534995)
RMAN>

Sometimes disasters have unexpected behaviours so we couldnt find dbid where we recorded it.
But still we need to  restore rman backup,  control file,  spfile, whole database  etc.  We have a small tip for writing dbid to alertlog file.



[oracle@server ~]$  sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 9 15:16:45 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> declare
  2  l_dbid number;
  3  begin
  4  select dbid into l_dbid from v$database;
  5  dbms_system.ksdwrt(2,'DBID='||l_dbid);
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Procedure  "dbms_system.ksdwrt" writes dbid at alertlog file.  This procedure  write messages to alertlog file. I learned from Arup Nanda.

DATA GUARD SECURITY

In this post  I want to point out  new feature  of  Oracle Database 11g  Enterprise Edition that have caught my eye.

Because its mention about "physical standby database security" and sounds good !

With this new feature : You can make log shipping to continue work without copying password file from primary to physical standby.

• You dont have to set  the same SYS password  for production  and standby databases, also you dont have to copy password file on all nodes for Rac enviroments.

• No more  password mismatch error messages, no more un "ORA-16191: Primary log shipping client not logged on standby" error messages.

• You can manage archivelog downstream / realtime downstream via redo transport user.! ( Realtime Datawarehousing ) 

Coding:

Step 1:

sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 21 11:01:26 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL>
SQL>
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 
SQL> create user DG_USER identified by "PSSWD4$" ;
User created.
 
SQL> GRANT SYSOPER TO DG_USER;
Grant succeeded.
 

SQL> show parameter redo_transport_user
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                  string

SQL>
SQL>
SQL> ALTER SYSTEM SET REDO_TRANSPORT_USER='DG_USER' SID='*';
System altered.
SQL>

SQL>  show parameter redo_transport_user
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                  string      DG_USER

SQL>
SQL> select * from V$PWFILE_USERS ;
 
USERNAME                    SYSDB    SYSOP   SYSAS
------------------------------    -----         -----        -----
SYS                                   TRUE     TRUE     FALSE
DG_USER                        FALSE   TRUE      FALSE
 

Step 2 :

[oracle@server]$ cd $ORACLE_HOME/dbs

[oracle@server dbs]$ ls -ltr
-rw-r----- 1 oracle oinstall     2048 Jan 21 10:05 orapwtest

[oracle@server dbs]$ cp orapwtest orapw_backup

[oracle@server dbs]$ orapwd file=orapwtest  password="PSSWD4$"  ignorecase=y entries=5

[oracle@server dbs]$ ls -ltr
-rw-r----- 1 oracle oinstall     2048 Jan 21 10:15 orapwtest


Step 3 ( important notice /  revoke user):

•  If you need to change password for REDO_TRANSPORT_USER (DG_USER) , you need to copy password file from primary to standby. But we are accepting that we dont change redo_transport_user password as often as SYS password.  You check the SEC_CASE_SENSITIVE_LOGON parameter at dataguard pfile  for case sensitive databases.

• If you dont want to redo_transport_user  ; You must revoke sysoper from user and set redo_transport_user parameter to null with alter system command and then drop user.

ref:  ID [1416595.1]

DATABASE SECURITY CHECKLIST - 3

 

33.  Check the users and roles  Who  has  “CREATE ANY DIRECTORY” privilege.

·         select GRANTEE,ADMIN_OPTION  from dba_sys_privs where privilege like '%CREATE ANY DIRECTORY%';

34.  Check the users  who has “CREATE JOB” privilege and discuss. Because creating job privilege can provide schedule  dba’s nightmares.

·         select GRANTEE,ADMIN_OPTION  from dba_sys_privs where privilege like '%CREATE JOB%';

35.  Check the powerful network packages and  control the  PUBLIC  grants. If they were granted to PUBLIC read the oracle support document ID 247093.1 and ID 470366.1 and revoke the privileges from PUBLIC and regrant to necessary schema accounts.

 

Ø  Be Cautious When Revoking Privileges Granted to PUBLIC [ID 247093.1]

Ø  Permissions for Packages DBMS_RANDOM, UTL_FILE, UTL_HTTP, UTL_SMTP and UTL_TCP [ID 470366.1]

·         SELECT grantee,

       table_name,

       privilege,

       grantable

  FROM dba_tab_privs

 WHERE table_name IN

          ('UTL_FILE',

           'UTL_TCP',

           'UTL_HTTP',

           'UTL_SMTP',

           'DBMS_RANDOM',

           'DBMS_LOB',

           'DBMS_SQL',

           'DBMS_SYS_SQL')

     AND grantee = 'PUBLIC';

 

36.  Check the DBMS_SCHEDULER and DBMS_JOB package privileges and revoke them from PUBLIC.

·         select * from dba_tab_privs where table_name ='DBMS_SCHEDULER';

·         select * from dba_tab_privs where table_name ='DBMS_JOB';

 

37.  Check the  users except schema  accounts  who has RESOURCE privileges .

·         Select * from dba_role_privs where granted_role ='RESOURCE' and grantee not in (select username from dba_users where profile ='SCHEMA');

 

38.  Check the users who has CONNECT privileges and if its note necessary REVOKE them.

·         select *  from dba_role_privs where granted_role ='CONNECT' and grantee not in (select username from dba_users where profile ='ORAUSER');

 

39.  Check the critical and administrative  roles  which has password

 

·         select * from sys.user$ where type#=0 and password is not null ;

 

40.  Check these parameters value and set the minimum requirements

·         SELECT distinct limit,resource_name  FROM DBA_PROFILES WHERE RESOURCE_NAME IN ('PASSWORD_LIFE_TIME','FAILED_LOGIN_ATTEMPTS','PASSWORD_GRACE_TIME','PASSWORD_REUSE_MAX')  order by 2 desc;

 

41.  Check the users who has default profile and check limits for  profile default .

 

·         select username from dba_users where profile='DEFAULT' ;

·         select * from dba_profiles where profile ='DEFAULT';

 

42.  Check the SLECT ANY PRIVILEGE  and  If its granted to PUBLIC revoke THEM.

 

·         select grantee,admin_option  from dba_sys_privs where privilege='SELECT ANY TABLE' ;

 

43.  Check the users who has default  password

·         select * from dba_users_with_defpwd;

 

44.  Check the TMP_DIR and TMPDIR directories path and privileges .

·         #cat /home/oracle/.bash_profile |grep TMP*

 

45.  Check the Oracle osuser id and group id , also privileges.

 

46.  07_DICTIONARY_ACCESSIBILITY parameter value is important for system privileges.  Its default = FALSE and not allowed the see sys and system privileges.

 

·         select value from gv$parameter where name='O7_DICTIONARY_ACCESSIBILITY';

 

47.  Control remote os authentication parameter.If its true check the users who can authenticate remotely.

 

·         select value from gv$parameter where name='remote_os_authent'

 

48.  Control the remote os roles parameter .If its true check the roles and granted users.

 

·         select value from gv$parameter where name='remote_os_roles' ;

 

49.  Check the ifile parameter value  and security.

 

50.  Check the users who was created externally.

 

·         select * from dba_users where authentication_type<>'PASSWORD';

 

51.  Check the privileges  INITJVMAUX and  OWA_UTIL packages.  If they granted to public analyze them and revoke from public give the necessary users.

 

·         select table_name,privilege,grantable from dba_tab_privs where table_name in('INITJVMAUX','OWA_UTIL') and grantee='PUBLIC' ;

 

52.  Check the database components and  their status. INVALID states requires reinstalling, deinstalling or compiling  according to Otn documents.  Older version companents requires up to date.

·         select comp_id,version,status from dba_registry;

53.  Check the trace file public parameter and find the optimum value. (pfile)

·         _trace_files_public

 

54.  Check the CREATE EXTERNAL JOB  system privilege and control granted to PUBLIC.

 

·         select grantee,admin_option from dba_sys_privs where privilege='CREATE EXTERNAL JOB' and grantee='PUBLIC';

·          

55.  SEC_RETURN_SERVER_RELEASE_BANNER  and  SEC_MAX_FAILED_LOGIN_ATTEMPS  parameter values are very important. These parameters are new features in 11g.

 

·         select inst_id,name,value from gv$parameter where name in ('sec_return_server_release_banner','sec_max_failed_login_attempts') ;

 

56.  Chek the SEC_CASE_SENSITIVE_LOGON parameter value. It provides case sensitive user and role passwords in 11g .Default value is TRUE.

 

·         select * from gv$parameter where name='sec_case_sensitive_logon';

 

57.  Check the DBMS_XMLQUERY package is granted to PUBLIC.

 

·         SELECT DECODE (grantee, 'PUBLIC', 'TRUE', 'FALSE')

      FROM dba_tab_privs

      WHERE table_name = 'DBMS_XMLQUERY' AND grantee = 'PUBLIC';

58. Check audit sys operation parameter is TRUE or FALSE.

 

·         select inst_id,value from gv$parameter where name='audit_sys_operations';

 

59. Control the all profiles and limit password verify function value. Set the NULL values to Oracle 11g standard VERIFY_FUNCTION. ( which has 8 karakter ,case sensitive, minimum 1 char, 1 number, 1 punction )

 

·         select * from dba_profiles where resource_name='PASSWORD_VERIFY_FUNCTION'  and limit is null;

 

60. Check the  SYS user profile and set the profile ORASYS if its not.  Control ORASYS profile users.

 

·         select profile from dba_users where username='SYS' ;

 

61. Check the audit log level parameter.

 

·         select inst_id,value from gv$parameter where name='audit_syslog_level';