How to unzip to a specific folder in Unix-based operating systems

Use the -d switch and redirect the content of the zip file to the desired directory

unzip myzipfile.zip -d myfolder

How to create a stored procedure for partition maintenance, executed by the Oracle Scheduler.

I needed to setup a small job that would remove partitions from a table with records older than 35 days.
The procedure should be flexible enough to accept different tables in different schemas, and there should be some basic logging, too.

Below is how I solved it.


First, grant permission, setup the logging table and add a sequence:

connect / as sysdba
grant select on dba_tab_partitions to scott;

connect scott/tiger

whenever sqlerror continue
drop sequence part_maint_seq;

create sequence part_maint_seq
increment by 1
start with 1
nomaxvalue
nominvalue
nocycle;

drop table partition_maintenance;

create table partition_maintenance(
    opid           number not null,
    table_owner    varchar2(8),
    table_name     varchar2(30),
    partition_name varchar2(30),
    operation      varchar2(10),
    success        char(1),
    exe_date       date,
    exec_by        varchar2(30) default 'automatic',
    errormsg       varchar2(512)
);

alter table partition_maintenance
add constraint part_maint_pk primary key (opid);

Then, create the stored procedure that will do the job. It requires two parameters to be passed to it: table owner and table name. Note that the procedure will need to be called with invoker's right, since it is accessing DBA_TAB_PARTITION:

connect scott/tiger

set serveroutput on

CREATE OR REPLACE PROCEDURE DROP_PARTITIONS ( towner IN VARCHAR2, tname IN VARCHAR2) AUTHID CURRENT_USER AS

  PNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE;
  L_DATA LONG;
  V_HVD DATE;
  V_PURGE_DATE DATE;
  SQLTORUN VARCHAR2(200);

  CURSOR PART_NAME
  IS
  SELECT PARTITION_NAME
    FROM DBA_TAB_PARTITIONS
    WHERE TABLE_NAME =  UPPER(tname)
    AND   TABLE_OWNER = UPPER(towner)
    AND   INTERVAL='YES'
    ORDER BY partition_position;

BEGIN

    OPEN PART_NAME;
    FETCH PART_NAME INTO PNAME;
    IF PART_NAME%ROWCOUNT = 0 THEN
        dbms_output.put_line('no such table as ' || UPPER(towner) ||'.'||UPPER(tname) );
            INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
                VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','N', SYSDATE, 'No table such as '|| towner || '.' || tname );
                COMMIT;
    END IF;
    WHILE PART_NAME%FOUND
        LOOP
            --DBMS_OUTPUT.PUT_LINE( pname );
            SELECT HIGH_VALUE
            INTO L_DATA
            FROM DBA_TAB_PARTITIONS
            WHERE TABLE_OWNER= UPPER(towner)
            AND   TABLE_NAME = UPPER(tname)
            AND PARTITION_NAME = PNAME;

            --dbms_output.put_line('partition name: ' || pname || ' ' ||  trim (substr ( l_data, 12, 10 )  ) );
             V_HVD := TO_DATE(TRIM (SUBSTR ( L_DATA, 12, 10 ))  ,'YYYY-MM-DD');
            -- dbms_output.put_line('high value date: ' || v_hvd);
           V_PURGE_DATE := TRUNC(SYSDATE)-35;
            --dbms_output.put_line('purge date: ' || v_purge_date);
            IF V_HVD < V_PURGE_DATE THEN
               DBMS_OUTPUT.PUT_LINE('partition ' || PNAME ||' too old, high value date for partition is: ' || V_HVD);
               -- drop statement goes here
               SQLTORUN := 'alter table ' || towner || '.' || tname || ' drop partition ' || PNAME || ' UPDATE INDEXES';
               DBMS_OUTPUT.PUT_LINE(SQLTORUN);
               EXECUTE IMMEDIATE SQLTORUN;
               -- logging to a separate table goes here
               INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
               VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','Y', SYSDATE, 'Partition ' || pname || ' of table ' || towner || '.' || tname || ' was dropped.');
               COMMIT;
            END IF;
            FETCH PART_NAME INTO PNAME;
        END LOOP;

    CLOSE PART_NAME;

END;
/

Finally, create a program of type "STORED_PROCEDURE", define its arguments, and create a job that executes the program on a regular basis:

whenever sqlerror continue
set serveroutput on

