Database RAC Dataguard RMAN Performance Tuning Feedback
Database Migration & Upgradation
DataGuard
ASM & RAC
Exadata
RMAN Backup & Recovery
Oracle Cloud AWS Azure Google Cloud
DevOps Ansible Kubernetes Terraform
Shell Scripting Powershell Scripting
Infomratica Others

Wednesday, October 27, 2021

Oracle Database - Connecting to CDB/PDB with OS Authentication Vs Password Authentication

OS Authentication Vs Password Authentication


How to connect to CDB?
How to connect to PDB?

How to connect to CDB with OS authentication?
How to connect to CDB with Password authentication?

How to connect to PDB with OS authentication?
How to connect to PDB with Password authentication?

Solution:
export ORACLE_PDB_SID=TESTPDB

1. Verify the environment 

[root@oraclelab2 ~]# ps -ef|grep smon
oracle     651     1  0 Oct20 ?        00:00:10 ora_smon_TESTCDB
oracle    4833     1  0 Oct21 ?        00:00:08 ora_smon_DEVCDB
root     12108 12034  0 01:48 pts/0    00:00:00 grep --color=auto smon
oracle   32650     1  0 Oct19 ?        00:00:09 asm_smon_+ASM
[root@oraclelab2 ~]#

[root@oraclelab2 ~]# su - oracle
Last login: Tue Oct 26 09:01:29 IST 2021 on pts/0
[oracle@oraclelab2 ~]$ . oraenv
ORACLE_SID = [oracle] ? TESTCDB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:57:03 2021
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTCDB   READ WRITE

SQL> 

[oracle@oraclelab2 ~]$ ps -ef|grep tns
root        23     2  0 Oct04 ?        00:00:00 [netns]
oracle    7813     1  0 Oct22 ?        00:00:08 /u01/app/19.0.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle    8020     1  0 Oct22 ?        00:00:00 /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr LISTENER_DB -inherit
oracle   13152 12948  0 01:58 pts/0    00:00:00 grep --color=auto tns
[oracle@oraclelab2 ~]$

2. How to connect to CDB with OS authentication?

[oracle@oraclelab2 ~]$ . oraenv
ORACLE_SID = [oracle] ? TESTCDB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:57:03 2021
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTCDB   READ WRITE

SQL>

3. How to connect to CDB with Password authentication?


[oracle@oraclelab2 ~]$ sqlplus sys/Mallik123@TESTCDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:58:57 2021
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTCDB   READ WRITE

SQL> 

4. Try connecting to CDB with wrong password it will not allow


[oracle@oraclelab2 ~]$ sqlplus sys/Mallik12345@TESTCDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 01:59:33 2021
Version 19.3.0.0.0

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

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


Enter user-name: [oracle@oraclelab2 ~]$
[oracle@oraclelab2 ~]$

5. Its mandatory to user TNS service named along with username and password in order to verify the password. If you don't use TNS service name then default it will user OS authentication to connect to database irrespective of correct password or wrong password you enter.


[oracle@oraclelab2 ~]$ sqlplus sys/Mallik12345 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:00:12 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTCDB   READ WRITE

SQL> 

[oracle@oraclelab2 ~]$ sqlplus sys/Mallik123 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:01:00 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      TESTCDB
SQL> 

6. Verify your TNS service name for CDB and PDB valid from your tnsnames.ora file and using tnsping 


[oracle@oraclelab2 dbhome_1]$ cd network/admin/
[oracle@oraclelab2 admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@oraclelab2 admin]$ ll
total 16
-rw-r--r--. 1 oracle oinstall  148 Oct 22 09:44 listener.ora
drwxr-xr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 14  2018 shrept.lst
-rw-r--r--. 1 oracle oinstall   79 Oct 22 10:41 sqlnet.ora
-rw-r-----. 1 oracle oinstall 1117 Oct 22 10:46 tnsnames.ora
[oracle@oraclelab2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TESTCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTCDB)
    )
  )

TESTPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTPDB)
    )
  )

[oracle@oraclelab2 admin]$ tnsping TESTCDB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-OCT-2021 02:02:34

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTCDB)))
OK (0 msec)
[oracle@oraclelab2 admin]$

[oracle@oraclelab2 admin]$ tnsping TESTPDB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-OCT-2021 02:14:36

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab2.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTPDB)))
OK (10 msec)
[oracle@oraclelab2 admin]$

7. How to connect to PDB? Connect to PDB via CDB


