Pages

Wednesday, July 20, 2011

DBMS_FILE_TRANSFER PL/SQL package.

Sample

CREATE or replace DIRECTORY source_dir AS '+DATA/DPG/ARCHIVELOG/2011_07_16';
CREATE or replace DIRECTORY dest_dir AS '/home/oracle/ARC';

exec dbms_file_transfer.COPY_FILE('source_dir','thread_1_seq_22287.328.756667191','dest_dir','thread_1_seq_22287.328.756667191');

COPY_FILE
The COPY_FILE procedure allows you to copy binary files from one location to another on the same server.

-- Create the source and destination directory objects.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';

-- Switch a tablespace into read only mode so we can
-- use it for a test file transfer.
ALTER TABLESPACE users READ ONLY;

-- Copy the file.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Checking the destination directory will reveal that the file has been copied successfully.

GET_FILE
The GET_FILE procedure allows you to copy binary files from a remote server to the local server.

-- Login to the remote server.
CONN system/password@remote

-- Create the source directory object and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
ALTER TABLESPACE users READ ONLY;

-- Login to the local server.
CONN system/password@local

-- Create the destination directory object and a database link.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';

-- Get the file.
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
source_database => 'REMOTE',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF');
END;
/

-- Login to the remote server.
CONN system/password@remote

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Checking the destination directory on the local server will reveal that the file has been copied successfully.

PUT_FILE
The PUT_FILE procedure allows you to copy binary files from the local server to a remote server.

-- Login to the remote server.
CONN system/password@remote

-- Create the destination directory object.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';

-- Login to the local server.
CONN system/password@local

-- Create the source directory object, database link and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';
ALTER TABLESPACE users READ ONLY;

-- Put the file.
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF',
destination_database => 'REMOTE');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;
Posted by at No comments:

Moving a datafile from File System to ASM


Moving datafiles from file system to ASM can be done in different ways, one of them using the RMAN copy command.

These are the steps:

1. Check where to build a new file system based tablespace:

[oracle@rac1 ~]$ cd /u01/oradata/racdb
[oracle@rac1 ~]$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/vx/dsk/u01
31457280 17540576 13819976 56% /u01

2. Connect to sqlplus and create a new tablespace

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 19 06:07:50 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create tablespace trtst datafile '/u01/oradata/racdb/trtst01.dbf' size 150M;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name ='TRTST';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/racdb/trtst01.dbf

3. take the tablespace offline, you may take offline a single datafile from a multifile tablespace if required.

SQL> ALTER TABLESPACE TRTST OFFLINE;

Tablespace altered.

4. Check where are your datafiles located on ASM

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATADG/racdb/datafile/users.259.623629857
+DATADG/racdb/datafile/sysaux.257.623629849
+DATADG/racdb/datafile/undotbs1.258.623629855
+DATADG/racdb/datafile/system.256.623629845
+DATADG/racdb/datafile/undotbs2.261.623630209
/u01/oradata/racdb/trtst01.dbf

6 rows selected.

5. Log out from sqlplus, start an RMAN session and execute the copy command

Note that when giving the destination inside ASM you just need to pass the disk group name when using omf (Oracle Managed Files) that is the best practice in ASM.

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac1 racdb]$ rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jun 19 06:12:14 2007

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

connected to target database: RACDB (DBID=621246832)
using target database control file instead of recovery catalog

RMAN> copy datafile '/u01/oradata/racdb/trtst01.dbf' to '+DATADG';

Starting backup at 19-JUN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/racdb/trtst01.dbf
output filename=+DATADG/racdb/datafile/trtst.263.625644857 tag=TAG20070619T061416 recid=1 stamp=625644858
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 19-JUN-07

RMAN> exit


Recovery Manager complete.

6. Start an sqlplus session, rename the old file to the new ASM file

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 19 06:15:11 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter database rename file '/u01/oradata/racdb/trtst01.dbf' to '+DATADG/racdb/datafile/trtst.263.625644857';

Database altered.

SQL> alter tablespace trtst online;

Tablespace altered.

7. remove the old file

rm /u01/oradata/racdb/trtst01.dbf

8. Create a test object

SQL> create table testtb tablespace trtst as select * from dba_source;

Table created.
Posted by at 1 comment:

Tuesday, July 19, 2011

How to configure Proxy Settings for the Linux Console and APT

You can use the following methods to configure your console to use a proxy server so that console based programs like wget could get connect to the internet through the proxy.

1 - Set the environment variable
# export http_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/
# export ftp_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/

export http_proxy=http://upul:pass@10.32.11.6:80/

In the above configuration you can ommit the DOMAIN\USERNAME:PASSWORD@ part if you are not using proxy authentication.

Note: If you get the following error when you try to use wget, you might have to pass the proxy authentication credentials to wget as arguments.

Connecting to SERVER:PORT... connected.
Proxy request sent, awaiting response... 407 Proxy Authentication Required
11:14:45 ERROR 407: Proxy Authentication Required.
1.1 - Passing proxy authentication credentials to wget as arguments
$ wget --proxy-user "DOMAIN\USERNAME" --proxy-passwd "PASSWORD" URL
2 - Configure the proxy settings in the .bashrc
If you want set the proxy for all the users you can do it in the system wide .bashrc file.
nano /etc/bash.bashrc

#proxy settings
export http_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/
export ftp_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/

Note: The system wide .bashrc file may not be available in all Linux systems and only can be used if you are using the bash shell

2.1 - Having an alias for wget with proxy
If you don't want to pass the proxy arguments to wget all the time, you create an alias for wget in the .bashrc file
alias wget 'wget --proxy-user "DOMAIN\USERNAME" --proxy-passwd "PASSWORD"'
Posted by at No comments:

Public Yum Server


The Oracle public yum server offers a free and convenient way to install packages from the Oracle Linux and Oracle VM installation media via a yum client.

You can download the full Oracle Linux and Oracle VM installation media via edelivery.oracle.com/linux. Note that errata are not available via this public yum server. To access errata (bug fixes, security fixes and enhancement), you must have a valid support contract to access Unbreakable Linux Network (ULN). For more information, see the FAQ below.

This yum server is offered without support of any kind. If you require support, please consider purchasing Oracle Linux support via the online store, or via your sales representative.

Getting Started

  1. Download and Install Oracle Linux

  2. Download and copy the appropriate yum configuration file in place, by running the following commands as root:

    Oracle Linux 4, Update 6 or Newer

    # cd /etc/yum.repos.d
    # mv Oracle-Base.repo Oracle-Base.repo.disabled
    # wget http://public-yum.oracle.com/public-yum-el4.repo

    Oracle Linux 5

    # cd /etc/yum.repos.d
    # wget http://public-yum.oracle.com/public-yum-el5.repo

    Oracle Linux 6

    # cd /etc/yum.repos.d
    # wget http://public-yum.oracle.com/public-yum-ol6.repo

    Oracle VM 2

    # cd /etc/yum.repos.d
    # wget http://public-yum.oracle.com/public-yum-ovm2.repo

  3. Enable the appropriate repository by editing the yum configuration file

    • Open the yum configuration file in a text editor
    • Locate the section in the file for the repository you plan to update from, e.g. [el4_u6_base]
    • Change enabled=0 to enabled=1

  4. Begin using yum, for example:

    yum list

    yum install firefox

You may be prompted to confirm the import of the Oracle OSS Group GPG key.

Posted by at No comments:
Subscribe to: Posts (Atom)