BEGIN
DBMS_SCHEDULER.DROP_JOB(
    job_name        => 'DROP_PARTITION_5WEEK'
  );
  DBMS_SCHEDULER.DROP_PROGRAM(
     program_name       => 'DROP_PARTITION_PROG'
  );
  DBMS_SCHEDULER.create_program (
    program_name        => 'DROP_PARTITION_PROG',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'DROP_PARTITIONS',
    number_of_arguments => 2,
    enabled             => FALSE,
    comments            => 'Program to drop partitions with contents older than 35 days..');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'DROP_PARTITION_PROG',
    argument_name     => 'towner',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => 'SCOTT');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'DROP_PARTITION_PROG',
    argument_name     => 'tname',
    argument_position => 2,
    argument_type     => 'VARCHAR2',
    default_value     => 'SALES');

  DBMS_SCHEDULER.enable( name => 'DROP_PARTITION_PROG');

  DBMS_SCHEDULER.CREATE_JOB
    (
    job_name        => 'DROP_PARTITION_5WEEK',
    program_name    => 'DROP_PARTITION_PROG',
    start_date      => TO_TIMESTAMP_TZ('2015/05/26 21:45:00.000000 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
    repeat_interval => 'FREQ=WEEKLY;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job will execute the program drop_partitions.');
END;
/

Some common errors seen when configuring TSM

ANU2534E Option file error.

The above error can occur when you run the command

tdpoconf showenv

as a non-root user (typically the user owning the oracle database software):

tdpoconf showenv

IBM Tivoli Storage Manager for Databases:
Data Protection for Oracle

Version 6, Release 3, Level 0.0
(C) Copyright IBM Corporation 1997, 2011. All rights reserved.

ANU2534E Option file error.

Solution: Change permissions on two of the configuration files:

chmod 644 /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.sys

ANS1217E (RC409) Server name not found in System Options File.

The following error typically occurs during a redirected restore, which is often needed during duplication of Oracle databases:

ANS1217E (RC409)  Server name not found in System Options File

The above indicates that the attribute "TCPServeraddress" found in the file /usr/tivoli/tsm/client/api/bin64/dsm.sys must be changed so that it identical to the source server.

ANS1087E (RC106) Access to the specified file or directory is denied.

This error could occur when you run the command tdpoconf shownev as a non-root user:

ANS1087E (RC106)  Access to the specified file or directory is denied

The above error is resolved by setting correct permissions on the /var/adm/log folder:

su -
cd /var/adm/
chmod 755 log
cd log
chmod 777 tdpoerror.log dsmerror.log

ANS1035S (RC406) Options file '*' could not be found, or it cannot be read.

This error is also triggered when you access tdpo as a non-root user, for example, by executing

tdpoconf showenv

as a non-root user:

ANS1035S (RC406)  Options file '*' could not be found, or it cannot be read.

To solve the problem, grant the right permissions on the files:

su -
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.opt
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.sys

ANS1025E (RC137) Session rejected Authentication failure.

The above error may occur when you are attempting to set a new password as a part of reconfiguring the server for a redirected restore:

ANS1025E (RC137) Session rejected: Authentication failure

When you run

tdpoconf passwd

you are prompted for password three times:

current password
new password
confirm new password

At this point during the configuration, the tsm server pointed to in the attribute "TCPServeraddress" in the configuration file /usr/Tivoli/tsm/client/api/bin64/dsm.sys, believes that it is communicating to the original backupclient. Therefore, it is important that you supply the original backupclient's password at all three prompts.

ANS0282E (RC168) Password file is not available.

When the above error message occurs, you simply need to execute

tdpoconf passwd

to reset the password. Supply the original backup client's password at all three prompts.

Valid formats for JDBC URLs

Format for JDBC URL with SID:

•jdbc:oracle:thin:@host:1521:sid
•jdbc:oracle:thin:user/password@host:1521:sid

Format for JDBC URL with Service Name:

•jdbc:oracle:thin:@//host:1521/servicename
•jdbc:oracle:thin:user/password@//host:1521/servicename

The difference is in the use of slashes (/) and colons (:)

Source: Oracle Documentation, note 832455.1

How to deinstall Oracle 10g database software

How to deinstall Oracle 10g database software

In oracle 10g, you need to use the oui (oracle universal installer) script supplied in the $ORACLE_HOME/oui/bin folder, as shown below.


# Log in as the oracle 10g user, and set the environment to the ORACLE_HOME you are about remove.
# Check the current ORACLE_HOME
echo $ORACLE_HOME
/u01/oracle/product/10.2.0.4/db

# run oui in silent mode, and point to the correct Oracle 10g home:

cd /u01/oracle/product/10.2.0.4/db/oui/bin
./runInstaller -deinstall -silent REMOVE_HOMES={"/u01/oracle/product/10.2.0.4/db"}

Expected output:

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-05-20_08-51-59AM. Please wait ...
Oracle Universal Installer, Version 10.2.0.4.0 Production

Copyright (C) 1999, 2008, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Wednesday, May 20, 2015 8:52:24 AM CEST)
Configuration assistant "iSQL*Plus Configuration Assistant" succeeded
Configuration assistant "Oracle Database Configuration Assistant" succeeded
Configuration assistant "Oracle Net Configuration Assistant - Deinstall Script" failed
............................................................................................................... 100% Done.

