Manually set local_listener at database level for service registration

Set local_listener at the database level  for service registration 



Set local_listener at DB levels 
-------------------------------------------

alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.43.200)(PORT=1521))'  scope=both;

alter system register ;


SQL> show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS = (PROTOCOL=TCP)(HOST
                                                 =192.168.43.36)(PORT=1521))

SQL>

sample listener.ora
-------------------------

[oracle@proddb19c admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_home/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.36)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@proddb19c admin]$

sample tnsnames.ora

----------------------------

[oracle@proddb19c admin]$ cat tnsnames.ora

orcl =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.36)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

      (UR = A)

   )

 )

[oracle@proddb19c admin]$

RPM required to enable X11 display

--------------------------------------------

yum install  xorg-x11-server-Xorg xorg-x11-xauth xorg-x11-apps -y

Oracle Database 19c step by step installation guide on Oracle Linux 7.5

Oracle Database 19c step by step installation guide on Oracle Linux 7.5

• Make sure all prerequisites met before installing oracle software, like kernel parameter , permission, RPM's, etc. Use this link for reference(https://anuragkumarjoy.blogspot.com/2016/02/oracle-database-installation-on-oracle.html)
• Download the oracle 19c software from oracle support
• unzip oracle software in ORACLE_HOME location 
• ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_home
• go to ORACLE_HOME (cd /u01/app/oracle/product/19.0.0/db_home)
• unzip command: (unzip -oq /tmp/LINUX.X64_193000_db_home.zip)
• after that run the  "./runInstaller"

In this case, I used sample schema installed

This window is pop-up because I used a non-standard password.

after increased "shmmax" parameter, run -run the check again

Need to run from root user.

Upgrade Timezone version (DSTv14) in 11gR2 database after upgrading the database from 11.2.0.3

Upgrade Timezone version (DSTv14) in 11gR2 database after upgrading the database from 11.2.0.3


SQL>  select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_3.dat                3

SQL>


SQL> sELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  FROM DATABASE_PROPERTIES
  WHERE PROPERTY_NAME LIKE 'DST_%'
  ORDER BY PROPERTY_NAME  2    3    4
  5
SQL> /

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         3
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL>


execute script in below order to update timezone DSTv14


Conn / as sysdba
spool countstatsTSTZ.log
@countstatsTSTZ.sql
spool off

Conn / as sysdba

spool upg_tzv_check.log

@upg_tzv_check.sql

spool off

Conn / as sysdba

spool upg_tzv_apply.log

@upg_tzv_apply.sql

spool off

Conn / as sysdba

spool upg_tzv_check.log

upg_tzv_check.sql

spool off

SQL> select * from v$timezone_file;

FILENAME                VERSION

-------------------- ----------

timezlrg_14.dat              14

SQL> sELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  FROM DATABASE_PROPERTIES

  WHERE PROPERTY_NAME LIKE 'DST_%'

  ORDER BY PROPERTY_NAME  2    3    4

  5  /

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL>