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