Thursday, July 23, 2015

Connecting with a user which has SYSDBA privilege, you act like SYS user

bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 23 11:12:10 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL>
SQL>
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>
SQL>

SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL>
SQL>
SQL>
SQL> create user admin identified by admin;

User created.

SQL> grant sysdba to admin;

Grant succeeded.

SQL> grant dba to admin;

Grant succeeded.

SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
ADMIN                          TRUE  FALSE FALSE

SQL>

SQL>
SQL> grant sysoper to admin;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
ADMIN                          TRUE  TRUE  FALSE


SQL>conn admin/admin
SQL> show user
USER is "ADMIN"
SQL>
SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         5 UNDOTBS2                       YES NO  YES

6 rows selected.

but when you go to shutdown  databasethen use sysdba role

SQL>
SQL> conn admin/admin as sysdba
Connected.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> conn admin/admin as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL>
SQL>

if use without  as sysdba so you got ora error

SQL>
SQL> conn admin/admin
Connected.
SQL>
SQL> shut immediate
ORA-01031: insufficient privileges
SQL>


About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Wednesday, July 22, 2015

OS Authentication on Oracle Server in 11gr2



Connect as sysdba

should be oracle user and os user same
in my case oracle is os user

SQL>
SQL>
SQL>
SQL> create user "OPS$ORACLE" identified by externally;

User created.

SQL> grant connect,resource to "OPS$ORACLE";

Grant succeeded.

SQL> show parameter auth

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ldap_directory_sysauth               string      no
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     FALSE
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.2$
bash-3.2$
bash-3.2$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 22 12:06:26 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "OPS$ORACLE"
SQL>

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Wednesday, July 15, 2015

Oracle Error: ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [4], [18313], [18410], [], [], [], [], [], [], []

oracle 11gr2,solaris10

After Power Fail Alter database open fails with

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]


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

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[4], [18313], [18410], [], [], [], [], [], [], []


SQL> shut abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  647204864 bytes
Fixed Size                  2213816 bytes
Variable Size             423626824 bytes
Database Buffers          218103808 bytes
Redo Buffers                3260416 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1046055 generated at 07/15/2015 11:46:31 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/TEST11G/archivelog/2015_07_15/o1_mf_1_4_%u_.arc
ORA-00280: change 1046055 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/TEST11G/archivelog/2015_07_15/o1_mf_1_4_%u_.arc
'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/TEST11G/archivelog/2015_07_15/o1_mf_1_4_%u_.arc
'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1046055 generated at 07/15/2015 11:46:31 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/TEST11G/archivelog/2015_07_15/o1_mf_1_4_%u_.arc
ORA-00280: change 1046055 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           1
SQL>
SQL>

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Wish you happy diwali 2026