setting password for listener in oracle

Most of the time in IT Audit, one issue always raise  "No password is set for listener" .

Today I will show how to set password for listener.

There are 3 types in lsnrctl status for Security

1) ON: Local OS Authentication
2) ON: Password or Local OS Authentication
3) ON: Password

Will see below steps to configure password on listener.


LSNRCTL>status

Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication

check Security

LSNRCTL>change_password
LSNRCTL>save_config

 in listener.ora you will get following things

 #----ADDED BY TNSLSNR 14-OCT-2014 18:33:11---
PASSWORDS_LISTENER = 18463FBDF70465CE
#--------------------------------------------


LSNRCTL>status

Uptime                    0 days 0 hr. 1 min. 31 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication

Now Security : password + OS authentication

Oracle 10g, operating system authentication for the listener has been implemented.
The listener checks to make sure you are part of the privileged dba group,
and if so it will grant you access to change the password, save configuration.

Remove OS authentication if required?

shutdown listener

put following in listener.ora
LOCAL_OS_AUTHENTICATION_LISTENER=OFF


LSNRCTL>status

Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Password

now listener will work only through passsword

LSNRCTL>status    (gives error)
TNS-01169: The listener has not recognized the password.

how to logging through listner

LSNRCTL>set password

LSNRCTL> status   ( it will execute )

Cold backup with RMAN

Cold backup with RMAN

Scenario 1:
Most of the time Interviewers ask candidates, I want to take cold backup with RMAN, can I?
And 90% candidates give answer as "No sir, we cant take cold backup with RMAN".

Scenario 2:
Though 10% of people give above answer correct, they fail by commenting "For RMAN backup DB always must be in archivelog mode"

In fact, we can take cold backup with RMAN no matter whether your DB is in archivelog mode or not.

Quick steps:
Backup

1) Shutdown DB  
2) Startup mount
3) RMAN>backup database   (make sure, controlfile autoback is on / or use COPY CURRENT CONTROLFILE TO 'location';)
4) Alter database open;
5) Create object 2305 in scott schema.
6)shut immediate;

Recovery steps
7) startup nomount;
8) restore controlfile from '';
9) alter database mount;
10) restore database;
11) sql> recover database using backup controlfile until cancel;
12) alter database open resetlogs;

transactions after step 3: wont be recover, if we dont have archivelog.



C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:52:29 2014

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Current log sequence           1
SQL>
SQL>
SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exiit
SP2-0042: unknown command "exiit" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

C:\Users\oradb>rman target sys/orcl1@orcl1

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:53:28 2014

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     348127232 bytes

Fixed Size                     1248936 bytes
Variable Size                 83886424 bytes
Database Buffers             260046848 bytes
Redo Buffers                   2945024 bytes

RMAN> backup database;

Starting backup at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NCSNF_TAG20140523T125343_9QXX
WV57_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAY-14

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.

C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:54:37 2014

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> create table scott.test2305 as select * from scott.emp;

Table created.

SQL> select count(*) from scott.test2305;

  COUNT(*)
----------
         0

SQL>
SQL>
SQL>
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
         0

SQL>
SQL>
SQL> select * from dba_objects where object_name='TEST2305';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
SCOTT
TEST2305
                                    56376          56376 TABLE
23-MAY-14 23-MAY-14 2014-05-23:12:55:03 VALID   N N N


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:56:41 2014

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  348127232 bytes
Fixed Size                  1248936 bytes
Variable Size              83886424 bytes
Database Buffers          260046848 bytes
Redo Buffers                2945024 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

C:\Users\oradb>rman target sys/orcl1@orcl1

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:56:56 2014

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

connected to target database: ORCL1 (DBID=1227596891, not open)

RMAN> restore database;

Starting restore at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1
_MF_NNNDF_TAG20140523T125343_9QXXW0CJ_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 23-MAY-14

RMAN> restore database using backup controlfile until cancel;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "using": expecting one of: "archivelog, channel, check, controlfile, clone, database, da
tafile, device, from, force, high, (, preview, ;, skip, spfile, standby, tablespace, until, validate"
RMAN-01007: at line 1 column 18 file: standard input

RMAN> exit


Recovery Manager complete.

C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:57:51 2014

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3042883 generated at 05/23/2014 12:52:58 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_05_23\O1_MF_1
_1_%U_.ARC
ORA-00280: change 3042883 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
         0

SQL> select count(*) from scott.test2305;
select count(*) from scott.test2305
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

Oracle Universal Installer (OUI) Silent Installations

Oracle Universal Installer (OUI) Silent Installations


1) Create response file of Oracle installation

To create a response file, start the OUI with the following command and perform an installation as normal.

./runInstaller -record -destinationFile /tmp/10gR2.rsp

The "-record" parameter tells the installer to write to the response file and
the "-destinationFile" parameter defines the name and location of the response file.


2) Execute below command to invoke Oracle silent installation
A silent installation is initiated using the following command.

./runInstaller -silent -responseFile /tmp/10gR2.rsp


Soon updates will be uploaded for Silent Installations.. 

Password verify function in Oracle

Demonstration

setting Password_verify_function in Oracle


1) Create user:

SQL> create user shreyas identified by shreyas;

User created.


2) For password verify function execute below file from location ($ORACLE_HOME/rdbms/admin)

SQL> @/u01/oracle/product/10.2.0/db_1/rdbms/admin/utlpwdmg.sql;

Function created.


Profile altered.

3) Check whether function got created or not.

SQL> select object_name from dba_objects where object_type='FUNCTION' and object_name like 'VERIFY%';

OBJECT_NAME
--------------------------------------------------------------------------------
VERIFY_FUNCTION


4) check users profile

SQL> select username,profile from dba_users where username='shreyas';

USERNAME                       PROFILE
------------------------------ ------------------------------
shreyas                      DEFAULT


5) alter default profile and enabled passowrd verify function

SQL> alter profile default limit password_verify_function verify_function;  

Profile altered.


6) Now try to change password ( here I am passing password as "pass")
SQL> password shreyas
Changing password for shreyas
New password:                                                   (password=pass)
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one character and one
punctuation

Password unchanged



/////Disable Verify_function//////////

1) Make password_verify_function null in profile

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.


2) Now try to change password ( here I am passing password as "pass")
SQL> password shreyas
Changing password for shreyas
New password:                                                                  (password=pass)
Retype new password:
Password changed

It allowed me to change password.

Note: Kindly test 1st in UAT.