Useful RMAN Commands

Few important RMAN Commands. Stay tuned, I am compiling a RMAN article for those who are NOT DBA, yet want to have proper backups for their databases.

Oracle DBA Zone

Show all of the RMAN parameters for the database

RMAN> show all;

Configure the disk channel to the format /backup/ora_df%t_s%s_s%p’

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;

Configure the disk channel to the format +FLASHDG

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘+FLASHDG’;

Backup the database using the standard RMAN parameters

RMAN> backup database;

List all of the current backups for the database

RMAN> list backup;

 

List Backup summary

RMAN> list summary;

 

Crosscheck backups to see if they still exist

RMAN> crosscheck backup;

 

Crosscheck archive logs to see if they still exist

RMAN> crosscheck archivelog all;

 

Remove any expired backup

RMAN> delete expired backup;

Remove any expired archivelos

RMAN> delete expired archivelog;

Backup the database and archivelogs using the standard RMAN parameters and delete the archivelogs after the backup

RMAN> backup archivelog all delete input;

And

RMAN> run

2> {

3> backup database;

4> backup …

View original post 270 more words

Advertisements

Oracle| Altering table structures through PL/SQL block

Hi guys

Prior moving to Oracle EBS (R12) we were using a mini ERP system, that was as robust as possible, however lacked many features those were required for modern business environments. While the company that distributed the software in the market over a decade finally dissolved, we were also provided a full copy of the source codes & the same was in my custody for whole these years.

Recently I wanted to revive it, after WebLogic 12c & seeing the possibility of “JAVA WEBSTART” in small & middle scale industries.

One of the major hurdles I came across were with the columns those saved document numbers. This software which was designed during late 90s only had maximum 6 digits document numbers across modules, totaling 496 tables!

It was not a very feasible idea to go through each table & to modify them using GUI tools like Oracle SQL developer. Instead I planned for a PL/SQL procedure.

& here comes the PL/SQL block


SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

V_EXECUTE_MODIFY BOOLEAN := FALSE; --'TRUE' FOR TABLE MODIFY

BEGIN
FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
--T_YR_MTH
IF (V_EXECUTE_MODIFY) THEN
IF (C.COLUMN_NAME 'T_YR_MTH') THEN
/*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
END IF;

END IF;
END IF;

END LOOP;

END LOOP;
END;

So what does this block do?


CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

This block fetches all table names from the DBA_TABLES for the owner that is passed in as a parameter, in this specific case ‘OPMAN’


CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

The above cursor takes in two parameters, OWNER name and the table name, that comes from the 1st cursor


FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
    FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
    IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

        DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
        --T_YR_MTH
        IF (V_EXECUTE_MODIFY) THEN
        IF (C.COLUMN_NAME 'T_YR_MTH') THEN
        /*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
        END IF;

        END IF;
        END IF;

        END LOOP;

END LOOP;

Let us analyze this part of the block now.

The first cursor passes the table name to 2nd cursor and the loop goes through all columns matching data type, precision and scale. I’ve checked for “Number”, precision 6 & scale 0 which are common for all columns those were defined for document numbers. However there were few instances where YearMonth (YYYYMM) values captured for month end processes, which shouldn’t be checked. As our mini ERP is a standard business application, it was designed following same nomenclature across tables while columns defined. Thus it become easier for me to isolate the columns whose were matching same data type, precision and scale & to limit the alter commands modifying only those columns which were defined for document numbers.

The above example could be further developed to easily alter tables while the entire application is modernized and to easily modify columns with bigger sizes.

Interesting? Why don’t you give it a try and post your comments?

regards,

rajesh