RMAN and EMC Legato Networker 7.4

In this post I would show you how to integrate RMAN with a media manager, in my case EMC Legato Networker 7.4



Launch EMC Legato Networker 7.4 from your browser or double click on the link to the java web interface.


Enter your administrative credentials into the Networker Management Console Login window.


The Networker Management Console will show the host used as Networker Server. Double clicking on the NAME Networker...


... it will show you the Networker Administration window


Expand the Groups and select the group created for the Data Warehouse, in my case it's called Gr_ORACLE_DWH


If you double click on the client associated with Gr_ORACLE_DWH on the right panel, the properties panel will be showed. On the General panel, Identity-->Name is the name of my vip server, IndexManagement shows the Browse and Retention policy for this backup, Backup-->Save Set shows you the path of the RMAN command that Networker will execute on the client, Backup-->Group identifies Gr_ORACLE_DWH as group, Backup-->Schedule show that this backup is scheduled as ORACLE_DWH_FULL.


On the Apps & Modules panel, Backup-->Backup command will show the command executed by Networker Server on the client, including some environment variables or pre and post backup command. This command nsrnmo_dwhdb2 is placed on the client under /usr/sbin/


If you instead right click on Gr_ORACLE_DWH group and select Properties the following screen is showed. In my case this backup starts at 19:00 but at present is disabled.


Selecting Schedules from the Networker Administration window on the left panel you can double click on ORACLE_DWH_FULL to view when and what type of backup is scheduled.


Select Media button on the top of Networker Administration window and then Media Pools.
The Media Pools for this backup is called ORAdatawarehouse. Backup data is sorted into backup media volumes by using media pools. A media pool is a specific collection of volumes to which the NetWorker server writes data. The server uses media pools to sort and store data. Media pools act as filters that tell the server which backup volumes should receive specific data.
When a scheduled backup occurs, the NetWorker server tries to match the save
stream to a media pool configuration. If the save stream matches the criteria of a
media pool configuration, it is directed to a labeled volume in the media pool. The
server then checks if a correctly labeled volume for that media pool is mounted on a
storage device:
◆ If a correctly labeled volume is mounted on a storage device, the NetWorker
server writes data to the volume.
◆ If there is no correctly labeled volume mounted on a storage device, the
NetWorker server requests that such a volume be mounted and waits until an
operator or an autochanger mounts an appropriate volume.


If you double click on ORAdatawarehouse a Basic Properties window is showed with some informations.


From the panel Selection Criteria, Target Device-->Devices shows that this kind of Media Pools use a TAPE Library (in my case they are /dev/nst0 and /dev/nst1)


General informations about the tapes available on the TAPE Library (ADIC Scalar i500) are showed selecting Devices button on the top of Networker Administration window and then Libraries-->ADIC.


Let's see the file what there is inside the file /usr/sbin/nsrnmo_dwhdb2 on the client.


You can find the required variables ORACLE_HOME and PATH, the optional variables ORACLE_SID...


...NLS_LANG, NSR_RMAN_ARGUMENTS, NSR_RMAN_OUTPUT, NSR_SB_DEBUG_FILE...


...PRECMD, POSTCMD and TNS_ADMIN.
Set PRECMD to the complete pathname of a file containing a preprocessing script to be executed before the RMAN backup script. If the pre-command script fails (returns a nonzero value), the scheduled Oracle backup does not proceed (that is, the RMAN script is not executed).
Set POSTCMD to the complete pathname of a file that contains a postprocessing script to be executed after the RMAN backup script. If the RMAN backup script fails, the failure is reported, and the postprocessing script is executed nevertheless. If the postprocessing script fails, an error message is reported.
These script files must have permissions that allow execution by the root user, as the scheduled Oracle backup is always launched by root. The scripts should return a zero value when they succeed and a nonzero value when they fail. The return of a nonzero value will cause the scheduled backup to fail.



My PRECMD file is located on /u01/app/oracle/srvctl_scripts/dwhdb/stop_database_DWHDB.sh and you can view what commands are executed. I need to shutdown my RAC database and then start it up in mount mode to execute a cold backup.



My POSTCMD file is located on /u01/app/oracle/srvctl_scripts/dwhdb/start_database_DWHDB.sh and you can view what commands are executed. I need to shutdown my RAC database (RMAN open it after is execution) and then start it up in open mode.



The RMAN commands for my Data warehouse cold backup is showed here.



Selecting Monitoring button on the top of Networker Administration window and then right click on Gr_ORACLE_DHW, you can start manually the group.


A popup window will ask you to proceed.


Double clicking on the group just started, you can view the Networker Server is trying to contact the client...



... and how the backup proceed.



You can select the tab Monitoring-->Devices to view how much Bytes are written on tape.


The backup is successfully completed.

STEP TO PARTITION A NON PARTITIONED TABLE

I have a table that is not partitioned and I need to make into a
partitioned table.
To solve this issue it's possible to partition a non-partitioned table using three different ways:
A) export/import method

B) Insert with a subquery method

C) Partition exchange method

I've used the second method that is:
1) Create the partitioned table:

