Skip to content

Posts from the ‘Uncategorized’ Category

Generate SSH keys

Introduction

The SSH (Secure Shell) protocol is a method for secure remote login from one computer to another. SSH enables secure system administration and file transfers over insecure networks using encryption to secure the connections between endpoints. SSH keys are an important part of securely accessing Oracle Cloud Infrastructure compute instances in the cloud.

If you already have an SSH key pair, you may use that to connect to your environment. We recommend you use the Oracle Cloud Shell to interface with the OCI compute instance you will create. Oracle Cloud Shell is browser-based, does not require installation or configuration of anything on your laptop, and works independently of your network setup. However, if you prefer to connect via your laptop, please select an option based on your configuration.

Important: If the SSH key is not created correctly, you will not be able to connect to your environment and will get errors. Please ensure you create your key properly.

Option 1: Oracle Cloud Shell

Watch the video below for an overview of the Generate SSH Keys Cloud Shell option.https://www.youtube.com/embed/oq2Hk1Yy9Cg

The Cloud Shell machine is a small virtual machine running a Bash shell which you access through the OCI Console (Homepage). Cloud Shell comes with a pre-authenticated OCI CLI (Command Line Interface), set to the Console tenancy home page region, as well as up-to-date tools and utilities. To use the Cloud Shell machine, your tenancy administrator must grant the required IAM (Identity and Access Management) policy.

  1. To start the Oracle Cloud shell, go to your Cloud console and click the cloud shell icon at the top right of the page.
  2. Once the cloud shell has started, enter the following commands. Choose the key name you can remember. This will be the key name you will use to connect to any compute instances you create. Press Enter twice for no passphrase.Copymkdir .ssh cd .ssh ssh-keygen -b 2048 -t rsa -f <<sshkeyname>> Note: The angle brackets «» should not appear in your code.
  3. Examine the two files that you just created.Copyls Note in the output that there are two files, a private key: <<sshkeyname>> and a public key: <<sshkeyname>>.pub. Keep the private key safe and don’t share its content with anyone. The public key will be needed for various activities and can be uploaded to certain systems as well as copied and pasted to facilitate secure communications in the cloud.
  4. To list the contents of the public key, use the cat command cat <<sshkeyname>>.pubNote: The angle brackets «» should not appear in your code.
  5. When pasting the key into the compute instance in future labs, make sure that you remove any hard returns that may have been added when copying. The .pub key should be one line.

Option 2: MacOS

Watch the video below for an overview of the Generate SSH Keys Mac option.https://www.youtube.com/embed/0Q6_fvJGgMA

  1. If you don’t already have a shortcut to the terminal application for MacOS, you can find it in the Applications > Utilities menu or (Shift+Command+U) on your keyboard.
  2. Start up Terminal and type in the command ssh-keygen. ssh-keygen will ask you where to save the key, accept the default of the .ssh folder in your home directory by pressing Enter. File name will be id_rsa or whatever you choose to name your key. Press Enter twice for no passphrase. Remember the directory where you saved your key (~/.ssh), you will need to reference it later when you create your instance.Copyssh-keygen
  3. Type the following commands in the terminal window to verify that the public and private keys were created, and to copy the contents of the public key for use in creating your instance in the OCI dialog.Copycd .ssh ls cat id_rsa.pub Note in the output that there are two files, a private key: id_rsa and a public key: id_rsa.pub. Keep the private key safe and don’t share its content with anyone. The public key will be needed for various activities and can be uploaded to certain systems as well as copied and pasted to facilitate secure communications in the cloud.
  4. If you’re ready to create an instance, copy the contents and paste when prompted for the SSH key. Make sure that you remove any hard returns that may have been added when copying.

Click for the MacOS Terminal User Guide

Option 3: Windows 10

Watch the video below for an overview of the Generate SSH Keys Windows option.https://www.youtube.com/embed/BHbfxg_ek3Y

Creating keys for Windows can be interesting as ssh-keygen was not a native utility for Windows until the release of Windows 10. And it wasn’t included in the initial Windows 10 builds.

  1. Open a Powershell command window on your Windows 10 system by clicking it’s icon/tile or by typing ‘powershell’ in the search field in the Start bar.
  2. Enter the command ssh-keygen into the terminal window. Pay particular attention to where the file will be saved so you can locate it later. ssh-keygen will default to the standard .ssh directory under the user’s base directory.Copyssh-keygen
  3. Press Enter at all of the prompts to accept the default location, default file names, and no passphrase.Note: In Unix variants, a folder with a dot (.) in front of it was usually designated for configuration files and ‘hidden’ from normal view. However, a dot (.) doesn’t mean anything special in front of Windows folders. So the folder will exist but won’t be hidden.
  4. Confirm that your keys exist and were created properly. Enter the following commands in the Powershell window.Copycd .ssh ls cat id_rsa.pub You now have a working SSH key pair and can use it for secure communications to instances in the cloud. Do not share the private key id_rsa with anyone unless you understand what you’re doing. You should only ever need to share and copy the public key id_rsa.pub.
  5. Also note that if you elect to copy/paste the content of the key into certain dialogs for your labs, you will need to locate the file in Windows, either through Powershell, Explorer, or other directory tools, and open the public key file to copy its content. The example below is using Powershell to cat the content. You can select the text with your mouse but the copy/paste commands aren’t available. Use <ctrl-c> to copy the contents to the clipboard for pasting into other application dialogs.Or you can just open the file with Notepad, Wordpad, or other text editors.Note: Don’t use MS Word or any other rich text editors as they might add extra formatting characters which will render the key unusable.