[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$

[oracle@oraclelab2 admin]$ ps -ef|grep smon
oracle     651     1  0 Oct20 ?        00:00:10 ora_smon_TESTCDB
oracle   12206     1  0 01:49 ?        00:00:00 ora_smon_DEVCDB
oracle   13566 12948  0 02:03 pts/0    00:00:00 grep --color=auto smon
oracle   32650     1  0 Oct19 ?        00:00:09 asm_smon_+ASM
[oracle@oraclelab2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:03:11 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO
         5 PDB2                           MOUNTED
         6 PDB3                           MOUNTED
SQL> alter session set container=TESTPDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 TESTPDB                        READ WRITE NO
SQL> 

8. How to connect to PDB with OS authentication?


[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ export ORACLE_PDB_SID=TESTPDB
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_PDB_SID=TESTPDB
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:05:55 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 TESTPDB                        READ WRITE NO
SQL> 

9. Can I able to start PDB using startup command which is down by connecting to PDB via OS authentication 

--- Not possible since PDBs does not have any init file/pfile or spfile 

[oracle@oraclelab2 admin]$ export ORACLE_PDB_SID=PDB2
[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_PDB_SID=PDB2
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:07:00 2021
Version 19.3.0.0.0

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

Connected.
SQL> show pdbs
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0


SP2-1545: This feature requires Database availability.
SQL> exit
Disconnected
[oracle@oraclelab2 admin]$

[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_PDB_SID=PDB2
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:11:49 2021
Version 19.3.0.0.0

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

Connected.
SQL> startup;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL>

10. How to start PDBs which are down:

Only way to start your PDBs is to connect to CDB and start them from CDB 

[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:07:38 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO
         5 PDB2                           MOUNTED
         6 PDB3                           MOUNTED
SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO
         5 PDB2                           READ WRITE NO
         6 PDB3                           MOUNTED
SQL> 

11. How to connect to PDB using normal user account?


[oracle@oraclelab2 admin]$ env |grep ORA
ORACLE_SID=TESTCDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab2 admin]$ 
[oracle@oraclelab2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:12:16 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO
         5 PDB2                           READ WRITE NO
         6 PDB3                           MOUNTED
SQL>

SQL> alter session set container=TESTPDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 TESTPDB                        READ WRITE NO
SQL> 

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where USERNAME='MALLIK';
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MALLIK                         OPEN

[oracle@oraclelab2 admin]$ sqlplus mallik/mallik@TESTPDB

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:15:43 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs
SP2-0382: The SHOW PDBS command is not available
SQL> show user
USER is "MALLIK"
SQL>

12. Connect to PDB using sys account via password authentication 


[oracle@oraclelab2 admin]$ sqlplus sys/Mallik123@TESTPDB  as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 02:16:31 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 TESTPDB                        READ WRITE NO
SQL>

Regards,
Mallik
Posted by at No comments:

Saturday, October 23, 2021

Oracle Database Listener Status - READY - UNKNOWN - RESTRICTED - BLOCKED

LISTENER Status:


Below are the typical listener status:

Ready --- When Instance started in open mode with dynamic registration
Unknown --- When Instance started in open mode with static registration
Blocked --- When Instance in in nomount
Restricted --- When Instance is started with restricted session

1. Verify that database and listener are down

[oracle@oraclelab1 ~]$ ps -ef|grep smon
oracle    4009  3533  0 01:17 pts/2    00:00:00 grep --color=auto smon
[oracle@oraclelab1 ~]$ ps -ef|grep tns
root        23     2  0 Sep30 ?        00:00:00 [netns]
oracle    4018  3533  0 01:17 pts/2    00:00:00 grep --color=auto tns
[oracle@oraclelab1 ~]$

2. Start Database in nomount mode then we can see listener will be in blocked state:

[oracle@oraclelab1 ~]$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
DEVDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N
[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 23 01:18:41 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2415917880 bytes
Fixed Size                  8899384 bytes
Variable Size             520093696 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
DEVDB            STARTED

SQL>

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      DEVDB
SQL>

[oracle@oraclelab1 admin]$ cat listener.ora
LISTENER_DEVDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
    )
  )
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:20:03

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@oraclelab1 admin]$ lsnrctl start LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:20:14

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DEVDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-OCT-2021 01:20:14
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oraclelab1 admin]$ 

[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:21:09

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DEVDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-OCT-2021 01:20:14
Uptime                    0 days 0 hr. 0 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
  Instance "DEVDB", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$ 

3. Start Database in mount mode with restricted exclusive mode then we can see listener will be in restricted state:


SQL> shut immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL>
SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 2415917880 bytes
Fixed Size                  8899384 bytes
Variable Size             520093696 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
DEVDB            MOUNTED

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEVDB     MOUNTED

SQL> 

[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:24:35

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DEVDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-OCT-2021 01:20:14
Uptime                    0 days 0 hr. 4 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
  Instance "DEVDB", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$

4. Start Database in open mode and verify that the listener is running with dynamic registration then we can see listener will be in Ready state:


SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 2415917880 bytes
Fixed Size                  8899384 bytes
Variable Size             520093696 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
DEVDB            OPEN

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEVDB     READ WRITE

SQL>

Dynamic Listener:
=================
LISTENER_DEVDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
    )
  )

[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:28:22

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DEVDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-OCT-2021 01:20:14
Uptime                    0 days 0 hr. 8 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
  Instance "DEVDB", status READY, has 1 handler(s) for this service...
Service "DEVDBXDB" has 1 instance(s).
  Instance "DEVDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$ 

5. Start Database in open mode and verify that the listener is running with static registration then we can see listener will be in Unknown state:


SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 2415917880 bytes
Fixed Size                  8899384 bytes
Variable Size             520093696 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
DEVDB            OPEN

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEVDB     READ WRITE

SQL>

Static Listener:
================
LISTENER_DEVDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_DEVDB=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=DEVDB)
          (SID_NAME=DEVDB)
          (ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
        )
      )

[oracle@oraclelab1 admin]$ cat listener.ora
LISTENER_DEVDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_DEVDB=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=DEVDB)
          (SID_NAME=DEVDB)
          (ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
        )
      )
