sqlplus - new features ( 12c to 18c)

The following are important new sqlplus  features  in 12c & 18c






1)SET FEEDBACK ON SQL_ID


It will show sql_id for the sql statements we execute from sqlplus prompt


SQL> create table test_invisible_cols (emp_id number, emp_info char(20),
                                  emp_acc_no number invisible);  2
Table created.


SQL_ID: cw7wvs1x99d72




2) SET ROWLIMIT 10


  it limits the number of rows returned from a sql




3) set linesize window


It will make the columns to fit into the window size




4) SET COLINVISIBLE ON


This will allow to show the invisible columns using desc




SQL> create table test_invisible_cols (emp_id number, emp_info char(20),
                                  emp_acc_no number invisible);  2
Table created.



SQL> desc test_invisible_cols
 Name                                                                                                                                    Null?    Type
 --------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------
 EMP_ID                                                                                                                                           NUMBER
 EMP_INFO                                                                                                                                         CHAR(20)


SQL> SET COLINVISIBLE ON


SQL> desc test_invisible_cols
 Name                                                                                                                                    Null?    Type
 --------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------
 EMP_ID                                                                                                                                           NUMBER
 EMP_INFO                                                                                                                                         CHAR(20)
 EMP_ACC_NO (INVISIBLE)                                                                                                                           NUMBER
SQL>




5)  sqlplus -nologintime


from 12.2 sqlplus connection will show the last_login ( dba_users has additional column last_login).


if we want to disable the last login time show, we can use -nologintime




6) set history on - Another important feature is sqlplus history
     - we can list the history
     - we can run the command out of the history list
    - we can edit a command from history
   - we can delete all/a command from history
 ( hist clear/ hist 1 delete )





SQL> set history on



SQL> set history on
SQL> select count(*) from dba_objects;
  COUNT(*)
----------
     60537
SQL> show sga
Total System Global Area 3.9278E+10 bytes
Fixed Size                 30153104 bytes
Variable Size            7381975040 bytes
Database Buffers         3.1675E+10 bytes
Redo Buffers              190418944 bytes

SQL> show parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FLASH01
db_recovery_file_dest_size           big integer 17793M
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

SQL> history
  1  select count(*) from dba_objects;
  2  show sga
  3  show parameter recovery


SQL> history 2 run
Total System Global Area 3.9278E+10 bytes
Fixed Size                 30153104 bytes
Variable Size            7381975040 bytes
Database Buffers         3.1675E+10 bytes
Redo Buffers              190418944 bytes
SQL>





SQL> help history
 HISTORY
 -------
 Stores, lists, executes, edits of the commands
 entered during the current SQL*Plus session.
 HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]
 N is the entry number listed in the history list.
 Use this number to recall, edit or delete the command.
 Example:
 HIST 3 RUN - will run the 3rd entry from the list.
 HIST[ORY] without any option will list all entries in the list.

SQL>

How to connect Oracle database using Pything 3.7

To connect oracle database from python ,we need cx_Oracle module to be installed.
let us first go thru  how to install cx_Oracle module and followed by writing a python script using this module

 

