Purpose:
Offloading Oracle Data to HDFS
Prerequisites:
Hortonworks NFS Gateway is running Please, Please visit the post below
Configuring NFS Gateway for HDFS [HDP]
Steps to follow
1- Create tablespace on local OS file system
SQL> CREATE TABLESPACE tshdfs DATAFILE '/data/mydata/tshdfs_df1.dbf' SIZE 50M;
Tablespace created.
2- Create table in newly created tablespace
SQL> create table T1 tablespace tshdfs as select * from user_objects;
Table created.
SQL> desc t1;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER
Steps to follow
1- Create tablespace on local OS file system
SQL> CREATE TABLESPACE tshdfs DATAFILE '/data/mydata/tshdfs_df1.dbf' SIZE 50M;
Tablespace created.
2- Create table in newly created tablespace
SQL> create table T1 tablespace tshdfs as select * from user_objects;
Table created.
SQL> desc t1;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER
3- After loading desired data in to your tablespace, make it read only.
SQL> alter tablespace tshdfs read only;
Tablespace altered
Get the data file information for tablespace
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES;
4- Make datafile offline now so that we can copy it to HDFS location
SQL> ALTER DATABASE DATAFILE '/data/mydata/tshdfs_df1.dbf' OFFLINE;
Database altered.
5- Now copy the offline file datafile to the NFS (HDFS) location
SQL> host cp /data/mydata/tshdfs_df1.dbf /data/hdfsloc/data/oraclenfs
Verify the copied file
SQL> host ls -l /data/hdfsloc/data/oraclenfs
total 51221
-rwxr-xr-x 1 oracle oinstall 617 Dec 25 10:43 emp.csv
-rw-r----- 1 oracle oinstall 12288 Dec 25 12:32 employee_ext.dmp
-rw-r--r-- 1 oracle oinstall 52436992 Dec 25 14:46 tshdfs_df1.dbf
6- Now alter tablespace to update new location of file
SQL> alter tablespace tshdfs rename datafile '/data/mydata/tshdfs_df1.dbf' to '/data/hdfsloc/data/oraclenfs/tshdfs_df1.dbf';
Tablespace altered.
7- Make datafile online now which is on new location
SQL> ALTER DATABASE DATAFILE '/data/hdfsloc/data/oraclenfs/tshdfs_df1.dbf' ONLINE;
Database altered.
8- Query the table on read only tablespace
SQL> select count(*) from t1;
COUNT(*)
----------
51762
No comments:
Post a Comment