[oracle@oraclelab1 admin]$ lsnrctl start LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:30:30

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DEVDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-OCT-2021 01:30:30
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
  Instance "DEVDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:31:21

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DEVDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-OCT-2021 01:30:30
Uptime                    0 days 0 hr. 0 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 2 instance(s).
  Instance "DEVDB", status UNKNOWN, has 1 handler(s) for this service...
  Instance "DEVDB", status READY, has 1 handler(s) for this service...
Service "DEVDBXDB" has 1 instance(s).
  Instance "DEVDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$ 

6. tnsnames entries in the tnsnames.ora files will be used for application or user connection via remotely


tnsnames:
=========
DEVDB =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = DEVDB)
  )
)

[oracle@oraclelab1 admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@oraclelab1 admin]$ vi tnsnames.ora
[oracle@oraclelab1 admin]$ cat tnsnames.ora
DEVDB =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = DEVDB)
  )
)
[oracle@oraclelab1 admin]$ tnsping DEVDB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:33:05

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DEVDB)))
OK (0 msec)
[oracle@oraclelab1 admin]$

Note:

If DB is down and if we start the listener then then listener does not have any status and it will not registered with any service or Instance.

Its just black listener starts:

[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:27:06

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DEVDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-OCT-2021 01:20:14
Uptime                    0 days 0 hr. 6 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oraclelab1 admin]$

Regards,
Mallik
Posted by at No comments:

Saturday, October 9, 2021

How to enable or disable BCT in Oracle Database?

How to enable or disable BCT in Oracle Database?


Block changing tracking improves the performance of incremental backups by recording changed blocks in the block change tracking file. During an incremental backup, instead of scanning all data blocks to identify which blocks have changed, RMAN uses this file to identify the changed blocks that need to be backed up.

Enabling the BCT will improves the RMAN incremental performance

To determine if block change tracking is enabled or not


[oracle@oracledb ~]$ . oraenv
ORACLE_SID = [ORACDB] ?
The Oracle base remains unchanged with value /u01/app/oradev
[oracle@oracledb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 8 21:18:19 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME
---------- ------------
DISABLED
SQL>

Enable:

Enable the block change tracing using below command and verify the same:


SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME
---------- --------------------------------------------------------------------------------
ENABLED /u01/app/oradev/oradata/ORACDB/ORACDB/changetracking/o1_mf_jp0t5bt0_.chg


Disable:

Disable the block change tracing using below command and verify the same:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME
---------- --------------------------------------------------------------------------------
DISABLED

Regards,
Mallik

Posted by at No comments:
Subscribe to: Posts (Atom)

Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month. [oracle@o...