Find out started Oracle databases in Windows using command line

One of the simplest way to find what databases are started in Windows  is to see all started processes and filter them by keyword oracle.

By net start command you will see all started services:

H:\>net start
These Windows services are started:

Apache Tomcat 6.0-1
 Application Experience Lookup Service
 COM+ Event System
 COM+ System Application
 Computer Browser
 Cryptographic Services
 DCOM Server Process Launcher
 DHCP Client
 Distributed Link Tracking Client
 Distributed Transaction Coordinator
 DNS Client
 Event Log
 Help and Support
 IPSEC Services
 Java Quick Starter
 Logical Disk Manager
 Machine Debug Manager
 ManageEngine EventLogAnalyzer 9 - Agent
 Net Logon
 NetBackup Client Service
 NetBackup Legacy Client Service
 NetBackup Legacy Network Service
 Network Connections
 Network Location Awareness (NLA)
 OracleOraDb10g_home1ClrAgent
 OracleOraDb10g_home1iSQL*Plus
 OracleOraDb10g_home1TNSListener
 OracleServiceORATEST10G
 Plug and Play
 Print Spooler
 Protected Storage
 Remote Procedure Call (RPC)
 Remote Registry
 Secondary Logon
 Security Accounts Manager
 Server
 Shell Hardware Detection
 Symantec Endpoint Protection
 Symantec Event Manager
 Symantec Management Client
 Symantec Private Branch Exchange
 Symantec Settings Manager
 System Event Notification
 Task Scheduler
 TCP/IP NetBIOS Helper
 Terminal Services
 VMware Physical Disk Helper Service
 VMware Tools Service
 VMware Upgrade Helper
 VNC Server Version 4
 Windows Management Instrumentation
 Windows Time
 Wireless Configuration
 Workstation

The command completed successfully.

To filter the output use | <–pipe and then command findstr which has option /I <–case insensitive to search keyword oracle :

H:\>net start|findstr /I "oracle"

 OracleOraDb10g_home1ClrAgent
 OracleOraDb10g_home1iSQL*Plus
 OracleOraDb10g_home1TNSListener
 OracleServiceORATEST10G

From here you should guess that database sid that is started is ORATEST10G

Blocking session, find root blockers and kill

 

By the following script you are able to find root cause of the locks. Script do not display BACKGROUND processes that are locking others (for example, LGWR, DBWR), because killing them causes database crush.

In front of the kill immediate statement there is written additional information such as : username[machine]:sql_id[prev_sql_id]:program and then comes kill immediate statement.

with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
 from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)
 select lpad(' ',2*(level-1))||waiter lock_tree from
 (select * from lk
 union all
 select distinct 'root', blocker from lk
 where blocker not in (select waiter from lk))
 connect by prior waiter=blocker start with blocker='root';
