Network, Oracle

Changing Listener Port Number for Database Connection Requests

To change the listener port number for database connection requests to 1522:To change the listener port number for database connection requests to 1522:
Stop the listener.
See “Stopping and Starting the Listener” for instructions.
Open the file listener.ora with a text editor.
Table: Location of the listener.ora File shows the location of this file on each platform.
Location of the listener.ora File
Platform LocationLinux
cd $ORACLE_HOME/network/admin/vi listener.ora
Windows
c:\oracledba\app\oracle\product\10.2.0\server\NETWORK\ADMIN\

Locate the following section of the file:
LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))      (ADDRESS = (PROTOCOL = TCP)(HOST = dberman)(PORT = 1521))    )  )
Note that the line indicated in bold may or may not be present in the file.
Change the text (PORT = 1521) to (PORT = 1522).
Save the modified listener.ora file.
Start the listener.
See “Stopping and Starting the Listener” for instructions.
Start SQL Command Line and connect to the database as user SYSTEM.
See “Connecting Locally with SQL Command Line” for instructions. You must supply the SYSTEM password. You set this password upon installation (Windows) or configuration (Linux) of Oracle Database dba.
Enter the following two commands:
ALTER SYSTEM SET LOCAL_LISTENER = “(ADDRESS=(PROTOCOL=TCP)(HOST=dberman.mydomain.com)(PORT=1522))”;
ALTER SYSTEM REGISTER;
Exit SQL Command Line and run the lsnrctl status command to verify the port number change.
The new port number should be displayed in the Listening Endpoints Summary section of the status report, and the report should include the following lines:
Service “dba” has 1 instance(s).  Instance “dba”, status READY, has 1 handler(s) for this service…

Advertisements
Standard
Oracle

change / move the Oracle Inventory

Hi,

The below steps will help you to change / move the Oracle Inventory to other locations for Linux flavor setups:

Find the current location of the Oracle Inventory (default $ORACLE_BASE/oraInventory):

For example:

[oracle@tto ~]$ cd $ORACLE_BASE

[oracle@tto app]$ pwd
/opt/app

find . -name oraInventory -print

[oracle@tto app]$ find . -name oraInventory -print
find: ./oracle/product/11.2.0/db_1/opmn/conf: Permission denied
find: ./oracle/product/11.2.0/db_1/opmn/logs: Permission denied
find: ./oracle/product/11.2.0/db_1/inventory/ContentsXML/ConfigXML: Permission denied
find: ./oracle/product/11.2.0/db_1/cfgtoollogs/cfgfw: Permission denied
./oraInventory

Open the oraInst.loc file in /etc/ and check the value of inventory_loc

[oracle@tto app]$ cat /etc/oraInst.loc
inventory_loc=/opt/app/oraInventory
inst_group=oinstall

Copy the oraInventory directory to the destination directory

cp -Rp /opt/app/oraInventory /home/oracle/oraInventory

Edit the oraInst.loc file to point to the new location

For example:

vi /etc/oraInst.loc
inventory_loc=/home/oracle/oraInventory inst_group=dba

Note: The oraInst.loc file is simply a pointer to the location of the Oracle Inventory (oraInventory)

Regards, Erman

Standard
Oracle

How to kill session in Oracle RAC database?

We use v$session for indetifying target session to kill in non-rac database.Here in Oracle RAC instance we should need to check and identify session using gv$session. Because only gv$session contains rac instance identifier column.

SQL>select sid,serial#,inst_id from gv$session where machine=’GPTWORKGROUP’;
SID SERIAL# INST_ID
——— ——— ——-
9 171 1
1 row selected
SQL> alter system kill session ‘9,171,@1’;
System altered.
OR optionally you can use following command to kill session in Oracle RAC…
SQL>alter system kill session ‘9,171,@1’ immediate;
System altered.

In the single database ;

SELECT ‘ALTER SYSTEM KILL SESSION ‘||sid||’,’||serial#||’;’ FROM v$session;

