getting ORA-01031: insufficient privileges error while connecting as sysdba

ORA-01031: insufficient privileges

error facing while connecting as sysdba

[oracle@myhostdb lib]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 17 00:43:39 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name: ^C

[oracle@myhostdb lib]$

Go through metalink Note :  Doc ID 435947.1/Doc ID 50507.1

This error coming if there is no dba group created while oracle installation and oracle's DBA default group is something else.

Goto the rdbms/lib location and move config.o with other name and relink the same. 
eg:

[oracle@myhostdb ~]$ cd /orabin/oracle/product/11.2.0.3/rdbms/lib/
[oracle@myhostdb lib]$ ls -ltr config.
config.c         config.c.ouibak  config.o
[oracle@myhostdb lib]$ ls -ltr config.*
-rw-r--r--. 1 oracle oradba  475 Oct 11  2011 config.c.ouibak
-rw-r-----. 1 oracle oradba  475 Jun  6  2012 config.c
-rw-r--r--. 1 oracle oradba 1360 Jun  6  2012 config.o

[oracle@myhostdb lib]$ mv config.o config.o_bak
[oracle@myhostdb lib]$
[oracle@myhostdb lib]$ make -f ins_rdms.mk ioracle
make: ins_rdms.mk: No such file or directory
make: *** No rule to make target `ins_rdms.mk'.  Stop.
[oracle@myhostdb lib]$ make -f ins_rdbms.mk ioracle
chmod 755 /orabin/oracle/product/11.2.0.3/bin

 - Linking Oracle
rm -f /orabin/oracle/product/11.2.0.3/rdbms/lib/oracle
gcc  -o /orabin/oracle/product/11.2.0.3/rdbms/lib/oracle -m64 -L/orabin/oracle/product/11.2.0.3/rdbms/lib/ -L/orabin/oracle/product/11.2.0.3/lib/ -L/orabin/oracle/product/11.2.0.3/lib/stubs/   -Wl,-E /orabin/oracle/product/11.2.0.3/rdbms/lib/opimai.o /orabin/oracle/product/11.2.0.3/rdbms/lib/ssoraed.o /orabin/oracle/product/11.2.0.3/rdbms/lib/ttcsoi.o  -Wl,--whole-archive -lperfsrv11 -Wl,--no-whole-archive /orabin/oracle/product/11.2.0.3/lib/nautab.o /orabin/oracle/product/11.2.0.3/lib/naeet.o /orabin/oracle/product/11.2.0.3/lib/naect.o /orabin/oracle/product/11.2.0.3/lib/naedhs.o /orabin/oracle/product/11.2.0.3/rdbms/lib/config.o  -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /orabin/oracle/product/11.2.0.3/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11  -lrt -lplp11 -lserver11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 `if [ -f /orabin/oracle/product/11.2.0.3/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /orabin/oracle/product/11.2.0.3/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11  -lrt -lplp11 -ljavavm11 -lserver11  -lwwg  `cat /orabin/oracle/product/11.2.0.3/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /orabin/oracle/product/11.2.0.3/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /orabin/oracle/product/11.2.0.3/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /orabin/oracle/product/11.2.0.3/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /orabin/oracle/product/11.2.0.3/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/orabin/oracle/product/11.2.0.3/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11  -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio    `cat /orabin/oracle/product/11.2.0.3/lib/sysliblist` -Wl,-rpath,/orabin/oracle/product/11.2.0.3/lib -lm    `cat /orabin/oracle/product/11.2.0.3/lib/sysliblist` -ldl -lm   -L/orabin/oracle/product/11.2.0.3/lib
test ! -f /orabin/oracle/product/11.2.0.3/bin/oracle ||\
           mv -f /orabin/oracle/product/11.2.0.3/bin/oracle /orabin/oracle/product/11.2.0.3/bin/oracleO
mv /orabin/oracle/product/11.2.0.3/rdbms/lib/oracle /orabin/oracle/product/11.2.0.3/bin/oracle
chmod 6751 /orabin/oracle/product/11.2.0.3/bin/oracle
[oracle@myhostdb lib]$

[oracle@myhostdb lib]$ sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 17 00:43:39 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: ^C
[oracle@myhostdb lib]$

if same error still coming then  change below file with apropiate dba group which is there in your environment. 

[oracle@myhostdb lib]$ cat config.[cs]

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP ""
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};
[oracle@myhostdb lib]$

once changing done

relink the oracle binary

$ORACLE_HOME/bin/relink all 

try to login into the db with sysdba

[oracle@myhostdb lib]$ sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 17 00:58:20 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@myhostdb lib]$

it started working now.

Script to Generate a CREATE DATABASE command from an existing database.

Script to Generate a CREATE DATABASE command from an existing database.

step 1>>

sqlplus /nolog

SQL> connect sys/<password> as sysdba

SQL> @gencrdb

Step 2>>

spool crdb.sql

set pages 1000

set head off

set termout off

set feedback off

set newpage none

set serveroutput on

select ‘CREATE DATABASE ‘||name text from v$database;

– select ‘CONTROLFILE REUSE’ from dual; — optional

select ‘LOGFILE’ from dual;

declare

print_var varchar2(200);

cursor c1 is select member from gv$logfile where inst_id = 1

order by group#;

logfile gv$logfile.member%TYPE;

cursor c2 is select bytes from gv$log where inst_id = 1

order by group#;

bytes number;

lsize varchar2(30);

begin

open c1;

open c2;

for record in (

select group#, count(*) members from gv$logfile where inst_id = 1

group by group#) loop

dbms_output.put_line(print_var);

fetch c2 into bytes;

if mod(bytes,1024) = 0 then

if mod(bytes,1024*1024) = 0 then

lsize := to_char(bytes/(1024*1024))||’M’;

else

lsize := to_char(bytes/1024)||’K’;

end if;

else

lsize := to_char(bytes);

end if;

lsize := lsize||’,’;

if record.members > 1 then

fetch c1 into logfile;

print_var := ‘GROUP ‘||record.group#||’ (‘;

dbms_output.put_line(print_var);

print_var := ””||logfile||””||’,’;

for i in 2..record.members loop

fetch c1 into logfile;

dbms_output.put_line(print_var);

print_var := ””||logfile||””||’,’;

end loop;

print_var := rtrim(print_var,’,’);

dbms_output.put_line(print_var);

print_var := ‘) SIZE ‘||lsize;

else

fetch c1 into logfile;

print_var := ‘GROUP ‘||record.group#||’ ”’||

logfile||””||’ SIZE ‘||lsize;

end if;

end loop;

close c1;

close c2;

print_var := rtrim(print_var,’,’);

dbms_output.put_line(print_var);

end;

/

select ‘MAXLOGFILES ‘||RECORDS_TOTAL from v$controlfile_record_section

where type = ‘REDO LOG’;

select ‘MAXLOGMEMBERS ‘||dimlm from sys.x$kccdi;

select ‘MAXDATAFILES ‘||RECORDS_TOTAL from v$controlfile_record_section

where type = ‘DATAFILE’;

select ‘MAXINSTANCES ‘||RECORDS_TOTAL from v$controlfile_record_section

where type = ‘DATABASE’;

select ‘MAXLOGHISTORY ‘||RECORDS_TOTAL from v$controlfile_record_section

where type = ‘LOG HISTORY’;

select log_mode from v$database;

select ‘CHARACTER SET ‘||value from v$nls_parameters

where parameter = ‘NLS_CHARACTERSET’;

select ‘NATIONAL CHARACTER SET ‘||value from v$nls_parameters

where parameter = ‘NLS_NCHAR_CHARACTERSET’;

select ‘DATAFILE’ from dual;

declare

cursor c1 is select * from dba_data_files

where tablespace_name = ‘SYSTEM’ order by file_id;

datafile dba_data_files%ROWTYPE;

print_datafile dba_data_files.file_name%TYPE;

begin

open c1;

fetch c1 into datafile;

– there is always 1 datafile

print_datafile := ””||datafile.file_name||

”’ SIZE ‘||ceil(datafile.bytes/(1024*1024))||’ M,’;

loop

fetch c1 into datafile;

if c1%NOTFOUND then

– strip the comma and print the last datafile

print_datafile := rtrim(print_datafile,’,’);

dbms_output.put_line(print_datafile);

exit;

else

– print the previous datafile and prepare the next

dbms_output.put_line(print_datafile);

print_datafile := ””||datafile.file_name||

”’ SIZE ‘||ceil(datafile.bytes/(1024*1024))||’ M,’;

end if;

end loop;

end;

/

select ‘;’ from dual;

spool off

– end script

Script to start the Oracle Enterprise Manager 12C

# Start everything.
#!/bin/bash
export ORACLE_SID
export ORACLE_HOME
export OMS_HOME
export AGENT_HOME
or
export ORACLE_SID=<ORACLE SID>
export ORACLE_HOME=<Oracle Home according to your environment>
export OMS_HOME=<OMS Home according to your environment>
export AGENT_HOME=<AGENT  Home according to your environment>
$ORACLE_HOME/bin/dbstart $ORACLE_HOME
$OMS_HOME/bin/emctl start oms
$AGENT_HOME/bin/emctl start agent


# Stop everything
#!/bin/bash
export ORACLE_SID
export ORACLE_HOME
export OMS_HOME
export AGENT_HOME
or
export ORACLE_SID=<ORACLE SID>
export ORACLE_HOME=<Oracle Home according to your environment>
export OMS_HOME=<OMS Home according to your environment>
export AGENT_HOME=<AGENT  Home according to your environment>
$ORACLE_HOME/bin/dbshut $ORACLE_HOME
$OMS_HOME/bin/emctl stop oms -all
$AGENT_HOME/bin/emctl stop agent

How to resize the redo log /How To Maintain and/or Add Redo Logs

A.  How to resize or add redo logs.

1. Review information on existing redo logs.

SQL>  SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group#;

or

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;

    GROUP#    THREAD#    MEMBERS    MB_SIZE STATUS
   ----------      ----------         ----------      ---------- ----------------             1                      1                     2                 50 CURRENT             2                      1                     2                 50 INACTIVE             3                      2                     2                 50 INACTIVE


SQL> alter system checkpoint;



and you want to resize all your groups. Lets say you want to set 100M instead of 50M.


2. Add new groups

ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;



3. Now you should wait till the Group 1/2/3 will start to be INACTIVE so you would be able to drop them. Also you can speed up this process by executing:

4. Check the status on all redo logs again.

SQL>  SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group#;

or

SQL> select GROUP#,MEMBERS,BYTES/1024/1024 MB_Size,status from v$log;


5.  Drop the online redo log groups that are not needed.  You must have the ALTER DATABASE system privilege.

Note: Before dropping an online redo log group, consider the following restrictions and precautions:


a.  An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)

b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.

SQL> alter system switch logfile;


6 .  Check that the group is inactive and archived before dropping it .

SQL > SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;

SQL>ALTER DATABASE DROP LOGFILE GROUP 3;


Sourece:- Doc ID 602066.1

Sync Standby database from incremental backup.

On Primary database only:
1. Find the current SCN of standby database.

select current_scn from v$database;
CURRENT_SCN
———–
4793543

2. On the primary database – take the incremental backup from the above SCN

rman target /
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 4793543 DATABASE FORMAT ‘/orabkp/ABCPROD/stnd_bkup_%U’;

3. create a new standby control file from production
backup current controlfile for standby format ‘/orabkp/ABCPROD/stnd_%U.ctl’;

4. SCP or FTP both the files into standby server:

On Standby database only:

5. On Standby Database: cancel the database from managed recovery mode.

sql> alter database recover managed standby database cancel;
or
sql> recover managed standby database cancel;

6. Start recovering the database
rman target /

RMAN> catalog start with ‘/orabkp/ABCPROD’;

RMAN> recover database noredo;

RMAN> shutdown immediate

RMAN> startup nomount

RMAN> restore standby controlfile from ‘/orabkp/ABCPROD/stnd_%U.ctl’;

RMAN> shutdown immediate

7. Now mount database as a standby database.

SQL> startup nomount
SQL> alter database mount standby database;

8. Now start standby recovery and start db as a physical standby.

SQL> alter database recover managed standby database disconnect from session;

DR Switch Over 

Check and run below on CURRENT PRODUCTION SERVER

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ABCPRD     READ WRITE

SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

connect / as sysdba
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
exit;

After completion of this Run below on Standby Server:

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ABCPRD     MOUNTED

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover standby database;
ORA-00279: change 269114126 generated at 02/15/2013 13:27:20 needed for thread
1
ORA-00289: suggestion : /oradata/ABCPRD/arch/ABCPRD_1_5932_785326766.arc
ORA-00280: change 269114126 for thread 1 is in sequence #5932


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 269114341 generated at 02/15/2013 13:30:31 needed for thread
1
ORA-00289: suggestion : /oradata/ABCPRD/arch/ABCPRD_1_5933_785326766.arc
ORA-00280: change 269114341 for thread 1 is in sequence #5933
ORA-00278: log file '/oradata/ABCPRD/arch/ABCPRD_1_5932_785326766.arc' no longer
needed for this recovery

ORA-00308: cannot open archived log
'/oradata/ABCPRD/arch/ABCPRD_1_5934_785326766.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name , open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ABCPRD    READ WRITE

SQL> alter system set log_archive_dest_state_2=enable;

Actions to be performed on Standby Database(Past Primary):

SQL> shut immediate

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 2471931904 bytes
Fixed Size                  2230872 bytes
Variable Size            1543505320 bytes
Database Buffers          905969664 bytes
Redo Buffers               20226048 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select name, open_mode from  v$database;

NAME      OPEN_MODE
--------- --------------------
ABCPRD       MOUNTED

SQL> select name, log_mode, database_role, open_mode from v$database;
NAME      LOG_MODE     DATABASE_ROLE    OPEN_MODE
--------- ------------ ---------------- --------------------
ABCPRD      ARCHIVELOG   PHYSICAL STANDBY MOUNTED

If standby_file_management set to manual

if standby_file_management set to manual and file got created on prod and not in DR then it will fail with below  error:
Errors in file /orabin/oracle/admin/ABCPRD/diag/diag/rdbms/abcprd_drabcdbstd03/ABCPRD/trace/ABCPRD_mrp0_21744.trc:
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/orabin/oracle/product/11g/dbs/UNNAMED00011'
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/orabin/oracle/product/11g/dbs/UNNAMED00011'

To fix this kind of issue:

SQL> alter database create datafile '/orabin/oracle/product/11g/dbs/UNNAMED00011' as '/oradata/ABCPRD/sysaux05.dbf';

Database altered.

SQL> alter database mount standby database;

SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> !ps -ef |grep mrp
oracle   22988     1  4 18:50 ?        00:00:00 ora_mrp0_ABCPRD
oracle   23012 22894  0 18:50 pts/3    00:00:00 /bin/bash -c ps -ef |grep mrp
oracle   23014 23012  0 18:50 pts/3    00:00:00 grep mrp

Register the database into dataguard:

On primary database only

dgmgrl

connect sys/<password>

show configuration;

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

If it is not configured then

DGMGRL> show configuration

Error: ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

Otherwise:

DGMGRL> show configuration

Configuration

  Name:                abcprod

  Enabled:             YES

  Protection Mode:     MaxPerformance

  Databases:

    fc_abcprod - Primary database

    dr_abcprod - Physical standby database

Fast-Start Failover: DISABLED

Current status for "abcprod":

Warning: ORA-16608: one or more databases have warnings

On DR site:

DGMGRL> show configuration

Configuration

  Name:                abcprod

  Enabled:             YES

  Protection Mode:     MaxPerformance

  Databases:

    fc_abcprod - Primary database

    dr_abcprod - Physical standby database

Fast-Start Failover: DISABLED

Current status for "abcprod":

Warning: ORA-16608: one or more databases have warnings

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

Create new dataguard configuration:

Create configuration ABCPROD as primary database is FC_ABCPROD connect identifier is FC_ABCPROD;

add database DR_ABCPROD as connect identifier is  DR_ABCPROD maintained as physical;

DGMGRL> enable configuration;

DGMGRL> show configuration

Oracle Database Performance Related Initialization Parameter

Parameters which can be affect db performance except memory or SGA related parameter

1) OPEN_CURSORS :
The Oracle initialization parameter OPEN_CURSORS specifies the number of cursors a session can have
open at any one time. The default value is 300. If any session attempts to open a new cursor but already has the maximum number of cursors open, the Oracle error -1000 will be returned.

2) SESSION_CACHED_CURSORS: 
Oracle monitors the SQL statements that are submitted for each session. If it detects the same statement has been submitted multiple times, it moves that statement to cursor cache and keep it open during session run time for subsequent reuse. The SESSION_CACHED_CURSORS parameter controls the number of cursors allowed in the cursor cache.
The default value for SESSION_CACHED_CURSORS varies by Oracle release. If your instance is not configured to cache at least 50 cursors, increase the value of this parameter to 50.

 3)  FAST_START_MTTR_TARGET: 
FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL

4)  DISTRIBUTED_LOCK_TIMEOUT:
DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.

5) DB_FILE_MULTIBLOCK_READ_COUNT:   (Oracle Doc ID 1398860.1)
This parameter determines how many database blocks are read in a single I/O during: 
1) full table scan and
2) Index fast full scan

Optimizer uses this value to calculate the cost of full table scan and index fast full scan. 

Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters we can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

the maximum read I/O request size that Oracle can issue to the OS is 1 Megabyte (1MB). 

Formula to calculate the db_file_multiblock_read_count:
db_file_multiblock_read_count = min(max I/O size/db_block_size , db_cache_size/(sessions * db_block_size))

OR  if you are using a db_block_size of 8192 (8k) and db_file_multiblock_read_count is set to 64 the maximum read size request would be 8192 * 64 = 524,288 bytes or 0.5MB. We could set db_file_multiblock_read_count = 128 to achieve a 1MB read size, but that is the absolute maximum possible.

6) OPTIMIZER_MODE:
Mechanism for result-set retrieval.
A subset of values are available: ALL_ROWS, FIRST_ROWS, FIRST_ROWS_N

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
first_rows_n : The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows: The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
all_rows :The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

7) CURSOR_SHARING:    CURSOR_SHARING=EXACT (default)  Oracle 


CURSOR_SHARING determines what kind of SQL statements can share the same cursors. Converts literal SQL to SQL with bind variables, reducing parse overhead.

its values would be : 

FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SELECT * FROM hr.employees WHERE employee_id = 101

If we use FORCE , then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan.

SIMILAR (Removed from 12c onwards) behaving similar to FORCE in 11.2.0.3

Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement. or the degree to which the plan is optimized.

EXACT
if a statement in the pool is not identical, then the database does not share the SQL area. Each statement has its own parent cursor and its own execution plan based on the literal in the statement.

8) FILESYSTEMIO_OPTIONS + DISK_ASYNCH_IO :

There are 4 options available for filesystemio_options:  ASYNC, DIRECTIO, SETALL, NONE
and 2 options for DISK_ASYNC_IO :  TRUE and FALSE


I/O operations in UNIX and Linux systems typically go through the file system cache, this extra processing does require resources. 
Bypassing the file system cache reduces CPU requirements, and frees up the file system cache for other non-database file operations.
Operations against raw devices automatically bypass the file system cache.

In Synchronus I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing processing. where as in sychronus I/O processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass some of the performance bottlenecks associated with I/O operations.

FILESYSTEMIO_OPTIONS=SETALL
DISK_ASYNCH_IO=TRUE

When both parameters are activated to use asynchronous I/O, dbw0 process performs the I/O asynchronously

FILESYSTEMIO_OPTIONS=NONE
DISK_ASYNCH_IO=TRUE

FILESYSTEMIO_OPTIONS is set to NONE. This should normally disable the DISK_ASYNCH_IO when datafiles are located on a regular filesystem (non-ASM). However, since datafiles are located on ASM, FILESYSTEMIO_OPTIONS has no affect in this case. I/O is asynchronous.


FILESYSTEMIO_OPTIONS=NONE
DISK_ASYNCH_IO=FALSE
and 
FILESYSTEMIO_OPTIONS=SETALL
DISK_ASYNCH_IO=FALSE

In the last two cases, whatever the filesystemio_options is set, if DISK_ASYNCH_IO is false, IO is done synchronously.

9) OPTIMIZER_INDEX_COST_ADJ:
Adjustment between the cost of an index scan and the cost of a full table scan. Set between 1 and 10 to force index use more frequently. Setting this parameter to a value between 1 and 10 pretty much guarantees index use, however, even when not appropriate

The following select will extract the majority of parameters relevent to query optimization

col name format a35

col value format a40

SELECT name, value

FROM v$parameter 

WHERE name like 'optimizer%' 

OR name like 'parallel%' 

OR name in ('cursor_sharing', 

'db_file_multiblock_read_count',

'hash_area_size', 

'hash_join_enabled',

'query_rewrite_enabled',

'query_rewrite_integrity',

 'sort_area_size', 

'star_transformation_enabled',

'bitmap_merge_area_size', 

'partition_view_enabled') 

ORDER BY name; 

Manual Creation of Database for RAC and Non RAC in 11gR2 environments steps wise.

1)       Create pfile in $ORACLE_HME/dbs 

2)       Create all the required directory in system.

a.       adump

b.       cdump

c.        diag

3)       create password file if required.

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw<SID> password=xxxxxx entries=5

4)       Check the FS where you are going to create datafile

5)       Create database as like below:

create database <Instance Name>

    USER SYS IDENTIFIED BY <password>

    USER SYSTEM IDENTIFIED BY <password>

    MAXLOGFILES 10

    MAXLOGMEMBERS 2

    MAXDATAFILES 200

    MAXINSTANCES 1

    MAXLOGHISTORY 1

logfile

        group 1 ('</FS> /<Instance>/redo/redo1a.log','</FS> /<Instance>/redo/redo1b.log') size 400M,

        group 2 ('</FS> /<Instance>/redo/redo2a.log','</FS> /<Instance>/redo/redo2b.log') size 400M,

        group 3 ('</FS> /<Instance>/redo/redo3a.log','</FS> /<Instance>/redo/redo3b.log') size 400M,

        group 4 ('</FS> /<Instance>/redo/redo4a.log','</FS> /<Instance>/redo/redo4b.log') size 400M

datafile  '</FS> /<Instance>/data/system01.dbf' size 1000M

extent management local

sysaux datafile '</FS> /<Instance>/data/sysaux01.dbf' size 1000M

undo tablespace UNDOTBS1

datafile '</FS> /<Instance>/data/undotbs01.dbf' size 5000M

default temporary tablespace temp tempfile '</FS> /<Instance>/data/temp01.dbf' size 5000M

character set  AL32UTF8

national character set AL16UTF16;

6)       Run below scripts to create data dictionary.

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

connect system/manager

@?/sqlplus/admin/pupbld

For RAC there is one more script that need to run to create RAC Dictionary.

@?/rdbms/admin/catclust.sql

connect system/manager

@?/sqlplus/admin/pupbld

Conn /as sysdba

@?/rdbms/admin/utlrp.sql

Setting up database to using java

@?/javavm/admin/initjvm 

To enable the database in cluster mode:

create spfile='<Spfile_path_RAW_device>.ora' from pfile;

shutdown immediate;
startup;

alter system set cluster_database=true scope=spfile; 

(We can also update the init file with cluster enable with true option  and then create spfile)

shutdown immediate;

startup;

alter database add logfile thread 2

group 5  ('</FS> /<Instance>/redo/redo6a.log','</FS> /<Instance>/redo/redo6b.log') size 400M,

group 6 ('</FS> /<Instance>/redo/redo6a.log','</FS> /<Instance>/redo/redo6b.log') size 400M,

group 7 ('</FS> /<Instance>/redo/redo7a.log','</FS> /<Instance>/redo/redo7b.log') size 400M,

group 8 ('</FS> /<Instance>/redo/redo8a.log','</FS> /<Instance>/redo/redo8b.log') size 400M


alter database enable public thread 2;

create undo tablespace UNDOTBS2 datafile '</FS> /<Instance>/data/undotbs01.dbf' size 5000M

Register the database in cluster:

srvctl add database -d <DB_NAME> -o <$ORACLE_HOME>
srvctl add instance -d <DB_NAME> -i <Instance_Name> -n rac1
srvctl add instance -d <DB_NAME>  -i <Instance_Name> -n rac2
srvctl enable database -d <DB_NAME>
srvctl enable instance -d <DB_NAME>  -i <Instance_Name>
srvctl enable instance -d <DB_NAME>  -i <Instance_Name>

srvctl remove instance -d  <DB_NAME>  -i <Instance_Name>

srvctl remove instance -d  <DB_NAME>  -i <Instance_Name>

srvctl remove database -d <DB_NAME>  -o <$ORACLE_HOME>

Physical Standby Database using RMAN Backup and Restore 

1)      Check init parameters file:

Some important parameters that need to check

For Primary Database:

*.db_name='ABCPROD'

*.DB_UNIQUE_NAME='FC_ABCPROD'

*.fal_client='FC_ABCPROD'

*.fal_server='DR_ABCPROD'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(FC_ABCPROD,DR_ABCPROD)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/ABCPROD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=FC_ABCPROD'

*.LOG_ARCHIVE_DEST_2='SERVICE=DR_ABCPROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DR_ABCPROD'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='ABCPROD_%t_%s_%r.arc'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*******New Parameter************

*.DG_BROKER_START=true

alter system set DG_BROKER_START=true scope=both;

System altered.

For Standby Database:

*.db_name='ABCPROD'

*.DB_UNIQUE_NAME='DR_ABCPROD'

*.FAL_CLIENT='DR_ABCPROD'

*.FAL_SERVER='FC_ABCPROD'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(FC_ABCPROD,DR_ABCPROD)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/ABCPROD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= DR_ABCPROD'

*.LOG_ARCHIVE_DEST_2='SERVICE=DR_ABCPROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=FC_ABCPROD'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.log_archive_format='ABCPROD_%t_%s_%r.arc'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.DG_BROKER_START=true

2)      Check Directory Structure:

If Primary and Standby database directory structure is same then no Action required.

Otherwise need to create same directory structure on DR server as well.

3)            Check Connectivity from each other:

Edit Tns Entry in both the server to make sure they will communicate to each other:

As per Gilead setup for DR listener must be running through listener.ora

4)            Check Rman backup:

Check and validate rman backup, it should be successful.

If till point 4 every thing is fine then we are good to create physical standby  database through rman backup and restore.

5)            Create password file and Start Standby database in nomount mode.

orapwd file=$ORACLE_HOME/dbs/orapwABCPROD password=oracle entries=5

sqlplus “/as sysdba”

startup nomount

6)            Move backup pieces to DR server.

Make a tarball of all  backup pieces and zip that tar file. It will be very small file and will fast transfer to DR location.

Either use scp or ftp to transfer.

Start restoration from below script.

[oracle@drabcdbval01 oradata]$ cat /home/oracle/RESTORE/stdby_restore.sh

DATE=`date '+%Y%m%d'`

DATETIME=`date '+%Y%m%d-%H%M'`

export ORAENV_ASK=NO

export ORACLE_SID=ABCPROD

export PATH=/usr/local/bin:$PATH

#. /usr/local/bin/oraenv

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"

rman log="/home/oracle/RESTORE/stdby_restore.log" << EOF

connect target /

run {

restore controlfile from '/orabkup/${ORACLE_SID}/${ORACLE_SID}_cf_%F ';

sql 'alter database mount';

set until time "to_date('11-FEB-13 05:28:00','DD-MON-YY HH24:MI:SS')";

allocate channel t1 device type disk format '/orabkup/ABCPROD/${ORACLE_SID}_dbf_%t_%s_%p';

restore database ;

release channel t1;

}

EOF

Exit;

7)            Above script will open database in mount state, but no need  to open it. It may need some archlog so if that needed restore it from tape but don’t start  recovery.

[oracle@drabcdbval01 oradata]$ cat restoredbarch.sh

DATE=`date '+%Y%m%d'`

DATETIME=`date '+%Y%m%d-%H%M'`

export ORAENV_ASK=NO

export ORACLE_SID=ABCPROD

export PATH=/usr/local/bin:$PATH

#. /usr/local/bin/oraenv

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"

rman log="/home/oracle/RESTORE/rman_restore_arch.log" << EOF

connect target /

run {

CATALOG START WITH '/orabkup/ABCPROD/BAKUP_19th';

RESTORE ARCHIVELOG FROM SEQUENCE 434 UNTIL SEQUENCE 435';

}

8)            Create standby Controlfile from production (Primary) database and scp same in standby server /orabkup direcoty.

alter database  create standby controlfile as ‘/orabkup/ABCPROD/abcprod_standby.ctl’;

scp /orabkup/ABCPROD/abcprod_standby.ctl  drabcdbval01:/orabkup/ABCPROD/.

9)            On Standby server shut down the database and move both controlfile with different name and copy standby controlfile from /orabkup/ABCPROD

cd /oradata/ABCPROD

mv control01.ctl  control01.ctl.old

mv control02.ctl  control02.ctl.old

mv control03.ctl  control03.ctl.old

cp  /orabkup/ABCPROD/abcprod_standby.ctl  .

cp abcprod_standby.ctl  control01.ctl

cp abcprod_standby.ctl  control02.ctl

cp abcprod_standby.ctl  control03.ctl

10)        Now mount database as a standby database.

startup nomount

alter database mount standby database;

11)        Now start standby recovery and start db as a physical standby.

alter database recover managed standby database disconnect from session;

12)        Check mrp process

[oracle@drabcdbval01 ABCPROD]$ ps -ef |grep mrp

oracle    7250     1  0 Feb12 ?        00:00:00 ora_mrp0_ABCPROD

oracle   14780 14471  0 12:24 pts/1    00:00:00 grep mrp

There is other way to create physical standby from RMAN:

After Step #3

4)   If it is adhoc and need to take backup then use as per below:

From crontab : run below script.

############################################################################

# Full RMAN Database backup

00 5 * * * /oradba/backup/rman_bkp_fulldb.sh ABCPROD >/dev/null 2>&1

Or below script:

#!/bin/ksh

DATE=`date '+%Y%m%d'`

DATETIME=`date '+%Y%m%d-%H%M'`

export ORAENV_ASK=NO

export ORACLE_SID=ABCPROD

export PATH=/usr/local/bin:$PATH

. /usr/local/bin/oraenv

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"

rman log="/orabkup/${ORACLE_SID}/rman_${ORACLE_SID}_$DATE.log" << EOF

connect TARGET  /

run {

allocate channel d1 type disk;

configure controlfile autobackup on;

set controlfile autobackup format for device type disk to '/orabkup/${ORACLE_SID}/${ORACLE_SID}_cf_%F';

setlimit channel d1 kbytes 2097200 maxopenfiles 32 readrate 200;

backup

format '/orabkup/${ORACLE_SID}/${ORACLE_SID}_dbf_%t_%s_%p'

database;

sql 'alter system archive log current';

crosscheck archivelog all;

backup

format '/orabkup/${ORACLE_SID}/${ORACLE_SID}_arc_%t_%s_%p'

archivelog all

delete all input;

release channel d1;

}

exit;

EOF

Run full database RMAN backup manually with controlfile for standby

Below is the script:

[oracle@drabcdbval01 oradata]$ cat backupstdby.sh

#!/bin/ksh

DATE=`date '+%Y%m%d'`

DATETIME=`date '+%Y%m%d-%H%M'`

export ORAENV_ASK=NO

export ORACLE_SID=ABCPROD

export PATH=/usr/local/bin:$PATH

. /usr/local/bin/oraenv

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"

rman log="/orabkup/${ORACLE_SID}/rman_${ORACLE_SID}_$DATE.log" << EOF

$ORACLE_HOME/bin/rman log=backupstandby_${log_dt}.log << EOF

set echo on

connect target /

run {

set controlfile autobackup format for device type disk to '/orabkup/${ORACLE_SID}/${ORACLE_SID}_cf_%F';

allocate channel d1 type disk maxpiecesize=2000M;

allocate channel d2 type disk maxpiecesize=2000M;

allocate channel d3 type disk maxpiecesize=2000M;

setlimit channel d1 kbytes 2097200 maxopenfiles 32 readrate 200;

backup

format '/orabkup/${ORACLE_SID}/${ORACLE_SID}_dbf_%t_%s_%p'

database include current controlfile for standby;

sql 'alter system archive log current';

crosscheck archivelog all;

backup

format '/orabkup/${ORACLE_SID}/${ORACLE_SID}_arc_%t_%s_%p'

archivelog all

release channel d1;

release channel d2;

release channel d3;

}

EOF

exit;

5)            After its successful completion we are good to restore it in standby server.

6)            Start Standby database in nomount mode.

7)            Start restoration, But it is not normal restore, we are creating duplicate database for standby.

Use Below script:

[oracle@drabcdbval01 oradata]$ cat restoredbstb.sh

export NLS_DATA_FORMAT='DD-MON-YY HH24:MI:SS'

log_dt=`date '+%d%m'`

echo "Restore started at `date` "

$ORACLE_HOME/bin/rman log=standby_restore_rman_${log_dt}.log <<EOF

connect target sys/<password>@prim_<DB Name>

connect AUXILIARY /

run {

allocate auxiliary channel d1 type disk;

allocate auxiliary channel d2 type disk;

allocate auxiliary channel d3 type disk;

allocate auxiliary channel d4 type disk;

set until time "to_date('11-FEB-13 05:28:00','DD-MON-YY HH24:MI:SS')";

DUPLICATE TARGET DATABASE FOR STANDBY dorecover nofilenamecheck;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

EOF

exit;

8)            Here we need not to recover the database, all archive file will start transferring to its standby location.

9)            Check the database status, it should be in mount mode.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

ABCPROD   MOUNTED

10)      Skip this point as per above method

11)      Skip this point as per above method

12)        Put the standby database in managed recovery mode.

alter database recover managed standby database disconnect from session;

13)        Now check MRP process:

[oracle@drabcdbval01 ABCPROD]$ ps -ef |grep mrp

oracle    7250     1  0 Feb12 ?        00:00:00 ora_mrp0_ABCPROD

oracle   14780 14471  0 12:24 pts/1    00:00:00 grep mrp

In Both Method:

14)        On Standby Database, use below commands to check the DR status :

SQL> select name,log_mode,database_role,open_mode from v$database;

NAME      LOG_MODE     DATABASE_ROLE    OPEN_MODE

--------- ------------ ---------------- --------------------

ABCPROD   ARCHIVELOG   PHYSICAL STANDBY MOUNTED

SQL> select PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE# from V$MANAGED_STANDBY;

PROCESS   STATUS       CLIENT_P  SEQUENCE#

--------- ------------ -------- ----------

ARCH      CONNECTED    ARCH              0

ARCH      CONNECTED    ARCH              0

MRP0      WAIT_FOR_LOG N/A             647

RFS       IDLE         ARCH              0

RFS       IDLE         LGWR            647

RFS       IDLE         UNKNOWN           0

RFS       IDLE         UNKNOWN           0

9 rows selected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

646

SQL> select TYPE,ARCHIVED_THREAD#,ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# from V$ARCHIVE_DEST_STATUS;

TYPE           ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#

-------------- ---------------- ------------- --------------- ------------

LOCAL                         0             0               0            0

LOCAL                         0             0               0            0

LOCAL                         0             0               0            0

LOCAL                         0             0               0            0

LOCAL                         0             0               0            0

LOCAL                         0             0               0            0

UNKNOWN                       1           646               1          646

15)        On primary Database:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

647

SQL> select name,log_mode,database_role,open_mode from v$database;

NAME      LOG_MODE     DATABASE_ROLE    OPEN_MODE

--------- ------------ ---------------- --------------------

ABCPROD   ARCHIVELOG   PRIMARY          READ WRITE

SQL> select TYPE,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ# from   V$ARCHIVE_DEST_STATUS;

TYPE           ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#

-------------- ---------------- ------------- --------------- ------------

LOCAL                         1           647               0            0

PHYSICAL                      1           647               1          646

LOCAL                         1           647               0            0

LOCAL                         1           647               0            0

LOCAL                         1           647               0            0

LOCAL                         1           647               0            0

NOTE:

Primary :  select max(sequence#) from v$archived_log;

Standby :  select max(sequence#) from v$archived_log;

Both output must be same or  at least 1 one less in Standby, if the Output gap margin is large then it means primary and standby are not in sync with each other.