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';

Linker error while installing Oracle 11g /u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk

During installing 11g on OEL7 I got error when linking libraries.

To solve you should change syntax in the following file:

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk

Change the following line from

$(MK_EMAGENT_NMECTL)

to:

$(MK_EMAGENT_NMECTL) -lnnz11

and click retry on error window to continue?

Good Luck!

CURSOR_SHARING effect on database performance, latch: shared pool

I have upgraded our database from 11g to 12c and after that query performance degraded significantly.

I have generated ADDM report during the problematic period and found that the main problem was hard parses :

1  Hard Parse Due to Literal Usage           10.38 | 43.55%        1

Finding 1: Hard Parse Due to Literal Usage
Impact is 10.38 active sessions, 43.55% of total activity.
———————————————————-
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.

For us it is not new that our developers are not using bind variables so shared pool was/is growing and growing to retain all of the parsed SQLs and their execution plans.

But the same codes were running on 11g and were working fine. Just after upgrade database started to feel that bad :)

The reason is that 12c has major change in optimize behavior. So if bind variables are not used in existing application then you need to use CURSOR_SHARING=FORCE option, old value of this parameter was CURSOR_SHARING=EXACT.

alter system set cursor_sharing=FORCE;

After that database started to feel better but in any case I cleared shared pool(for clearing old,not necessary parses):

alter system flush shared_pool;

Database started to feel better!

Good Luck!

scsi_id not returning any output in a VM on VMware ESX

  1. scsi_id not returning result:

    scsi_id -g -u -d /dev/sdb
    No Result

  2. Start the vSphere Client, and log in to a vCenter Server.
  3.  Select Virtual Machines for which you want to retrieve disk unique id. I need this attribute for udev rules to prepare disks for ASM, for example.
  4.  Right-click the virtual machine for which you are enabling the disk UUID attribute, and select Power > Power Off.
    The virtual machine powers off.
  5. Right-click the virtual machine, and click Edit Settings.
  6. Click the Options tab, and select the General entry in the settings column.
  7. Click Configuration Parameters.
    The Configuration Paramters window appears.
  8. Click Add Row.
  9. In the Name column, enter disk.EnableUUID
  10. In the Value column, enter TRUE.
  11. Click OK and click Save.
  12. Power on the virtual machine.
  13. Now, it returns id:

    scsi_id -g -u -d /dev/sdb
    36000c292dfddac7b8934d3293313098e

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;

SESSIONS WAITING ON INACTIVE TRANSACTION BRANCH, GLOBAL HASH COLLISION 12c, 11g Oracle RAC(distributed transaction)

When performing XA transactions against a multi-node Oracle RAC configuration, some branches of the transaction may not commit.. this is a known bug, but to tell the truth no bug fix helped me to solve this problem until I came across IBM technote. https://www-304.ibm.com/support/docview.wss?uid=swg21460967

There are several workarounds but I prefer Work around 1.  I have used it and works perfectly.

1. If using pfile (init.ora) files, add the following line to the file:

_clusterwide_global_transactions=false

2. If using an spfile, issue the following command from SQL*Plus:

alter system set “_clusterwide_global_transactions”=false scope=spfile

3. Restart the database (you can restart nodes , one by one)

Problem should dissapear.

 

In SQL*Plus, how do I change the prompt to show the connected user and database?

#Connect as oracle user and add the following line at the end of th glogin.sql script.

su - oracle
vi $ORACLE_HOME\sqlplus\admin\glogin.sql
set sqlprompt "_user '@' _connect_identifier > " 

#Now check

sqlplus / as sysdba
SYS @ LBTCI1 >
Follow

Get every new post delivered to your Inbox.