create table hr.fact_navigation_new
( date_events date not null
)
partition by range (date_events)
(
partition part_2008_01_01 values less than (to_date('2008-01-02', 'yyyy-mm-dd)) tablespace part_2008_01_usr_dwh ,
partition part_future values less than (MAXVALUE) tablespace part_future
) enable row movement;

With user SYS I created first two tablespaces:
create tablespace part_2008_01_usr_dwh
datafile '+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_01.ora' size 1024m reuse autoextend on next 51200k maxsize 32767m, '+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_02.ora' size 1024m reuse autoextend on next 51200k maxsize 32767m,
'+dwh_db/dwhdb/datafile/dtf_part_2008_01_usr_dwh_03.ora' size 512m reuse autoextend on next 51200k
maxsize 32767m extent management local segment space management auto;

create tablespace part_future
datafile '+dwh_db/dwhdb/datafile/dtf_part_future_usr_dwh_01.ora' size 512m reuse
autoextend on next 51200k maxsize 32767m, '+dwh_db/dwhdb/datafile/dtf_part_future_usr_dwh_02.ora' size 512m reuse autoextend on next 51200k
maxsize 32767m extent management local segment space management

2) Insert into the partitioned table with a subquery from the
non-partitioned table:

insert into hr.fact_navigation_new
select * from hr.fact_navigation;

3) To have the same name as the original table,
then rename the original table and the new table:

alter table hr.fact_navigation rename to hr.fact_navigation_ori;
alter table hr.fact_navigation_new rename to hr.fact_navigation

Now I have a new partitioned by range table with two partitions. What about if I need to add new daily partitions between part_2008_01_01 and part_future ?

I've solved with a PL/SQL procedure and a job executed by the DBMS_SCHEDULER utility.
As SYS user execute the following procedure:

create or replace PROCEDURE CHECK_PARTITIONS AS
l_curr_max_part varchar2(16); /* current max partition */
l_today_part varchar2(16); /* today+interval partition */
l_last_day_part varchar2(16); /* last partition from today + two months */
l_diff_part number;
l_exist_tab number;
l_tab_name varchar2(30); /* tablespace name */
l_date date;
l_stmt varchar2(2048);
l_part_name varchar2(16); /* partition name to be created */
l_range_month number; /* interval between created partitions and to_be_created partitions : 2 months is my default */
BEGIN

l_diff_part := 0;
l_exist_tab := 0;
l_range_month := 2;

/**
* Current max partition
*/
select substr(max(partition_name),6)
into l_curr_max_part
from dba_tab_partitions
where table_name = 'FACT_NAVIGATION'
and TABLE_OWNER = 'HR'
and partition_name <> 'PART_FUTURE';

/**
* Today+interval partition
*/
select to_char(add_months(sysdate, l_range_month), 'YYYY_MM_DD')
into l_today_part
from dual;

if (l_curr_max_part < l_today_part)
then
/**
* If max partition is the least compared with Today+interval partition,
* I need to detect the latest day of the partition to create
*/
select to_char(last_day(add_months(sysdate, l_range_month)), 'YYYY_MM_DD') LD
into l_last_day_part
from dual;

/**
* I need to detect the number of partitions to create starting
* from the current available max partition
* untill the latest
*/
select to_date(l_last_day_part, 'YYYY_MM_DD')-to_date(l_curr_max_part, 'YYYY_MM_DD') RET
into l_diff_part
from dual ;

for ii IN 1..l_diff_part
loop
/**
* Tablespace name to create
*/
select 'PART_' || to_char(to_date(l_curr_max_part, 'YYYY_MM_DD')+ii, 'YYYY_MM') || '_USR_DWH'
into l_tab_name
from dual;
/**
* I need to verify if tablespace name alreadyu exists. Otherwise I create it.
*/
select count(*)
into l_exist_tab
from dba_tablespaces
where tablespace_name = l_tab_name;

if (l_exist_tab = 0)
then
/**
* I need to create the tablespace
*/
execute immediate('CREATE TABLESPACE ' || l_tab_name || ' DATAFILE ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_01.ora''' || ' SIZE 1024M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M, ' || ' ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_02.ora''' || ' SIZE 1024M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M, ' || ' ''+DWH_DB/dwhdb/datafile/dtf_' || l_tab_name || '_03.ora''' || ' SIZE 512M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M ' || ' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO');

execute immediate ('ALTER USER USR_SUPER_DWH QUOTA UNLIMITED ON ' || l_tab_name);
end if;

select to_date(l_curr_max_part, 'YYYY_MM_DD')+ii+1
into l_date
from dual;

execute immediate ('ALTER TABLE USR_SUPER_DWH.fact_navigation SPLIT PARTITION PART_FUTURE AT(to_date(''' || to_char(l_date, 'YYYY-MM-DD') || ''', ''YYYY-MM-DD'')) ' || ' INTO (PARTITION PART_' || l_part_name || ' tablespace '|| l_tab_name ||', PARTITION PART_FUTURE)');
end loop;
end if;
END CHECK_PARTITIONS;

The following is the script to create the job using the DBMS_SCHEDULER:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SPLIT_PARTITION',
job_type => 'STORED_PROCEDURE',
job_action => 'CHECK_PARTITIONS',
start_date =>to_date('13-02-2008 15:05:00', 'DD-MM-YYYY hh24:mi:ss'),
repeat_interval =>'FREQ=WEEKLY;BYDAY=WED',
enabled => TRUE,
auto_drop => FALSE
);
END;

My job starts every wednesday at 15:05 every week.
If you want to see the status of this job, simply execute:
SELECT * FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'SPLIT_PARTITION';

If you want to start it immediately, run the following statement:
BEGIN
DBMS_SCHEDULER.RUN_JOB (
job_name => 'SPLIT_PARTITION',
use_current_session => FALSE);
END;

In the following pictures you can see how I manually run the job and see the status RUNNING
in DBA_SCHEDULER_JOBS table, after scheduled the job at 15:05.
When the job finished, new partitions are created (from PART_2008_03_31 to PART_2008_04_30) and
the status of the job become again SCHEDULED.