Option 4: Prior Windows Versions

Use Git

In earlier versions of Windows, ssh-keygen was not a native utility, so third party utilities had to be utilized. In this section, we’ll illustrate using Git for WindowsGit for Windows includes a Unix like shell called Git Bash which is what you will use to create keys, and establish SSH communications with your cloud host systems. If you prefer PuTTY, go to the next section.

  1. If you don’t already have it installed, access the link below and download the application. If you are unable to install anything on your laptop due to permission issues, please use the Oracle Cloud Shell option above.Click here to download Git for Windows
  2. Follow the instructions for installation.Note: Installing Git for Windows is beyond the scope of this lab.
  3. Once installed, you should have an entry in your Windows Start menu for Git which should include the Git Bash command. Click on the Git Bash command.
  4. Type ssh-keygen into the terminal window. Press the Enter key to accept the default location (~/.ssh) and default filename (id_rsa) and <Enter> two more times for no passphrase.Copyssh-keygen Note: The tricky part here is that Git Bash uses a simulated Unix home directory. In order to view, retrieve, or copy your keys, you will need to navigate into the Windows directory structure.
  5. First navigate ‘up’ into the root C: directory.Copycd c: Then navigate ‘down’ into the .ssh folder in your normal home directory.Copycd Users/<your home folder name>/.ssh/ Note: The angle brackets <> should not appear in your code.Copyls Note in the output that there are two files, a private key: id_rsa and a public key: id_rsa.pub. Keep the private key safe and don’t share its content with anyone. The public key will be needed for various activities and can be uploaded to certain systems as well as copied and pasted to facilitate secure communications in the cloud.
  6. Make a note of where your SSH public and private key files are located. You may be asked to upload the file or to copy/paste the content in other labs for Oracle Cloud Services. Copy the key content exactly, capturing space after the key characters may render your key invalid. In the example below, you can use the gitbash cat command to display the public key file content. You can select the key file content and right-click to Copy the key. Or you can upload the file directly.Note: If you’ve already installed Git for Windows, don’t bother with PuTTY. It’s your choice which utility to use for key generation and terminal access.

Use PuTTY

In earlier versions of Windows, ssh-keygen was not a native utility, so third party utilities had to be utilized. In this section, we’ll illustrate using PuTTY. If you prefer Git for Windows, visit the option prior to this one.

  1. If you don’t already have it installed, access the link below and download the application. For Oracle employees, PuTTY is also available for download internally via the MyDesktop application. For non-Oracle employees and customers, use the below link. If you are unable to install anything on your laptop due to permission issues, please use the Oracle Cloud Shell option above.Click here to download PuTTY for Windows
  2. Follow the instructions for installation.Note: Installing PuTTY is beyond the scope of this lab document.Once installed, you should have an entry in your Windows Start menu, and perhaps a desktop shortcut for PuTTY. PuTTY is actually a suite of secure communication utilities. We’ll be using two of them, the PuTTY utility for terminal access and the PuTTYgen utility for generating a secure SSH key.
  3. Open the Windows start menu and navigate to the PuTTY folder. Select the PuTTYgen utility.
  4. Verify that the defaults are selected and the key type should be RSA set at 2048 bits. Click on the Generate button.
  5. Follow the instructions and move your mouse around the empty grey area to generate random information. PuTTY is using that information to generate a random, secure SSH key.
  6. In the below screen, PuTTY has taken your mouse information and created a key. We need to do several things here that are a little different than other key generation methods. Although we can’t actually use the file for an OCI Instance, we’ll still want to save the key for future reference. Click the Save public key button.Note: PuTTY does not save keys in an OpenSSH compatible format. Thus, if you upload a public key file created with PuTTY to a Linux/Unix system using OpenSSH, the key will not be read correctly. However, the key information itself, when copied directly from the PuTTYgen application, does work correctly when pasted into fields that then use that information to create a proper OpenSSH compatible key. For example, when creating an instance on OCI, you can paste the SSH key from PuTTY and it will work correctly.
  7. In the Save public key as: dialog, name your key and add the .pub extension to the filename. It will also be helpful if save the file in the common .ssh folder under your Windows username / folder structure. In this example the key-files will be accessible C:\Users\<username>\.ssh directory. Store the keys here for easy future reference.
  8. Next you will need to save the private key. Click the Save private key button, answer Yes to the warning about saving without a passphrase.
  9. Name the key and verify that it’s saved with a .ppk extension to identify the file as the private key file. Do not share your private key with anyone.
  10. Now you’ve saved the keys for future reference, all you have to do is copy the key information from the PuTTY dialog.
  11. Select the key text in the dialog box from start to finish, then right click and choose Copy. You can then paste the key into a Notepad or directly into the instance creation dialog in the OCI console.
  12. Below is an example of the Add SSH key – Paste SSH keys dialog in the OCI instance creation form.This concludes the section on using PuTTY to generate a SSH key pair for versions of Windows prior to Windows 10.Follow below instructions to connect to a cloud instance via SSH using the PuTTY terminal.

