Friday, July 31, 2020

Start RHEL 7/ OL 7 in Graphical mode (GUI)

[root@odi12c ~]# yum groupinstall "Server with GUI"
[root@odi12c ~]# systemctl get-default
multi-user.target
[root@odi12c ~]# systemctl set-default graphical.target
Removed symlink /etc/systemd/system/default.target.
Created symlink from /etc/systemd/system/default.target to /usr/lib/systemd/system/graphical.target.
[root@odi12c ~]# systemctl get-default
graphical.target
[root@odi12c ~]# reboot

Saturday, July 18, 2020

clean up .patch_storage in /u01 after quarterly RU installations

[oracle@dbhost1 u01]$ du -sh /u01/*/.patch_storage
6.6G    /u01/db/.patch_storage
9.8G    /u01/grid/.patch_storage


[oracle@dbhost1 u01]$ $ORACLE_HOME/OPatch/opatch util cleanup -help
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


DESCRIPTION
     This utility cleans up 'restore.sh,make.txt' files and 'scratch,backup'
     directories of the.patch_storage directory of Oracle Home.If -ps option is used,
     then, it cleans the above specified areas only for that patch, else for all
     patches under ORACLE_HOME/.patch_storage. You will be still able to
     rollback patches after this cleanup.

SYNTAX
opatch util cleanup  [-invPtrLoc ]
                     [-jre ] [-oh ]
                     [-silent] [-report]
                     [-ps , this will
                       be located under ORACLE_HOME/.patch_storage/]

OPTIONS
       -invPtrLoc
              Used to locate the oraInst.loc file. Needed when the
              installation used the -invPtrLoc flag. This should be
              the path to the oraInst.loc file.

       -jre
              This option tells OPatch to use JRE (java) from the
              specified location instead of the default location
              under Oracle Home. Both -jdk and -jre options cannot
              be specified together. OPatch will display error in
              that case.

       -oh
              The oracle home to work on. This takes precedence over
              the environment variable ORACLE_HOME.

       -ps
              This option is used to specify the Patch ID with timestamp.
              This Patch ID with timestamp should be the same as in
              .patch_storage directory.

              A directory by this name will be present under
              ORACLE_HOME/.patch_storage. If this directory is specified
              and is valid, then the contents specified in the description
              will be cleaned up only for this patch. Otherwise, all patch
              related directories will be acted upon by this utility.

      -silent
              In silent mode, the cleanup always takes place.

      -report
              Prints the operations without actually executing them.


OPatch succeeded.

[oracle@dbhost1 u01]$ $ORACLE_HOME/OPatch/opatch util cleanup -oh /u01/grid
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/grid/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.2.0.7.0
Log file location : /u01/grid/cfgtoollogs/opatch/opatch2020-07-18_10-24-18AM_1.log

Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y

Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.

OPatch succeeded.

Additionally, further cleanup of the $ORACLE_HOME/.patch_storage is possible if there are directories from patches applied to previous versions.


[oracle@dbhost1 .patch_storage]$ du -sh /u01/grid/.patch_storage/[23]*
248K    /u01/grid/.patch_storage/29517242_Apr_17_2019_23_27_10
32K     /u01/grid/.patch_storage/29517247_Apr_1_2019_15_08_20
76K     /u01/grid/.patch_storage/29585399_Apr_9_2019_19_12_47
928M    /u01/grid/.patch_storage/30122149_Sep_19_2019_19_36_02
442M    /u01/grid/.patch_storage/30122167_Sep_3_2019_00_43_16
731M    /u01/grid/.patch_storage/30125133_Oct_9_2019_00_10_29
626M    /u01/grid/.patch_storage/30489227_Jan_7_2020_03_37_45
484M    /u01/grid/.patch_storage/30489632_Dec_24_2019_03_32_55
1.2G    /u01/grid/.patch_storage/30557433_Jan_6_2020_19_07_34
3.4M    /u01/grid/.patch_storage/30655595_Dec_12_2019_04_55_54
1.2G    /u01/grid/.patch_storage/30869156_Apr_6_2020_23_20_53
564M    /u01/grid/.patch_storage/30869304_Feb_16_2020_07_11_33
988M    /u01/grid/.patch_storage/30894985_Apr_10_2020_05_35_01
3.4M    /u01/grid/.patch_storage/30898856_Feb_13_2020_21_26_23
1.7G    /u01/grid/.patch_storage/31281355_Jul_6_2020_11_18_02
564M    /u01/grid/.patch_storage/31304218_Jun_29_2020_05_07_23
619M    /u01/grid/.patch_storage/31305087_Jun_25_2020_11_36_08
3.3M    /u01/grid/.patch_storage/31335188_May_12_2020_05_27_58

[oracle@dbhost1 .patch_storage]$ du -sh /u01/db/.patch_storage/[23]*
248K    /u01/db/.patch_storage/29517242_Apr_17_2019_23_27_10
76K     /u01/db/.patch_storage/29585399_Apr_9_2019_19_12_47
212M    /u01/db/.patch_storage/30122149_Sep_19_2019_19_36_02
751M    /u01/db/.patch_storage/30125133_Oct_9_2019_00_10_29
425M    /u01/db/.patch_storage/30128191_Aug_29_2019_23_53_58
227M    /u01/db/.patch_storage/30489227_Jan_7_2020_03_37_45
1.2G    /u01/db/.patch_storage/30557433_Jan_6_2020_19_07_34
426M    /u01/db/.patch_storage/30805684_Feb_21_2020_20_52_36
1.3G    /u01/db/.patch_storage/30869156_Apr_6_2020_23_20_53
227M    /u01/db/.patch_storage/30894985_Apr_10_2020_05_35_01
426M    /u01/db/.patch_storage/31219897_Jul_8_2020_01_57_16
1.4G    /u01/db/.patch_storage/31281355_Jul_6_2020_11_18_02
215M    /u01/db/.patch_storage/31305087_Jun_25_2020_11_36_08

rm -rf    /u01/grid/.patch_storage/29517242_Apr_17_2019_23_27_10
rm -rf    /u01/grid/.patch_storage/29517247_Apr_1_2019_15_08_20
rm -rf    /u01/grid/.patch_storage/29585399_Apr_9_2019_19_12_47
rm -rf    /u01/grid/.patch_storage/30122149_Sep_19_2019_19_36_02
rm -rf    /u01/grid/.patch_storage/30122167_Sep_3_2019_00_43_16
rm -rf    /u01/grid/.patch_storage/30125133_Oct_9_2019_00_10_29
rm -rf    /u01/grid/.patch_storage/30489227_Jan_7_2020_03_37_45
rm -rf    /u01/grid/.patch_storage/30489632_Dec_24_2019_03_32_55
rm -rf    /u01/grid/.patch_storage/30557433_Jan_6_2020_19_07_34
rm -rf    /u01/grid/.patch_storage/30655595_Dec_12_2019_04_55_54
rm -rf    /u01/grid/.patch_storage/30869156_Apr_6_2020_23_20_53
rm -rf    /u01/grid/.patch_storage/30869304_Feb_16_2020_07_11_33
rm -rf    /u01/grid/.patch_storage/30894985_Apr_10_2020_05_35_01
rm -rf    /u01/grid/.patch_storage/30898856_Feb_13_2020_21_26_23

rm -rf     /u01/db/.patch_storage/29517242_Apr_17_2019_23_27_10
rm -rf     /u01/db/.patch_storage/29585399_Apr_9_2019_19_12_47
rm -rf     /u01/db/.patch_storage/30122149_Sep_19_2019_19_36_02
rm -rf     /u01/db/.patch_storage/30125133_Oct_9_2019_00_10_29
rm -rf     /u01/db/.patch_storage/30128191_Aug_29_2019_23_53_58
rm -rf     /u01/db/.patch_storage/30489227_Jan_7_2020_03_37_45
rm -rf     /u01/db/.patch_storage/30557433_Jan_6_2020_19_07_34
rm -rf     /u01/db/.patch_storage/30805684_Feb_21_2020_20_52_36
rm -rf     /u01/db/.patch_storage/30869156_Apr_6_2020_23_20_53
rm -rf     /u01/db/.patch_storage/30894985_Apr_10_2020_05_35_01


[oracle@dbhost1 .patch_storage]$ du -sh /u01/*/.patch_storage
2.0G    /u01/db/.patch_storage
2.8G    /u01/grid/.patch_storage

Sunday, July 12, 2020

SQL Micro - Top N View (from Oracle 19.6 onwards)

create or replace function top_n (
  num_rows number, tab dbms_tf.table_t
) return varchar2 sql_macro is
begin
  return 
    'select * from top_n.tab
     fetch first top_n.num_rows 
       rows only';
end;
/

select * from top_n(5,employees);