Deinstall successful

End of install phases.(Wednesday, May 20, 2015 8:56:17 AM CEST)
End of deinstallations

Please check '/u01/oracle/oraInventory/logs/silentInstall2015-05-20_08-51-59AM.log' for more details.

To verify the deinstallation, check the oracle inventory, in my case /u01/oracle/oraInventory/ContentsXML/inventory.xml

There should be a row similar to:

Remove the old ORACLE_HOME folder physically from disk:

cd /u01/oracle/product
rm -rf 10.2.0.4

How to deinstall Oracle 11g database software

In oracle 11g, you can use the deinstall script supplied in the $ORACLE_HOME/deinstall folder, as shown below.

In my example, I have two Oracle software installations on the host

/u01/oracle/product/11203 <-- not in use, can be removed

/u01/oracle/product/11204 <-- in use

cd /tmp

# first perform a trial run

/u01/oracle/product/11203/deinstall/deinstall -checkonly

# do the actual deinstall

/u01/oracle/product/11203/deinstall/deinstall

I have found that when you have a listener running on the host out of a different ORACLE_HOME, the deinstall script will often (but not always) want to deconfigure it, despite the fact that it runs out of a different ORACLE_HOME. So for a brief period, the host may be without a valid listener process. However, this was never a problem , simply change directory to the working /u01/oracle/product/11204/network/admin folder and restart the listener.

Sometimes (but not always) you may have to manually remove the deinstall folder under the ORACLE_HOME home you just deinstalled.

How to add a logfile group and a logfile member - syntax

alter database add logfile group 2
(
'/u01/app/oracle/flash_recovery_area/mydb/onlinelog/redo02a.log',
'/u01/app/oracle/oradata/mydb/onlinelog/redo02b.log'
) size 50M;

Remember, if you have just recently dropped the redo log members, they are still present physically on disk.
If you'd like to reuse the log file member names, and to avoid

ORA-00301: error in adding log file /u03/oradata/arutvt/redo03.log - file cannot be created,

add the REUSE keyword at the end of the statement:

alter database add logfile group 3 ('/u03/oradata/mydb/redo03.log') size 1024M REUSE;

To add another member to an already existing group:

ALTER DATABASE ADD LOGFILE MEMBER '/u03/oradata/mydb/redo03.log' TO GROUP 3;

If the redo log file member is already present on disk, use reuse:

ALTER DATABASE ADD LOGFILE MEMBER '/u03/oradata/mydb/redo3b.log' REUSE TO GROUP 3;

If you do not specifically say which log group number you want to create, Oracle will take add another group based on the largest log file group number in v$logfile. So if I have 3 groups already, and execute

alter database add logfile '/rdodata/mydb/redo04.log' size 2048M;

It will create group number 4 for you, even if you do not explicitly say so:

SYS@cdb>SQL>@redo

    GROUP# MEMBER                                                     MB ARC STATUS            SEQUENCE#
---------- -------------------------------------------------- ---------- --- ---------------- ----------
         1 /rdodata/mydbredo01.log                              2048 YES INACTIVE                220
         2 /rdodata/mydb/redo02.log                             2048 YES INACTIVE                221
         3 /rdodata/mydb/redo03.log                             2048 NO  CURRENT                 222
         4 /rdodata/mydb/redo04.log                             2048 YES UNUSED                    0

Documentation for Oracle 19c is found here