Connecting to an Instance Using PuTTY

  1. Open the PuTTY utility from the Windows start menu. In the dialog box, enter the IP address of your OCI Compute Instance. This can be obtained from the OCI Console > Compute > Instances > Instance Details screen.
  2. Under Category select Connection and then choose the Data field. Enter the assigned instance’s username. OCI instances will default to the username opc. Enter opc.
  3. Under Category, navigate to Connection – SSH and choose the Auth category. Click on the Browse button and locate the private key file you created in the earlier step. Click the Open button to initiate the SSH connection to your cloud instance.
  4. Click Yes to bypass the Security Alert about the uncached key.
  5. Connection successful. You are now securely connected to an OCI Cloud instance.You are now able to connect securely using the PuTTY terminal utility. You can save the connection information for future use and configure PuTTY with your own custom settings.Note: If you’ve already installed PuTTY, don’t bother with Git for Windows. It’s your choice which to use for key generation and terminal access.For more information on using PuTTY

Option 5: SSH Keys for Linux

  1. Open a terminal window and type in the ssh-keygen command. There are a few command line options for the ssh-keygen utility; however, for quick and dirty key creation for lab use, no options are necessary. Type ssh-keygen --help in your terminal window to see all the possible options.For now, just run the command by itself.Copyssh-keygen
  2. You should run this command from your home directory. In this case as the user-id opc. The dialog will default to a hidden directory, ~/.ssh. If you don’t already have keys created, accept the default file name id_rsa by hitting the Enter key. Press the Enter key two more times to create a key with no passphrase. The best practice in a production environment would be to use a secure passphrase; however, we don’t need to bother with these practice labs.The dialog will indicate that the key pair has been saved in the /home/username/.ssh directory and is now ready for use.
  3. Change to the .ssh directory, list and examine your keys.Copycd .ssh ls Note in the output that there are two files, a private key: id_rsa and a public key: id_rsa.pub. Keep the private key safe and don’t share its contents with anyone. The public key will be needed for various activities and can be uploaded to certain systems as well as copied and pasted to facilitate secure communications in the cloud.
  4. Use the Linux cat command to list the contents of id_rsa.pub.Copycat id_rsa.pub
  5. In some labs you will be asked to upload or copy (rcp) the public key to an instance in order to facilitate communications. So remember where the file is kept. Other labs will ask for the ‘contents’ of the key to be pasted into various dialog boxes to facilitate secure connections. Use the cat command and copy/paste the information from the key starting at the word “ssh-rsa” and copy everything up to the final character in the line. In the example below, you would copy from “ssh-rsa … “ and to exactly after “… -01”. Copy the key contents exactly, capturing space after the key characters may render your key invalid.You have created a public/private SSH key pair and can utilize it in any of the Oracle OCI labs that require an SSH key.

How To Know If An Object (Table / Procedure / View /…) Is Currently Being Used 

APPLIES TO:

Oracle Database Exadata Express Cloud Service – Version N/A and later
Oracle Database Cloud Service – Version N/A and later
Oracle Database – Enterprise Edition – Version 10.2.0.5 and later
Oracle Database Cloud Schema Service – Version N/A and later
Oracle Database Exadata Cloud Machine – Version N/A and later
Information in this document applies to any platform.

GOAL

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

The information is intended to help with situations where you want to know whether a certain object is being used, e.g.

1. If the objects is currently being used, e.g. by a DML statement, DDL on the objects will fail. How can you check it before executing the DDL command?

2. If a procedure “USER2.TEST_PROCEDURE” is being executed by user USER3 and owner USER2 wants to replace the procedure code using “create or replace procedure test_procedure ….”. How can you find the session that is running the procedure “USER2.TEST_PROCEDURE”?

