LOAD LINUX HPUX disk space info through ORACLE external table into SQL SERVER TABLE

I have exported all my Windows Disk info from SQL server views into my Metrics database, so that I can expect the disk growth and check if disk is going to full in how many days. (will introduce next time). But for my 10 oracle physical servers, I manually collect to Excel and import to SQL table, present in SSRS report. This is time consuming. Today I have create on external table in ORACLE and made it happen automatically, same like my other SQL night DBA, ORACLE night DBA (backup alert) etc. my Oracle servers on a few HP UX servers and a few LINUX servers, except some part (I listed two), most are same for both systems.
Thanks to https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5088536900346242095 


----create directory
>mkdir -p /home/oracle/diskspace
--create run_df.sh

     >
[9004] $ vi .bashrc

     export PATH=$PATH:/home/oracle/diskspace
   

[9005] $ source .bashrc


[9007] $ vi run_df.sh
      1 #!/bin/sh
      2
      3 /bin/df -kP
~


oracle@hpux:/home/oracle/diskspace>  cat run_df.sh
#!/usr/bin/sh

/usr/bin/df -kP

or
/home/oracle/diskspace/run_df.sh
[2546] $ cat run_df.sh
#!/bin/sh
/bin/df


-----add x
oracle@hpux:/home/oracle/diskspace> chmod +x run_df.sh
oracle@hpux:/home/oracle/diskspace> run_df.sh

----create directory
 create or replace directory EXEC_DIR as '/home/oracle/diskspace';
   ---grant permission to
   grant read,write,execute on directory exec_dir to DBAREPORTER;

 




----create table

CREATE TABLE df
   (
    "FILESYSTEM" VARCHAR2(200),
    "BLOCKS" NUMBER,
    "USED" NUMBER,
      "AVAILABLE" NUMBER,
     "CAPACITY" VARCHAR2(10),
      "MOUNT" VARCHAR2(100)
    )
   ORGANIZATION external
   (
     TYPE oracle_loader
     DEFAULT DIRECTORY exec_dir
     ACCESS PARAMETERS
     (
       RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
           preprocessor  exec_dir:'run_df.sh'
       READSIZE 1048576
       SKIP 1
       FIELDS TERMINATED BY WHITESPACE LDRTRIM
       REJECT ROWS WITH ALL NULL FIELDS
       (
         "FILESYSTEM" CHAR(255)
           TERMINATED BY WHITESPACE,
         "BLOCKS" CHAR(255)
           TERMINATED BY WHITESPACE,
         "USED" CHAR(255)
           TERMINATED BY WHITESPACE,
         "AVAILABLE" CHAR(255)
           TERMINATED BY WHITESPACE,
         "CAPACITY" CHAR(255)
           TERMINATED BY WHITESPACE,
         "MOUNT" CHAR(255)
           TERMINATED BY WHITESPACE
       )
     )
     location
     (
       exec_dir:'run_df.sh')
   )
 
   ;
   
 alter table df reject limit unlimited;

SQL> grant select on sys.df to DBAREPORTER;

 
   --test
  SELECT  b.host_name,b.instance_name,TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') as Today, a.* from sys.df a, (select host_name,instance_name from v$instance) b ;
---------

Comments

Post a Comment

Popular posts from this blog

Sysaux tablespace is too big

Image
 today I received my report alert regarding one of Oracle database tablespace SYSAUX is over 80% (I created automated job using SSIS package to check tablespace usage daily) following steps to resolve it:  /*  2021.07.08  Documentation steps for troubleshooting Sysaux tablespace is huge --85% full  */   ---1. see what uses the most select occupant_name,occupant_desc, space_usage_kbytes/1024 MB from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes desc; ---find it is SM/Advisor Server Manageability - Advisor Framework 25GB --now look for top 10 big objects   select * from (                select bytes/1024/1024 MB, blocks, s.SEGMENT_NAME, s.partition_name, s.segment_type, s.tablespace_name                  from dba_segments s                 where owner='SYS'             ...
Read more

SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future

  SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future when I change sql agent service AD user password to meeting security requirement, I used the below powershell script and change all my 200+ sql instances sql agent service, no service restart required. Today just found after I done a patching on one of my clusters, the sql agent service didn't start, I thought the password was old, and retype pwd, still failed to start, then I tried to reload the AD user by search user name in AD and type the password, it works. script : [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null $text=Get-Content C:\sql\serverlist0.txt $TotalComputers=$text.Length write-host "the total number of computers is $TotalComputers" For ($i=0; $i -lt $text.Length; $i++) {     # write-host " the server name is $text[$i] being working on "     $SMOWmiserver = New-Object (‘Microsoft.SqlServ...
Read more

GENERATE RSA KEYS WITH SSH BY USING PUTTYGEN

 GENERATE RSA KEYS WITH SSH BY USING PUTTYGEN  5.12.3.9.1 Autologin (TFDBA runbook Unix/Linux Tips) 1. Use puttygen to create keys, move mouse to create random 2. Save keys public and private, if doesn’t want passphrase to login using keys, ignore the passphrase 3. Copy and paste the output on the screen of puttygen to clipboard (for HPUX use "...\putty\PuttyKeysPublicPrivate\authorized_keys" or linux one for linux,  open and copy and paste) 4. In Linux Server, under your name, create $HOME/.ssh/authorized_keys file and paste step 3 to it and save Same as HPUX, for root it is /.ssh/authorized_keys, append the keys (paste below existing if there is) 5. In putty ->connection->SSH->Auth load the private key  :\putty\PuttyKeysPublicPrivate\testCernerPrivateKey.ppk 6. Save session. (connection->data_autologin name, add your loginname)
Read more