Step 1. Download & Install cx_Oracle ( Detail installation steps are in https://oracle.github.io/python-cx_Oracle/)

 

 

Installation methods.

 

There are various methods to install python modules. the following are most popular methods

•      pip
•      git
•      Using source from pypi

I used the last option. let us quickly go thru all the steps

1.1 PIP:

python -m pip install cx_Oracle --upgrade

incase if proxy for the internet is not set you want to use the following

python -m pip install --upgrade --user --proxy http://proxy.xxxx.com:8080 cx_oracle

1.2 GIT:

git clone https://github.com/oracle/python-cx_Oracle.git cx_Oracle
cd cx_Oracle
git submodule init
git submodule update
python setup.py install

1.3 Install Using Source from PyPI ( https://pypi.org/project/cx_Oracle/#files)

The source package can be downloaded manually from PyPI and extracted, after which the following commands should be run from the target python version

python setup.py build
python setup.py install

I downloaded cx_Oracle version 7 on 3.7 python
 

 

Note:
cx_Oracle 7 has been tested with Python version 2.7, and with versions 3.5 through 3.7. You can use cx_Oracle with Oracle 11.2, 12.1 and 12.2 and 18.3 client libraries. Oracle's standard client-server version interoperability allows connection to both older and newer databases. For example Oracle 18.3 client libraries can connect to Oracle Database 11.2.

 

 

2 . Database connection script

cat db1.py

import sys
import cx_Oracle
username ='user1'
password='password'
databaseName='connect_string'
con = cx_Oracle.connect( username,password,databaseName)
cursor=con.cursor()
cursor.execute('select startup_time,sysdate from v$instance')
result=cursor.fetchall ()
for row in result:
   print ( row[0] )
   print ( row[1] )

Execution:

./python db1.py
2018-11-12 02:06:38
2018-11-13 02:54:36

 

 

 

No space left on device" while fileystem has lots of free space - Inode header full - Unix

No space left on device" while fileystem has lots of free space . Sometime it happens due to inode header is full. Unix keep tack of file details in the inode header. incase if too many files under a file-system or  folder may cause the inode header to become full.


-- df shows it has 61% free space
df -h .
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/rootvg-u01
                      9.8G  5.6G  3.7G  61% /u01


-- df -I  shows it has 100% full.
df -i .
Filesystem           Inodes  IUsed IFree IUse% Mounted on
/dev/mapper/rootvg-u01
                     655360 655360     0  100% /u01






use the below command to find out the folder with huge files




find /u01 -xdev -printf '%h\n' | sort | uniq -c | sort -k 1 -n




we may need to either house keep or move it to different folder the unwanted files to make the inode header free

Exadata - centralized management utilities

Exadata has the following utilitities which facilitates centralized management across an Oracle Exadata System Software realm
   dcli
   cellcli
   dbmcli
  

=> dcli    :utility runs commands on multiple compute nodes and cells in parallel threads
=> cellcli :The Cell Control Command-Line Interface (CellCLI) utility is the command-line administration tool for Oracle Exadata Storage Server.
=> dbcli    :utility is the command-line administration tool for configuring database servers,


dcli examples:
 Assume dbs_group file contains compute nodes & cell_group file contains cell servers
cat  dbs_group
    db1comp0101.yg.com
    db1comp0102.yg.com
    db1comp0103.yg.com
    db1comp0104.yg.com
    db1comp0105.yg.com
    db1comp0106.yg.com
    db1comp0107.yg.com
    db1comp0108.yg.com

cat  cell_group
    cell1server0101.yg.com
    cell1server0102.yg.com
    cell1server0103.yg.com
    cell1server0104.yg.com
    cell1server0105.yg.com
    cell1server0106.yg.com
    cell1server0107.yg.com
    cell1server0108.yg.com
    cell1server0109.yg.com
    cell1server0110.yg.com
    cell1server0111.yg.com
    cell1server0112.yg.com
    cell1server0113.yg.com
    cell1server0114.yg.com

1. to run a command in all compute nodes
   dcli -g <group_file>  -l <user> <command>
   dcli -g dbs_group -l oracle df -h

2. to run a command in all cell servers ( assume oracle has ssh key set for cellmonitor account)
   dcli -g <group_file>  -l <user> <command>
   dcli -g cell_group -l cellmonitor cellcli -e list griddisk

3. to copy a file from one compute node to all computes

   dcli -g dbs_group -l oracle -f <filename> -d <target_location>
   dcli -g dbs_group -l oracle -f /tmp/p27681568_180000_Linux-x86-64.zip -d /u01/app/oracle/patch/

4. unzip the files

   dcli -l oragrid -g dbs_group unzip -oq -d <target_directory>  <patch_file>
   dcli -l oragrid -g dbs_group unzip -oq -d /u01/app/oracle/patch/  /u01/app/oracle/patch/p27681568_180000_Linux-x86-64.zip

How to onboard ZFS ( Backup appliance) into OEM EM12c/EM13c

These  steps prepared based on Oracle documentation to enable Monitoring for Oracle ZFS Appliance in Oracle Enterprise Manager 12c Version 12.1.0.5.


OMS Version: 12.1.0.5
Plugin version : 12.1.0.6/12.1.0.7
ZFS plugin      : 2013.06.05.7.14,1-1.1/ 8.7.14

1 .  UNIX SA Task:   Preparing Oracle ZFS Storage Appliance for Oracle Enterprise Manager Monitoring

Log in to the Oracle ZFS Storage Appliance BUI as user root and go to the Maintenance/Workflows tab.

1. Run the Configure for Oracle Enterprise Manager Monitoring workflow, entering and recording the password for the oracle_agent user. You can rerun the workflow later to reset the worksheet, re-enable data sets, or recreate the user.
   
   When you receive the message that oracle_agent already exists and you get asked whether to recreate the user, please answer NO.
   
   The following occurs when the workflow is run:

•        A restricted role user named oracle_user is created for use by the monitoring agent. The restricted access role associated with the oracle_agent user is also named oracle_agent.
•        An analytics worksheet is automatically created to ensure smooth communication between the Cloud Control administrator and the storage administrator tasked with administering the target Oracle ZFS Storage Appliance.

     2. Got to Configuration/USERS and edit the user oracle_agent by clicking the pencil on the very right side of the user’s entry.
      3. Ensure that the “Kiosk user” checkbox is unchecked.
      4. Press APPLY button in case you changed the user.
      5. Enable TLS V1.0 Protocol ( Configuration => HTTPS=> Edit SSL/TSL => check TLS V1.0 Protocol )  ( this step is required only if we use OEM 12.1.0.5 + ZFS plugin
         ( 12.1.0.6/12.1.0.7 , for EM13c, we can skip this step)
      6.Communicate the oracle_agent password to the OEM  Administrator

2: OEM Admin Task :   Discover / Add ZFS Storage Appliances to OEM 12c

Task 2.1 :  Push the plugin to the target agent from OEM
    1. Login to OEM12c Enterprise Manager with an Account with OEM ADMIN privilege
    2 Select Setup → Extensibility → Plug-ins → Servers,Storage & Network → Oracle ZFS Storage Appliance
    3. Select Actions → Deploy On → Management Agent → Continue
    4. Enter the "Management Agent" ( zfsappliance.kh.com:3830) → continue → Next
    5. Once plugin installation is successful.
    6. Login to the agent host ( zfsappliance.kh.com)
    7. Create blackout & Stop agent
          . oraenv
           <AGENT/AGENT12C>
           emctl start blackout ZFS_ONBOARD -nodelevel -d 01:00
           emctl stop agent
    8. cd /data/oracle/product/agent12c/plugins/oracle.sun.oss7.agent.plugin_12.1.0.7.0/scripts/emx/sun_storage_7000
            (  <agent_home>/plugins/oracle.sun.oss7.agent.plugin_12.1.0.7.0/scripts/emx/sun_storage_7000/   )


    9. Backup current Oracle_Grid.jar as Oracle_Grid.jar.bak
         ( cp Oracle_Grid.jar Oracle_Grid.jar.bak )


    10. Download the latest Oracle_Grid.jar  [ This step is required only if we use OMS 12.1.0.5 + plugin version 12.1.0.6/12.1.0.7 + ZFS image version > 8 ( 8.1.14) ]
              ( Alternatively this jar can be downloaded from Note 2384693.1 )


   11. Install latest JDK ( take backup of the current JDK)
 this patch can be downloaded from MOS 1944044.1


           $AGENT_HOME/jdk/bin/java -version
         cd $AGENT_HOME          
          unzip p19138237_16085_Linux-x86-64.zip
    
         ./jdk-6u85-linux-x64.bin


     $AGENT_HOME/jdk/bin/java -version    


 11. start agent & remove blackout
        emctl start agent
        emctl stop blackout ZFS_ONBOARD
        
Task 2.2 : Discover the target in OEM

1. Login to OEM12c Enterprise Manager with an Account with ADD TARGET privilege
2. Select Setup -> Add Target -> Add Targets Manually
3. Select Add Targets Declaratively by Specifying Target Monitoring Properties
4. As Target Type choose Oracle ZFS Storage Appliance
5. For Monitoring Agent click the Search Button and select as

              Management Agent:zfsappliance.kh.com:3830

     
     6. Press Add Manually
   7. In the opening window enter the target details as follows:
        Target
           Target Name: zfsappliance.kh.com
           Monitoring Target Credentials
           UserName: oracle_agent
            Password / Confirm Password: <oracle_agent password>
       Properties            Management Port: 215
            Storage Server Name or IP Address: zfsappliance-netman.kh.com
      Global Properties
           Department: Standard Database Support
           Lifecycle Status: Production
           Location: Germany
           Contact : DB_GLOBAL_DATABASE
           Press OK


8.Verify that target has been added and appears as up and running.


9. Repeat steps 2) – 8) for the all the heads in zfs appliance