SOLUTION

Situation 1

To check objects currently being used, e.g. by a DML statement, before executing a DDL command, you can query v$locked_object as in the example below.


Session A

SQL> connect user1/<PASSWORD>;
Connected.

SQL> drop table test;

Table dropped.

SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.



Session B

SQL> connect / as sysdba
Connected.

SQL> truncate table user1.test;
truncate table user1.test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
–> this is expected because user1.test is locked in Session A

SQL>
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
       lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
       lo.PROCESS, lo.LOCKED_MODE
from   dba_objects do, v$locked_object lo
where  do.OBJECT_ID = lo.OBJECT_ID
and    do.OWNER = ‘USER1’
and    do.OBJECT_NAME = ‘TEST’;

OWNER    OBJECT_NAME  OBJECT_ID  SESSION_ID ORACLE_USERNAME
——– ———— ———- ———- ——————————
OS_USER_NAME         PROCESS        LOCKED_MODE
——————– ————– ———–
USER1    TEST            63754         1079 USER1
oracle               23013                    3



From above, you can see user1.test is locked by Oracle user ‘USER1’ with OS user name ‘oracle’.
Session id is 1079, OS process id is 23013.

In case you are running a RAC system, then the command should be changed to below to detect the resource holder and instance number of holder session:

SQL> select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.inst_id,lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, gv$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = ‘USER1’
and do.OBJECT_NAME = ‘TEST’;

OWNER OBJEC OBJECT_ID INST_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
—————————— —– ———- ———- ———- —————————— —————————— ———————— ———–
USER1 TEST 87450 1 52 USER1 oracle 7906 3


You can kill the session to release the lock.

SQL> select SID, SERIAL# from v$session where sid = 1079;

SID        SERIAL#
———- ———-
1079       663

SQL> alter system kill session ‘1079,663’;

System altered.



Then the DDL can be executed successfully:

SQL> truncate table user1.test;

Table truncated.

Situation 2

If a DDL statement on an object, e.g. create or replace procedure, is hanging because another user is executing the procedure, you can find the blocking session as in the example below.

Using 3 sessions:

  1. Session A: connect as sysdba
  2. Session B: connect as user user2/<PASSWORD>
  3. Session C: connect as user USER3/<PASSWORD>


Session A

SQL>
connect / as sysdba
create user user2 identified by user2;
grant connect, resource to user2;
create user USER3 identified by USER3;
grant connect, resource to USER3;
grant execute on dbms_lock to user2;
grant execute on dbms_lock to USER3;



Session B

SQL>
connect user2/<PASSWORD>

create or replace procedure user2_test_p1 is
begin
  null;
end;
/

grant execute on user2_test_p1 to USER3;



Session C

SQL>
connect USER3/<PASSWORD>
begin
  user2.user2_test_p1;
  dbms_lock.sleep(120);
end;
/



Session A

SQL>
connect / as sysdba

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
       ob.kglnaobj obj_name, ob.KGLNAOWN obj_owner,
       ses.KSUUDNAM cur_user
from   x$kglpn pn, x$kglob ob, x$ksuse ses
where  ob.KGLNAOBJ=’USER2_TEST_P1′
and    (ob.KGLHDPMD <> 0
        or
        (ob.KGLHDPMD = 0 and ob.KGLHDLMD not in (0,1))
       )
and    ob.kglhdadr = pn.kglpnhdl
and    pn.kglpnuse = ses.addr;

SID        SERIAL#    OBJ_NAME             OBJ_OWNER       CUR_USER
———- ———- ——————– ————— ——————
1094       5909       USER2_TEST_P1          USER2             USER3

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and    b.serial#=5909;

SQL_TEXT
——————————————————————————–
begin user2.user2_test_p1; dbms_lock.sleep(120); end;



If the DDL is already hanging due to a lock, you can follow below test case to find the lock details.

Session C

SQL> connect USER3/<PASSWORD>

SQL>
begin
  user2.user2_test_p1;
  dbms_lock.sleep(120);
end;
/



Session B

SQL> connect user2/<PASSWORD>

SQL> alter procedure user2_test_p1 compile;
     –> This is hanging



Session A

col obj_name format a30
col pin_cnt format 999
col pin_mode format 999
col pin_req format 999
col state format a30
col event format a30
col wait_time format 999999999
col seconds_in_wait format 999999999

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
       ob.kglnaobj obj_name,
       pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req,
       w.state, w.event, w.wait_time, w.seconds_in_Wait
from   x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
where  pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
and    ob.kglhdadr = pn.kglpnhdl
and    pn.kglpnuse = ses.addr
and    w.sid = ses.indx
order by seconds_in_wait desc;

