dbms_backup_restore Package

The dbms_backup_restore package is used as a PL/SQL command-line interface for replacing native RMAN commands, and it has very little documentation.

The Oracle docs note how to install and configure the dbms_backup_restore package:

“The DBMS_BACKUP_RESTORE package is an internal package created by the dbmsbkrs.sql and prvtbkrs.plb scripts. This package, along with the target database version of DBMS_RCVMAN, is automatically installed in every Oracle database when the catproc.sql script is run. This package interfaces with the Oracle database server and the operating system to provide the I/O services for backup and restore operations as directed by RMAN.”

The docs also note that “The DBMS_BACKUP_RESTORE package has a PL/SQL procedure to normalize filenames on Windows NT platforms.”

Oracle DBA John Parker gives this example of dbms_backup_restore to recover a controlfile:

declare
devtype varchar2(256);
done boolean;
begin
devtype:=dbms_backup_restore.deviceallocate( type=>'sbt_tape',
params=>'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=rdcs,OB2BARLIST=ORA_RDCS_WEEKLY)',
ident=>'t1');
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto('D:\oracle\ora81\dbs\CTL1rdcs.ORA');
dbms_backup_restore.restorebackuppiece(
'ORA_RDCS_WEEKLY.dbf', DONE=>done );
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto('D:\DBS\RDCS\CTL2RDCS.ORA');
dbms_backup_restore.restorebackuppiece(
'ORA_RDCS_WEEKLY.dbf', DONE=>done );
dbms_backup_restore.devicedeallocate('t1');
end;

Here are some other examples of using dbms_backup_restore:

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => 'D:\ORACLE_BASE\datafiles\SYSTEM01.DBF');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => 'D:\ORACLE_BASE\datafiles\UNDOTBS.DBF');
--dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => 'D:\ORACLE_BASE\datafiles\MYSPACE.DBF');
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_DF_BCK05H2LLQP_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/

--restore archived redolog
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/

Creating Oracle Database Link

Oracle has invested heavily in distributed database technology and the creation of a database link is very straightforward. You specify the database link name, the remote user to connect to, the password for the remote user and the TNS service name for the database link connection:

create public database link
  mylink
connect to
  remote_username
identified by
  mypassword
using 'tns_name';

You can also create a database link to non-Oracle databases, here are steps for a database link to MySQL:

1. Your first step is having installed the TRANSPARENT GATEWAY (it comes in as of the options when you install Oracle).
2. You must have a user in the SQL Server.
3. In the directory \tg4msql\admin look for the file inittg4msql.ora and have the following options:
   

   HS_FDS_CONNECT_INFO="SERVER=name_server;DATABASE=name_db"
   HS_FDS_TRACE_LEVEL=OFF
   HS_FDS_RECOVERY_ACCOUNT=user_sqlserver
   HS_FDS_RECOVERY_PWD=pass_user_sqlserver

4. Configure the listener and add the following
   

   (SID_DESC =
   (PROGRAM = tg4msql)
   (SID_NAME = MSQL)
   (ORACLE_HOME = C:\oracle1)
   )
   )

   
   and on tnsnames.ora add the following
   

   MSQL=
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = servidor)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SID = MSQL)
   )
   (HS=OK)
   )

   
   where HS set up as a heterogonous service
5. Create a link to sql server.
   

   create database link msql connect to user_sqlserver identified by password using 'MSQL';

6. You can now use the database link to a foreign database:
   

   select * from table@msql

   
   

Connect Oracle to SQL Server

In order to connect to SQL*Server from Oracle, you must do several things:

1. Install ODBC drivers for the SQL*Server database. The drivers are installed on your laptop or on the server that contains the Oracle code tree.
2. Setup the ODBC connection on your laptop using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.
4. Ensure the Global_names parameter is set to False in the Oracle database.
5. Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle
6. Oracle database installation.
7. Modify the Listener.ora file on the database installation to connect to the Oracle instance and ODBC drivers.
8. Modify the Tnsnames.ora file to point to the proper code tree.
9. Reload the listener
10. Create a database link on the Oracle installation.
11. Run a Select statement for the Oracle installation using the database link.

Finding Tables References Inside PL/SQL Store Procedure

The Oracle data dictionary tracks the object types referenced in PL/SQL with the dba_dependencies view. To track the dependency among packages and tables, try this dictionary query:

select
  referenced_owner,
  referenced_name,
  referenced_type
from
  dba_dependencies
where
  name= 'MY_STORE_PROC'
and
  owner = 'SCOTT'
order by
  referenced_owner, referenced_name, referenced_type;

you can also select the referenced_type, ie. tables

Oracle listener lsnrctl command

Here are lsnrctl command :

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-PEB-2008 18:56:26

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               change_password
quit                exit                set*
show*

LSNRCTL>

The following commands are used to manage the listener:

• start – Starts the listener with the name specified, otherwise LISTENER will be used. For Windows systems, the listener can also be started from the Control Panel.
• stop – Stops the listener. For Windows systems, the listener can also be stopped from the Control Panel.
• status – Provides status information about the listener, including start date, uptime, and trace level.
• services – Displays each service available, along with the connection history.
• version – Displays the version information of the listener.
• reload – Forces a read of the configuration file in order for new settings to take effect without stopping and starting the listener.
• save_config – Creates a backup of the existing listener.ora file and saves changes to the current version.
• trace – Sets the trace level to one of the following – OFF, USER, ADMIN, or SUPPORT.
• spawn – Spawns a program that runs with an alias in the listener.ora file.
• dbsnmp_start – Starts the DBSNMP subagent.
• dbsnmp_stop – Stops the DBSNMP subagent.
• dbsnmp_status – Displays the status of the DBSNMP subagent.
• change_password – Sets a new password for the listener.
• quit and exit – Exits the utility.
• set – Changes the value of any parameter. Everything that can be shown can be set.
• show – Displays current parameter settings.

Preventing Oracle DDL Unauthorized Schema Changes

One of the biggest problems in Oracle are "unexpected" schema changes. In production environments changes are often allowed only during special maintenance times, and changes such as re-analyzing schema statistics and DDL to change table/index structures must be prohibited at other times.

This can be tracked with an Oracle DDL trigger, but the DBA can disable DDL at the object level with a simple script to "disable table lock" syntax:

alter table mbsdba.t_dept disable table lock;

This script could be used to lock all tables within a schema:

connect sys/xxx as sysdba;
spool runme.sql
select "alter table "||owner||"."||table_name||" disable table lock;"
from
dba_tables
where
owner = "mbsdba";

spool off;
@runme.sql

While locked, any attempted schema changes will be rejected

SQL>ALTER TABLE mbsdba.t_dept ADD (NEWCOL NUMBER);

ALTER TABLE mbsdba.t_dept
*

ERROR at line 1:
ORA-00069: cannot acquire lock-- table locks disabled for mbsdba.t_dept