--Generate SQLs to kill top-level blockers
set serverout on
 declare
 sess varchar2(20);
 sessinfo varchar2(100);
 begin
 for i in
 (with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
 from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)
 select distinct blocker from lk where blocker not in (select waiter from lk)
 )
 loop
 begin
 select regexp_substr(i.blocker,'[0-9]+$')||','||serial# ||',@' || regexp_substr(i.blocker,'[0-9]+'),
 substr(username||'['||machine||']:'||sql_id||'['||prev_sql_id||']:'||program,1,100) into sess, sessinfo
 from gv$session where inst_id = regexp_substr(i.blocker,'[0-9]+') and sid = regexp_substr(i.blocker,'[0-9]+$') and type='USER';
 dbms_output.put_line(sessinfo || ' ' || 'alter system kill session ''' || sess || ''' immediate;');
 exception when no_data_found
 then continue;
 end;
 end loop;
 end;
 /

DDL Trigger to audit schema changes

  1. Create sequence for assigning numbers to the events:
    -- Create sequence
    create sequence SYS.DSQ_DDLEVENTS
    minvalue 1
    maxvalue 9999999999999999999999999999
    start with 1
    increment by 1
    cache 100;
  2. Create ddl events table
    -- Create table
    create table SYS.DDL_EVENTS
    (
     eventid NUMBER,
     inst_id   NUMBER,
     eventdate DATE,
     oraloginuser VARCHAR2(100),
     oradictobjname VARCHAR2(100),
     oradictobjowner VARCHAR2(100),
     oradictobjtype VARCHAR2(100),
     orasysevent VARCHAR2(100),
     machine VARCHAR2(100),
     program VARCHAR2(100),
     osuser VARCHAR2(100)
    )
    tablespace USERS;
  3. Create table for saving SQL statements(this is necessary because triggered sql statements may have several lines)
    -- Create table
    create table SYS.DDL_EVENTS_SQL
    ( eventid NUMBER,
     sqlline NUMBER,
     sqltext VARCHAR2(4000)
    )
    tablespace USERS;
  4. Create DDL trigger(it doesn’t degrade performance at all, by my experience)
    CREATE OR REPLACE TRIGGER sys.audit_ddl_trg
    AFTER DDL ON DATABASE
    DECLARE
    l_eventId NUMBER;
     l_sqlText ORA_NAME_LIST_T;
    BEGIN
    if ORA_SYSEVENT!='TRUNCATE' then
    
     SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL;
    INSERT INTO ddl_events (eventId,
     inst_id,
     EVENTDATE,
     ORALOGINUSER,
     ORADICTOBJNAME,
     ORADICTOBJOWNER,
     ORADICTOBJTYPE,
     ORASYSEVENT,
     machine,
     program,
     osuser
     )
     ( SELECT l_eventId,
     inst_id,
     SYSDATE,
     ORA_LOGIN_USER,
     ORA_DICT_OBJ_NAME,
     ORA_DICT_OBJ_OWNER,
     ORA_DICT_OBJ_TYPE,
     ORA_SYSEVENT,
     machine,
     program,
     osuser
     FROM SYS.DUAL
     right outer join
     SYS.GV$SESSION s on (1=1)
     WHERE s.sid=SYS_CONTEXT('USERENV','SID')
     and SYS_CONTEXT('USERENV','INSTANCE')=s.inst_id);
     FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
     INSERT INTO ddl_events_sql
     ( eventId, sqlLine, sqlText )
     VALUES
     ( l_eventId, l, l_sqlText(l) );
     END LOOP;
     end if;
    
     exception when others then
     null;
    END;

Oracle: Audit DMLs by specific user

Ordinary auditing do not have option to indicate audit some activities done by specific user.

I mean, you cannot write the following:

audit insert on my_schema.my_table by my_user;  <<—-not possible. The right statement is:
audit insert on my_schema.my_table by access;
or
audit insert on my_schema.my_table by session;

If I want to audit only activities done by my_user, one of the way is to create audit policy like the following;

begin
dbms_fga.add_policy(
object_schema=>’my_schema‘,
object_name=> ‘my_table‘,
policy_name=> ‘my_policy’,
audit_condition => ‘sys_context(”USERENV”,”CURRENT_USER”)=”MY_USER”’,
enable => TRUE,
statement_types => ‘INSERT, UPDATE, DELETE’,
audit_column_opts => dbms_fga.all_columns);
END;

So audit_condition gives the opportunity to check something and in this case we are checking user that is running statements indicated in statement_types option.

–Logs will be located here

SELECT * FROM dba_fga_audit_trail

–To see what policies we have

SELECT * FROM dba_audit_policies

HOW TO CATALOG TAPE BACKUP PIECES

You can catalog TAPE backup piece only using automatic channel:

RMAN> configure channel device type 'SBT_TAPE' parms <mml parameters>

Example:

configure channel device type 'SBT_TAPE' parms='ENV=(NB_ORA_CLIENT=ClientHostName,NB_ORA_SERV=backupServerHostName)';
RMAN> catalog device type 'SBT_TAPE' backuppiece 'arch_dEYC_ub4qtfud9_s20836_p1_t903346601';

Oracle proxy user, create dblink, job in different schema

This post describes how database administrator can use to log on to a user when the password is not known.

Sometimes DBAs want to create database links in another schema, that is not possible just with create database link username.database_link_name, but the connected user should be the same as the owner of the link.
Also if DBA want the job, during creation, to take different user credentials than they are connected there are two methods(maybe more):

1. By resetting user password

  1.  -- Get user password hash
    SELECT password
    FROM   sys.user$
    WHERE  name = 'MARI';
    
    PASSWORD
    ------------------------------
    D456844C36682A67
    -- Reset the password
    ALTER USER MARI IDENTIFIED BY 123;
  2. --Connect by mari and do what you want.
    CONN MARI/123
  3. --Return back mari's password
    CONN / AS SYSDBA
    ALTER USER MARI IDENTIFIED BY VALUES 'D456844C36682A67';

2. By using proxy user. Assume that we have DBA user named my_dba, by which we will be able to connect to the database as MARI without knowing MARI password.

  1. ALTER USER MARI GRANT CONNECT THROUGH my_dba;
  2. CONN my_dba[MARI]/my_dba_password
  3. SHOW USER
    USER is "MARI"

    Do what you want with mari user.

  4. Proxy users can be identified using the PROXY_USERS view.
    SELECT * FROM proxy_users;
  5. –To revoke permission
    ALTER USER scott REVOKE CONNECT THROUGH test_user;

Configure Oracle 11g database to connect with AD(Domain) user using Kerberos5

Problem:

Application config files contain plain text passwords. Which is insecure and should be changed to encrypted passwords or not indicate them at all 🙂

The last solution can be achieved by KERBEROS authorization. So users will connect to the database using Active Directory user and will not indicate username/password. Like it happens in SQL Server with Windows Authorization.

So at the end of this post domain user connected to the windows computer will be able to connect to the database without indicating username and password.

========Details==============

AD Server:

Ip: 192.168.11.10

Hostname: ADSERVER.DOMAIN.GE

Realm: DOMAIN.GE

===========================

Database Server:

Ip: 192.168.11.15

Hostname: DBSERVER.DOMAIN.GE

Database Name/SID: ORCL

===========================

Client:

Ip: 192.168.11.20

Domain User: MARIAMI.DOMAIN.GE

Client Home: D:\app\oracle\product\11.2.0\client_1

===========================

Let’s start..

========AD configuration

1. Under DOMAIN.GE /Users  create a new user with the same name as the hostname of the database (with lowercase letters).

First name             : dbserver.domain.ge
Fill name               : dbserver.domain.ge
User logon name : dbserver.domain.ge
User logon name (pre-Windows 2000): DOMAIN \ dbserver.domain.ge

Next >

I prefer to check “Password never expires”  and uncheck “User must change password at next logon

Next >

Finish

Right click on the newly created user , choose Properties -> choose tab Account ->in Account options section -> check “Do not require Kerberos preauthentication

2. Generate keytab file using ktpass.exe

ktpass.exe -princ oracle/dbserver.domain.ge@DOMAIN.GE -ptype KRB5_NT_PRINCIPAL -mapuser dbserver.domain.ge -crypto ALL -pass qwert123QWERT  -out d:\keytab

========Database Server Configuration

export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/db/network/admin

mkdir -p $TNS_ADMIN/kerberos

Copy keytab file generated on AD server and place it in $TNS_ADMIN/kerberos/  location.

Create krb5.conf file.

vim $TNS_ADMIN/kerberos/krb5.conf

[libdefaults]
default_realm = DOMAIN.GE

[realms]
DOMAIN.GE = {
kdc = adserver.domain.ge:88
}

[domain_realm]
.domain.ge = DOMAIN.GE
domain.ge = DOMAIN.GE

File  is case sensitive 🙂 try to not make case sensitive errors.  AD server hostname is in lowercase letters.

So $TNS_ADMIN/kerberos/ contains two files:

ls  $TNS_ADMIN/kerberos/

keytab    krb5.conf

Configure sqlnet.ora file.

Note that at this time if you mistakenly configure sqlnet.ora file , connections to the database will cause error. Be very careful when configuring on production server. But you can solve this error in 3 seconds , by replacing new sqlnet.ora with old sqlnet.ora or just change parameter

SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)

to

SQLNET.AUTHENTICATION_SERVICES= (BEQ)

vim $TNS_ADMIN/sqlnet.ora
SQLNET.KERBEROS5_KEYTAB = /u01/app/oracle/product/11.2.0/db/network/admin/kerberos/keytab

SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)

SQLNET.KERBEROS5_CONF = /u01/app/oracle/product/11.2.0/db/network/admin/kerberos/krb5.conf

SQLNET.KERBEROS5_CONF_MIT = TRUE

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

ADR_BASE = /u01/app/oracle

SQLNET.KERBEROS5_CC_NAME=/u01/app/oracle/product/11.2.0/db/network/admin/kerberos/cache_file

#TRACE_LEVEL_SERVER = SUPPORT

#TRACE_UNIQUE_SERVER = on

#TRACE_DIRECTORY_SERVER = /u01/app/oracle/traces

#TRACE_FILE_SERVER = server

DIAG_ADR_ENABLED = OFF

#TRACE_TIMESTAMP_SERVER = ON

Note: I have commented TRACE_* parameters , because it is not necessary if everything is OK , but if connection fails then you will need these parameters to be uncommented to generate more informative trace file to solve the problem.

Connect to the database via sqlplus and create database user corresponding to the domain user, using uppercase letters.

sqlplus / as sysdba

create user “MARIAMI.DOMAIN.GE” identified externally.

grant connect, resource to “MARIAMI.DOMAIN.GE”;

ALTER SYSTEM SET os_authent_prefix=” SCOPE=SPFILE;

ALTER SYSTEM SET remote_os_authent=FALSE SCOPE=SPFILE;

shutdown immediate;

startup;

Generate ticket

$ okinit -e 23 -f dbserver.domain.ge

Enter the password of dbserver.domain.ge username , in our case qwert123QWERT.

Note, it will generate a cache file called cache_file(SQLNET.KERBEROS5_CC_NAME parameter in sqlnet). It has expire time (8 hours) and after it is passed , connection to the database without password will not work. So you will need a cronjob to run the above command every 7 or less hours. But for now you have 8 hours to configure kerberos and test the connection.. )

========Client Configuration

1. Configure sqlnet.ora

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

SQLNET.KERBEROS5_CONF_MIT = true

SQLNET.KERBEROS5_CC_NAME=OSMSFT://

DIAG_ADR_ENABLED = OFF

SQLNET.AUTHENTICATION_SERVICES= (beq, kerberos5)

SQLNET.KERBEROS5_CONF = d:\app\oracle\krb5.ini

#TRACE_LEVEL_CLIENT = SUPPORT

#TRACE_UNIQUE_CLIENT = on

#TRACE_DIRECTORY_CLIENT = D:\app\oracle

#TRACE_FILE_CLIENT = client

#TRACE_TIMESTAMP_CLIENT = ON

#SQLNET.KERBEROS5_KEYTAB = C:\krb5\v5srvtab

2. Copy krb5.conf file content from db server and name it as .ini instead of .conf

–krb5.ini

[libdefaults]
default_realm = DOMAIN.GE

[realms]
DOMAIN.GE = {
kdc = adserver.domain.ge:88
}

[domain_realm]
.domain.ge = DOMAIN.GE
domain.ge = DOMAIN.GE

3. tnsnames.ora , nothing special.

ORCL =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

4.  Add kerberos5 in C:\windows\system32\drivers\etc\services:

Before:

kerberos 88/tcp krb5 kerberos-sec #Kerberos

kerberos 88/udp krb5 kerberos-sec #Kerberos

After:

kerberos 88/tcp kerberos5 krb5 kerberos-sec #Kerberos

kerberos 88/udp kerberos5 krb5 kerberos-sec #Kerberos

Try to connect without password. /@ORCL  together.

sqlplus /@ORCL

With PL/SQL Developer , do not indicate username and password just sid and try to connect. I have noticed that PL/SQL Devloper version 9 doesn’t worked for me version 10 worked.

Note: If you are using shared servers and during connecting to the database returns ORA-01017: invalid username/password; logon denied. The problem may be the following, that  you changed sqlnet.ora after instance startup. The problem is that shared servers read sqlnet.ora just before instance startup, so if you change any parameters in sqlnet.ora file after instance startup then shared servers will not recognize it. The only solution is to restart database, to re-read sqlnet.ora file.

Oracleasm deletedisk failed Unable to clear disk

I’ve deleted disk from ASM diskgroup:

ALTER DISKGROUP DATA01 DROP DISK DISK6;

After it finished dropping the disk and all extents were moved to the remaining disks, I tried to delete disk from the ASM library and return it to the system:

oracleasm deletedisk -v DISK6

Clearing disk header: oracleasm-write-label: Unable to open device "/dev/oracleasm/disks/DISK6": Device or resource busy
failed
Unable to clear disk "DISK6"

The reason is that oracle leaves the disk open even after removing it from the diskgroup.

There are two solutions:________________________________________________

1. One for  24/7 databases:

–Find the device name

[root@r2n1 ~]# blkid |grep oracleasm

/dev/sdd1: LABEL="DISK6" TYPE="oracleasm"

–Clear the device header manually

Make sure, you use the right device name. If you specify wrong device name, the operation is unrecoverable.

su –

dd if=/dev/zero of=/dev/sdd1 bs=1024 count=100

or

dd if=/dev/zero of=/dev/oracleasm/disks/DISK6 bs=1024 count=100

If you have RAC system , run dd on all nodes.

Another solution:

1. Reboot the server

# reboot

Or

Restart database instance and CRS services

–Shutdown database

$ export ORACE_SID=orcl
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ sqlplus / as sysdba
SQL> shutdown immediate;

–Shutdown CRS services

./crsctl stop crs

–Start CRS services

./crsctl start crs

–Start database

$ export ORACE_SID=orcl
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ sqlplus / as sysdba
SQL> startup;

2. Then delete the disk.

oracleasm deletedisk -v DISK6

PIN_PACKS-TRIGGER-INVALID, DBMS_SHARED_POOL.KEEP must be declared 10g

To resolve the issue, do the following:

1. Connect as SYSDBA

$sqlplus / as sysdba

2. Create DBMS_SHARED_POOL package , grant necessary privileges and create dba_keepsizes view.

@?/rdbms/admin/dbmspool.sql

3. Recompile the trigger

alter trigger PIN_PACKS compile;

DBMS_SQLPA – PACKAGE BODY – INVALID 10g

To resolve the issue, do the following:

1. Connect as SYSDBA

$ sqlplus / as sysdba

2. Drop plan table

SQL> drop table plan_table;

Table dropped.

 

3. Recreate plan table

SQL> @?/rdbms/admin/utlxplan

Table created.

4. Re-create dbms_sqlpa package and public synonym

SQL> @?/rdbms/admin/prvtspao.plb

PL/SQL procedure successfully completed.

Package created.

No errors.

Synonym created.

No errors.

Package body created.

No errors.

5. Recompile all objects

SQL> @?/rdbms/admin/utlrp.sql