SID        SERIAL#    OBJ_NAME                       PIN_CNT PIN_MODE PIN_REQ
———- ———- —————————— ——- ——– ——-
STATE                          EVENT                          WAIT_TIME
—————————— —————————— ———-
SECONDS_IN_WAIT
—————
      1082       6765 USER2_TEST_P1                          3        2       0
WAITING                        PL/SQL lock timer                       0
             39

      1074       6060 USER2_TEST_P1                          0        0       3
WAITING                        library cache pin                       0
             27

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and    b.serial#=6765;

SQL_TEXT
——————————————————————————–
begin user2_test_p1; dbms_lock.sleep(120); end;

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and  b.serial#=6060;

SQL_TEXT
——————————————————————————–
alter procedure user2_test_p1 compile

In case you are running a RAC system, you can get the holder via hanganalyze trace:

eg:

SQL> conn / as sysdba
SQL> oradebug setmypid
SQL> oradebug -g all hanganalyze 3                       ============> this syntax only works for Oracle 11.2 or higher
Hang Analysis in /u01/app/oracle/diag/rdbms/abc/abc2/trace/abc2_diag_5180.trc

The trace file will show something like:

——————————————————————————-
Chain 1:
——————————————————————————-
Oracle session identified by:
{
instance: 2 (abc.abc2)
os id: 8829
process id: 44, oracle@xxx (TNS V1-V3)
session id: 57
session serial #: 23
}
is waiting for ‘library cache pin’ with wait info:
{
p1: ‘handle address’=0x8899a6b0
p2: ‘pin address’=0x8b45baf0
p3: ‘100*mode+namespace’=0x1559600010003
time in wait: 3 min 19 sec
timeout after: 11 min 40 sec
wait id: 301
blocking: 0 sessions
current sql: alter procedure user2_test_p1 compile
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-kjusuc()+3400<-ksipgetctxi()+1759<-kqlmPin()+2943<-kqlmClusterLock()+237<-kglpnal()+4044<-kglpin()+1373<-kkdllk0()+904<-kkdlGetCodeObject()+307<-kkpalt()+353<-opiexe()+18119<-opiosq0()+3932<-kpooprx()+274<-kpoal8()+842<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_
wait history:
* time between current wait and wait #1: 0.000137 sec
1. event: ‘library cache lock’
time waited: 0.000631 sec
wait id: 300 p1: ‘handle address’=0x8899a6b0
p2: ‘lock address’=0x898f2828
p3: ‘100*mode+namespace’=0x1559600010003
* time between wait #1 and #2: 0.001686 sec
2. event: ‘library cache pin’
time waited: 0.000642 sec
wait id: 299 p1: ‘handle address’=0x8987e208
p2: ‘pin address’=0x88a29c50
p3: ‘100*mode+namespace’=0x1084c00030002
* time between wait #2 and #3: 0.004552 sec
3. event: ‘SQL*Net message from client’
time waited: 4 min 24 sec
wait id: 298 p1: ‘driver id’=0x62657100
p2: ‘#bytes’=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (abc.abc1)
os id: 8388
process id: 43, oracle@xxx (TNS V1-V3)
session id: 43
session serial #: 115
}
which is waiting for ‘PL/SQL lock timer’ with wait info:
{
p1: ‘duration’=0x0
time in wait: 3 min 30 sec
timeout after: 196 min 29 sec
wait id: 77
blocking: 1 session
current sql: begin
user2.user2_test_p1;
dbms_lock.sleep(12000);
end;
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-psdwat()+169<-pevm_icd_call_common()+897<-pfrinstr_ICAL()+169<-pfrrun_no_tool()+63<-pfrrun()+627<-plsql_run()+649<-peicnt()+302<-kkxexe()+525<-opiexe()+18001<-kpoal8()+2118<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+253
wait history:
* time between current wait and wait #1: 0.001350 sec
1. event: ‘SQL*Net message from client’
time waited: 10.208592 sec
wait id: 76 p1: ‘driver id’=0x62657100
p2: ‘#bytes’=0x1
* time between wait #1 and #2: 0.002433 sec
2. event: ‘SQL*Net message to client’
time waited: 0.000001 sec
wait id: 75 p1: ‘driver id’=0x62657100
p2: ‘#bytes’=0x1
* time between wait #2 and #3: 0.000004 sec
3. event: ‘SQL*Net break/reset to client’
time waited: 0.000091 sec
wait id: 74 p1: ‘driver id’=0x62657100
p2: ‘break?’=0x0
}

Chain 1 Signature: ‘PL/SQL lock timer'<=’library cache pin’

How To Break And Restart A DataPump Export Or Import Job

1. DataPump Export

expdp system/<password> directory=tmp dumpfile=parttab001_%u.dmp logfile=expdp_parttab001.log tables=test_usr.parttab001 job_name=test_usr parallel=2

