Password reset

DBMS_RANDOM package can be used to generate an alphanumerical string that can be used to reset a password.

Example 1:

SELECT INITCAP(DBMS_RANDOM.STRING('X',8)) FROM DUAL;

NEW_PWD
-----------------------------------------------------------------
Xzcf996c

Example 2:

 
SELECT DBMS_RANDOM.STRING('A',5)||ROUND(DBMS_RANDOM.VALUE(100,999),0) FROM DUAL;

NEW_PWD
----------------------------------------------------------------------------------------------------------------------------
cPwER827

Standby Database Issue - Part 2

Issue 1:

 PING[ARC0]: Heartbeat failed to connect to standby  'stand'. Error is 1031        

Reason for the above error:
Standby database password file was missing.

Solution:
Copy the primary database password file to standby server.


Issue 2:

 PING[ARC0]: Heartbeat failed to connect to standby 'stand'. Error is 16047         

Reason for the above error:
For LOG_ARCHIVE_DEST_2 db_unique_name was wrongly specified.
LOG_ARCHIVE_DEST_2='service=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=prim'

Solution:
Changed db_unique_name from 'prim' to 'stand'
LOG_ARCHIVE_DEST_2='service=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=stand'



Issue 3: While creating spfile from pfile got the below error:

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

Solution:
Metalink DOC: 444917.1

Creating spfile/pfile from memory

Now we can even create a spfile/pfile from memory. It is a new feature in the 11g. Prior to 11g, we were able to create spfile from pfile or pfile from spfile.


Syntax:

CREATE SPFILE FROM MEMORY;
CREATE PFILE FROM MEMORY;

Examples:

SYS@orcl11g>CREATE SPFILE='D:\backup\ORCL11G\spfile_bkp_18Jan2010.ora' FROM MEMORY;

File created.

SYS@orcl11g>CREATE PFILE='D:\backup\ORCL11G\pfile_bkp_18Jan2010.ora' FROM MEMORY;

File created.

SYS@orcl11g>

Read-Only Table

In Oracle 11g we can make a table read-only. It is a new feature in the Oracle 11g. Earlier we were able to make a non-system tablespace read-only.The Oracle 11g goes one step further and allows us to make a table read-only.

We have a new column called 'read_only' in dba_tables, user_tables and all_tables views which tells whether a table is read only or not.

We will not be able to execute DML statements like INSERT,UPDATE and DELETE on a read-only table. And all so we cannot add/remove columns.

Syntax:

  
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

Examples:

 
SCOTT@orcl11g>DROP TABLE test_tbl;

Table dropped.


SCOTT@orcl11g>CREATE TABLE test_tbl AS SELECT * FROM dual;

Table created.


SCOTT@orcl11g>SELECT * FROM test_tbl;

D
-
X


SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');

1 row created.


SCOTT@orcl11g>ALTER TABLE test_tbl READ ONLY;

Table altered.


SCOTT@orcl11g>SELECT table_name,read_only FROM user_tables WHERE table_name='TEST_TBL';

TABLE_NAME                     READ_ONLY
------------------------------ ----------
TEST_TBL                       YES


SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');
INSERT INTO test_tbl values ('A')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>UPDATE test_tbl SET dummy='Y' WHERE dummy='X';
UPDATE test_tbl SET dummy='Y' WHERE dummy='X'
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>DELETE FROM test_tbl;
DELETE FROM test_tbl
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl ADD (name varchar2(10));
ALTER TABLE test_tbl ADD (name varchar2(10))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl DROP COLUMN name;
ALTER TABLE test_tbl DROP COLUMN name
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl READ WRITE;

Table altered.


SCOTT@orcl11g>INSERT INTO test_tbl VALUES('A');

1 row created.

SCOTT@orcl11g>

Case_sensitive passwords

In Oracle 11g passwords are case sensitive by default. We can enable or disable this feature by setting the SEC_CASE_SENSITIVE_LOGON parameter.
The SEC_CASE_SENSITIVE_LOGON parameter is a dynamic parameter. In the earlier version of the oracle like 10g & 9i passwords were not case sensitive.

 
SYS@orcl11g>show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

Examples:

 
SYS@orcl11g>alter user scott identified by Tiger account unlock;

User altered.

SYS@orcl11g>show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SYS@orcl11g>exit

sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 10:56:52 2010

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:


sqlplus scott/Tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 11:05:54 2010

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


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

SCOTT@orcl11g>

sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 10:57:30 2010

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


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

SYS@orcl11g>alter system set SEC_CASE_SENSITIVE_LOGON=false scope=both;

System altered.

SYS@orcl11g>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 18 10:57:52 2010

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


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

SCOTT@orcl11g>