Standard
Oracle

alter database set time_zone doesn’t work

Hello, We have a Oracle database 12c  run on Sun Solaris Server, the Unix server timezone is set to ‘EST'(Eastern Standard Time). When we created the database, the db timezone wasn’t explicitly specified. So when I issued the following statement:
“Select dbtimezone from dual”
I got +02:00

Syntax :

ALTER DATABASE <my db name> SET TIME_ZONE = ‘US/Eastern’
or
ALTER DATABASE <my db name> SET TIME_ZONE = ‘EST’

What do I do to change my Oracle timezone?

clear@db> select dbtimezone from dual;
DBTIMEZONE
——
-02:00

clear@db> alter database set time_zone = ‘EST’;
Database altered.

clear@db> select dbtimezone from dual;
DBTIMEZONE
——
-03:00
clear@db> connect / as sysdba;
Connected.
clear@db> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
clear@db> startup
ORACLE instance started.

Total System Global Area 358909124 bytes
Fixed Size 279748 bytes
Variable Size 335544320 bytes
Database Buffers 16777216 bytes
Redo Buffers 6307840 bytes
Database mounted.
Database opened.

clear@db> select dbtimezone from dual;
DBTIMEZONE
—-
EST

*******

For OMS( Oracle Management Server ) , log into the OMS repository as the SYSMAN user, the above command ’emctl resetTZ agent’ tells you exactly what needs to be executed in the repository.

SQL> exec mgmt_target.set_agent_tzrgn(‘‘, ‘Etc/GMT-6’);
SQL> commit;
SQL> emctl start agent — (or emctl start dbconsole)

 

Br. Erman.

Standard
Awr / Ash Report, Oracle

AWR Reports Rac and Single Oracle Database

1) AWR various Report.

SQL> @?/rdbms/admin/awrsqrpt.sql –> awr report for only single sql_id
SQL> @?/rdbms/admin/awrrpt.sql –> Traditional awr report for instance.

2) RAC Related awr Report

In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

SQL> @?/rdbms/admin/awrgrpt.sql — AWR Global Report (RAC) (global report)
SQL> @?/rdbms/admin/awrgdrpt.sql — AWR Global Diff Report (RAC)

Other important scripts under $ORACLE_HOME/rdbms/admin

SQL> @?/rdbms/admin/spawrrac.sql — Server Performance RAC report
SQL> @?/rdbms/admin/awrsqrpt.sql — Standard SQL statement Report
SQL> @?/rdbms/admin/awrddrpt.sql — Period diff on current instance
SQL> @?/rdbms/admin/awrrpti.sql — Workload Repository Report Instance (RAC)

Standard
Oracle

Find Oracle users with DBA or different privileges

Hello, How do I identify all Oracle users who have been granted DBA, Deployer or Etc. privileges. This would include SYSDBA and the DBA role granted. What are some swcript examples for finding these users?

basic scripts.
SQL> select * from dba_role_privs where granted_role=’DBA’;
SQL> select * from dba_role_privs where granted_role=’Deployer’;

The v$pwfile_users view contains a list of all users who have been granted the SYSDBA or SYSOPER privilege.

SQL> select * from v$pwfile_users;

ref: http://rampant-books.com/book_0814_audit.htm

br. erman.

Standard
Oracle, Troubleshooting

Reset Oracle SYS password

I have list my SYS password in Oracle and I need to recover the SYS password, or at least restore the password.  How can I recover my lost SYS password?

sqlplus /nolog
connect / as sysdba
ALTER USER sys IDENTIFIED BY “new_password”;

demo :

[oracle@mezon01 ~]$ . profile_db_PDEV
[oracle@mezon01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 1 17:19:52 2018

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

SQL> connect / as sysdba
Connected.
SQL> ALTER USER sys IDENTIFIED BY System_2018;

User altered.

SQL>
SQL>
SQL>
SQL> exit

Standard