connect / as sysdba
select * from dba_datapump_jobs where job_name = ‘TEST_USR’

JOB_MODE
————

EXECUTING

To perform an orderly shutdown, use STOP_JOB (without any associated value). A warning requiring confirmation will be issued. An orderly shutdown stops the job after worker processes have finished their current tasks.

At this point break the expdp process with CTRL-C and then:

Export> stop_job
Are you sure you wish to stop this job ([yes]/no): y

The job status in the database changes to:

connect / as sysdba
select * from dba_datapump_jobs where job_name = ‘TEST_USR’

JOB_MODE
————

STOP PENDING

To perform an immediate shutdown, specify STOP_JOB=IMMEDIATE.

After the job was shutdown, the job status changes to:

connect / as sysdba
select * from dba_datapump_jobs where job_name = ‘TEST_USR’

JOB_MODE
————

NOT RUNNING 

The DataPump export job is now stopped.

To restart the job TEST_USR, first perform:

#> expdp system/<password> attach=test_usr

Then restart the job with:

Export> continue_client

2. DataPump Import

impdp system/<password> job_name=test_usr directory=tmp dumpfile=parttab001_%u.dmp tables=test_usr.parttab001 logfile=impdp_parttab001.log parallel=2

At this point, break the impdp process with CTRL-C and then:

Import> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): y

The DataPump import job is now stopped.

select * from dba_datapump_jobs where job_name = ‘TEST_USR’

To restart the job TEST_USR, first perform:

#> impdp system/<password> attach=test_usr

Then restart the job with:

Import> continue_client

How To Flush A Sql Statement From Shared Pool

Problem:

Multiple session was hanging on sqlid that was executing very well one day ago.

Solution:

Flush the single sql id rather than whole shared pool

  • Get the address and hash_value of the sql_id:
    select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id=’3jktm6bykn79w’;
  • Now purge the sql statement using sys
  • exec DBMS_SHARED_POOL.PURGE (‘ADDRESS,HASH_VALUE’,’C’);

exec DBMS_SHARED_POOL.PURGE (‘0700010156DED3F0,4247395644′,’C’);

PL/SQL procedure successfully completed.

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id=’3jktm6bykn79w’;

no rows selected

Oracle DBA Interview Question

Question: User called and said he or she dropped all tables in schema and want to restore it. How will you restore all tables?

Please post your answer in comments.

Oracle Script to find unindexed Foreign key that cause blocking

This script suggest index on foreign key that cause blocking

run this code and enable dbms output to see result. it will show existing index on the table and recommended index


SET SERVEROUTPUT ON
DECLARE
    PROCEDURE print_all (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;

    PROCEDURE print_ddl (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;
BEGIN
    DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform,
                                       'SEGMENT_ATTRIBUTES',
                                       FALSE);

    FOR a
        IN (  SELECT COUNT (*)                                       samples,
                     event,
                     p1,
                     p2,
                     o.owner                                         c_owner,
                     o.object_name                                   c_object_name,
                     p.object_owner                                  p_owner,
                     p.object_name                                   p_object_name,
                     id,
                     operation,
                     MIN (p1 - 1414332420 + 4)                       lock_mode,
                     MIN (sample_time)                               min_time,
                     MAX (sample_time)                               max_time,
                     CEIL (10 * COUNT (DISTINCT sample_id) / 60)     minutes
                FROM dba_hist_active_sess_history
                     LEFT OUTER JOIN dba_hist_sql_plan p USING (dbid, sql_id)
                     LEFT OUTER JOIN dba_objects o ON object_id = p2
                     LEFT OUTER JOIN dba_objects po
                         ON po.object_id = current_obj#
               WHERE     event LIKE 'enq: TM%'
                     AND p1 >= 1414332420
                     AND sample_time > SYSDATE - 15
                     AND p.id = 1
                     AND operation IN ('DELETE', 'UPDATE', 'MERGE')
            GROUP BY event,
                     p1,
                     p2,
                     o.owner,
                     o.object_name,
                     p.object_owner,
                     p.object_name,
                     po.owner,
                     po.object_name,
                     id,
                     operation
            ORDER BY COUNT (*) DESC)
    LOOP
        print_ddl (
               '--  '
            || a.operation
            || ' on '
            || a.p_owner
            || '.'
            || a.p_object_name
            || ' has locked '
            || a.c_owner
            || '.'
            || a.c_object_name
            || ' in mode '
            || a.lock_mode
            || ' for '
            || a.minutes
            || ' minutes between '
            || TO_CHAR (a.min_time, 'dd-mon hh24:mi')
            || ' and '
            || TO_CHAR (a.max_time, 'dd-mon hh24:mi'));

        FOR s
            IN (SELECT DISTINCT
                       REGEXP_REPLACE (
                           CAST (SUBSTR (sql_text, 1, 2000) AS VARCHAR2 (60)),
                           '[^a-zA-Z ]',
                           ' ')    sql_text
                  FROM dba_hist_active_sess_history
                       JOIN dba_hist_sqltext t USING (dbid, sql_id)
                 WHERE     event LIKE 'enq: TM%'
                       AND p2 = a.p2
                       AND sample_time > SYSDATE - 15)
        LOOP
            print_all ('--      ' || 'blocked statement: ' || s.sql_text);
        END LOOP;

        FOR c
            IN (WITH
                    c
                    AS
                        (SELECT p.owner          p_owner,
                                p.table_name     p_table_name,
                                c.owner          c_owner,
                                c.table_name     c_table_name,
                                c.delete_rule,
                                c.constraint_name
                           FROM dba_constraints  p
                                JOIN dba_constraints c
                                    ON (    c.r_owner = p.owner
                                        AND c.r_constraint_name =
                                            p.constraint_name)
                          WHERE     p.constraint_type IN ('P', 'U')
                                AND c.constraint_type = 'R')
                    SELECT c_owner                     owner,
                           constraint_name,
                           c_table_name,
                              CONNECT_BY_ROOT (p_owner || '.' || p_table_name)
                           || SYS_CONNECT_BY_PATH (
                                     DECODE (delete_rule,
                                             'CASCADE', '(cascade delete)',
                                             'SET NULL', '(cascade set null)',
                                             ' ')
                                  || ' '
                                  || c_owner
                                  || '"."'
                                  || c_table_name,
                                  ' referenced by')    foreign_keys
                      FROM c
                     WHERE     LEVEL <= 10
                           AND c_owner = a.c_owner
                           AND c_table_name = a.c_object_name
                CONNECT BY NOCYCLE     p_owner = PRIOR c_owner
                                   AND p_table_name = PRIOR c_table_name
                                   AND (   LEVEL = 1
                                        OR PRIOR delete_rule IN
                                               ('CASCADE', 'SET NULL'))
                START WITH     p_owner = a.p_owner
                           AND p_table_name = a.p_object_name)
        LOOP
            print_all (
                   '--      '
                || 'FK chain: '
                || c.foreign_keys
                || ' ('
                || c.owner
                || '.'
                || c.constraint_name
                || ')'
                || ' unindexed');

            FOR l
                IN (SELECT *
                      FROM dba_cons_columns
                     WHERE     owner = c.owner
                           AND constraint_name = c.constraint_name)
            LOOP
                print_all ('--         FK column ' || l.column_name);
            END LOOP;

            print_ddl (
                   '--      Suggested index: '
                || REGEXP_REPLACE (
                       TRANSLATE (
                           DBMS_METADATA.get_ddl ('REF_CONSTRAINT',
                                                  c.constraint_name,
                                                  c.owner),
                           CHR (10) || CHR (13),
                           '  '),
                       'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].*[)]).* REFERENCES ".*',
                       'CREATE INDEX ON \1 \3;'));

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE     (index_owner, index_name) IN
                                   (SELECT owner, index_name
                                      FROM dba_indexes
                                     WHERE     owner = c.owner
                                           AND table_name = c.c_table_name)
                           AND column_name IN
                                   (SELECT column_name
                                      FROM dba_cons_columns
                                     WHERE     owner = c.owner
                                           AND constraint_name =
                                               c.constraint_name))
            LOOP
                print_ddl ('--      Existing candidate indexes ' || x.ddl);
            END LOOP;

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE (index_owner, index_name) IN
                               (SELECT owner, index_name
                                  FROM dba_indexes
                                 WHERE     owner = c.owner
                                       AND table_name = c.c_table_name))
            LOOP
                print_all ('--       Other existing Indexes: ' || x.ddl);
            END LOOP;
        END LOOP;
    END LOOP;
END;
/


Sample Ouput

— MERGE on hr.addr has locked hr.address in mode 5 for 5 minutes between 09-aug 15:56 and 13-aug 14:50
— blocked statement: MERGE INTO addr C USING SELECT C CNTC ID FROM ac
— FK chain: hr.addr referenced by hr”.”address (hr.add4) unindexed
— FK column CNTC_ID_PAR
— Suggested index: CREATE INDEX ON “hr”.”address” (“CNTC_ID_PAR”);
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE INDEX “hr”.”ITD_address_NDX1″ ON “hr”.”address” (“CNTC_ID”)

PLS-00306: wrong number or types of arguments in call to ‘GETDCBACKUPHISTORY’ RMAN-06004 ORA-06550

SYMPTOMS

Rman backup fails when connected through catalog. But when rman connects directly the jobs run OK.

DBGSQL: RCVCAT> declare first boolean := FALSE;
bhistoryRec dbms_rcvman.bhistoryRec_t;
atAnyScn boolean := FALSE; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
begin if (:first > 0) THEN first := TRUE; end if;
if (:atAnyScn > 0) THEN atAnyScn := TRUE; end if;
dbms_rcvman.getDcBackupHistory( backedUpDev => :devtype:devtype_i, first => first, bhistoryRec => bhistoryRec, doingCmd => :backup, keepTag => :tag, toDest1 => :todest1, toDest2 => :tod est2, toDest3 => :todest3, toDest4 => :todest4, atAnyScn => atAnyScn );
:fileno := bhistoryRec.dfNumber; :crescn := bhistoryRec.create_scn; :rlgscn := bhistoryRec.reset_s cn;
:rlgtime := bhistoryRec.reset_time; :ckpscn := bhistoryRec.ckp_scn; :nbackups := bhistoryRec.nbackups;
:compTime := bhistoryRec.compTime; end;
DBGSQL: sqlcode = 6550
DBGSQL: B :fileno = NULL
DBGSQL: B :crescn = NULL
DBGSQL: B :rlgscn = NULL
DBGSQL: B :rlgtime = NULL
DBGSQL: B :ckpscn = NULL
DBGSQL: B :nbackups = NULL
DBGSQL: B :compTime = NULL
DBGSQL: B :first = 1
DBGSQL: B :devtype = SBT_TAPE
DBGSQL: B :backup = B
DBGSQL: B :tag = NULL
DBGSQL: B :todest1 = NULL
DBGSQL: B :todest2 = NULL
DBGSQL: B :todest3 = NULL
DBGSQL: B :todest4 = NULL
DBGSQL: B :atAnyScn = 0
released channel: T1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 09/09/2019 10:27:58
RMAN-06004: ORACLE error from recovery catalog database: ORA-06550: line 1, column 211:
PLS-00306: wrong number or types of arguments in call to ‘GETDCBACKUPHISTORY’
ORA-06550: line 1, column 211:
PL/SQL: Statement ignored

CAUSE 1

A new variable, atAnyScn, is passed to dbms_rcvman.GETDCBACKUPHISTORY in new RMAN catalog versions.
If this variable is not part of the dbms_rcvman description then this error is returned.
 
DESCRIBE on dbms_rcvman on recovery catalog schema is not showing this variable atAnyScn

 

SOLUTION 1

Run DESCRIBE on dbms_rcvman on recovery catalog schema and see if it’s showing this variable : atAnyScn

a.  Connected to the catalog schema with sqlplus, execute:

SQL>  desc dbms_rcvman;

b.  Check for the existence of atAnyScn under PROCEDURE GETDCBACKUPHISTORY

c.  If it does not exist, this catalog schema needs to be upgraded.

CAUSE 2

forgot upgrade catalog after datapatch -verbose

 

SOLUTION 2

rman catalog username/password@rman

RMAN> UPGRADE CATALOG;

RMAN> UPGRADE CATALOG;

ORA-16688: command cannot be issued on a disabled member

Cause: Disable and Enable standby database when connecting to standby database

dgmgrl sys@stby

DGMGRL> disable database stby
Disabled.

DGMGRL> enable database stby
Error: ORA-16688: command cannot be issued on a disabled member

Failed.

DESCRIPTION

trying to enable database from disable standby database and that failed

Solution:

connect with primary database instead of standby and issue command again

dgmgrl sys@prmy

DGMGRL> enable database stby
Enabled.

ORA-12514: TNS:listener does not currently know of service requested in connect

it can be one of these

  • check database status for single instance and CDB and PDB status for multitenant
  • srvctl status database -d dbname or show pdbs from sqlplus
  • check services status with – srvctl status service -d dbname
  • check OUD or TNS entry for correct service name
  • check remote_listener parameter – show parameter remote_listener
  • check PDB name

Single quotes in strings – oracle SQL query

Apostrophe/single quote in a concatenated string

If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Today' || '''' || 's Date: ' || sysdate FROM dual;

Today’s Date: 09-DEC-20

select 'PDB Name is : ' || name || ':' from v$pdbs;

PDB Name is : ABC:

SELECT 'Hello' || '''' || 's World' FROM dual;

Hello’s World

Apostrophe/single quote at start of string

When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT '''Hello World' FROM dual;

would return

'Hello World

Apostrophe/single quote in the middle of a string

When the apostrophe/single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:

SELECT 'She''s Here' FROM dual;

would return

She's Here

Apostrophe/single quote at the end of a string

When the apostrophe/single quote is at the end of a string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Worlds''' FROM dual;

would return

Worlds'
%d bloggers like this: