SFTP commands

List of SFTP commands (SFTP will abort if any of the following commands fail):

get [flags] remote-path[local-path]	Retrieve the remote-path and store it on the local machine. If the local path name is not specified, it is given the same name it has on the remote machine.
put [flags] local-path [local-path]	Upload local-path and store it on the remote machine. If the remote path name is not specified, it is given the same name it has on the local machine.
rename oldpath newpath	Rename remote file from oldpath to newpath.
ln oldpath newpath	Create a symbolic link from oldpath to newpath.
rm path	Delete remote file specified by path.
lmkdir path	Create local directory specified by path.
bye	Quit sftp.
exit	Quit sftp.
quit	Quit sftp.
cd path	Change remote directory to path.
lcd path	Change local directory to path.
ls [path]	Display remote directory listing of either path or current directory if path is not specified.
pwd	Display remote working directory.
rmdir path	Remove remote directory specified by path.
chgrp grp path	Change group of file path to grp. grp must be a numeric GID.
chmod mode path	Change permissions of file path to mode.
chown own path	Change owner of file path to own. own must be a numeric UID.
symlink oldpath newpath	Create a symbolic link from oldpath to newpath.
mkdir path	Create remote directory specified by path.
lls [ls-options [path]]	Display local directory listing of either path or current directory if path is not specified.
lpwd	Print local working directory.
lumask umask	Set local umask to umask.
! command	Execute command in local shell.
!	Escape to local shell.
?	Synonym for help.
help	Display help text

Example:   here I’m transferring ‘.dmp’ files from prod to test server using ‘SFTP’.

From test server:

/var/backup/ $  sftp  chandra@prodhostname

chandra@prodhostname's password: ********

Connected to prodhostname.

sftp> cd /var/backup/corpdb/datapump/                   (moving to datapump location on prod)

sftp> bye                                                                            (quits from sftp prompt)

(creating directories  corpdb and datapump on local server(test))

/var/backup/ $  mkdir corpdb                             

/var/backup/ $ cd corpdb

/var/backup/corpdb/  $  mkdir datapump

/var/backup/corpdb/ $   cd datapump

/var/backup/corpdb/datapump/ $  sftp chandra@prodhostname

chandra@host2's password: ********               (connecting to prod server using prod username)

Connected to host2.

sftp> cd /var/backup                          (moving to datapump location on prod(remote server))

sftp> ls                                              (lists all files & folders in /var/backup location on remote server(prod))

sftp> cd  /var/backup/corpdb

sftp> cd  /var/backup/corpdb/datapump  

sftp> get *.dmp                                    (getting .dmp files from prod to test server)

Fetching /var/backup/corpdb/datapump/CORP01.dmp to CORP01.dmp

/var/backup/corpdb/datapump/CORP01.dmp        0%  192MB  1MB/s   992.0KB/s  5:58:45 ETA


Where          0%             -      Percentage of the file that has been transferred at this point
                    192MB       -     Amount of Data Transferred
                    1Mbps        -     Transfer rate
                    ETA            -     Estimated Time of Arrival(i.e. Remaining Time)

NOTE :   We can also give sftp>  get *.dmp /var/backup/corpdb/datapump   but since we are connected from /var/backup/corpdb/datapump   location so no need of specifying path, by default it will place all the files in the location from where you are connected to SFTP.

Similarly the same operation can be done through prod server also but the only difference is use 'PUT' command in place of 'GET' command.

                                                

Hope this helps, ALL THE BEST

11g data pump parameters

Export Parameters :

Parameter	Description
abort_step	Undocumented feature
access_method	Data Access Method – default is Automatic
attach	Attach to existing job – no default
cluster	Start workers across cluster; default is YES
compression	Content to export: default is METADATA_ONLY
content	Content to export: default is ALL
current_edition	Current edition: ORA$BASE is the default
data_options	Export data layer options
directory	Default directory specification
dumpfile	dumpfile names: format is (file1,…) default is expdat.dmp
encryption	Encryption type to be used: default varies
encryption_algorithm	Encryption algorithm to be used: default is AES128
encryption_mode	Encryption mode to be used: default varies
encryption_password	Encryption key to be used
estimate	Calculate size estimate: default is BLOCKS
estimate_only	Only estimate the length of the job: default is N
exclude	Export exclude option: no default
filesize	file size: the size of export dump files
flashback_time	database time to be used for flashback export: no default
flashback_scn	system change number to be used for flashback export: no default
full	indicates a full mode export
include	export include option: no default
ip_address	IP Address for PLSQL debugger
help	help: display description on export parameters, default is N
job_name	Job Name: no default
keep_master	keep_master: Retain job table upon completion
log_entry	logentry
logfile	log export messages to specified file
metrics	Enable/disable object metrics reporting
mp_enable	Enable/disable multi-processing for current session
network_link	Network mode export
nologfile	No export log file created
package_load	Specify how to load PL/SQL objects
parallel	Degree of Parallelism: default is 1
parallel_threshold	Degree of DML Parallelism
parfile	parameter file: name of file that contains parameter specifications
query	query used to select a subset of rows for a table
remap_data	Transform data in user tables
reuse_dumpfiles	reuse_dumpfiles: reuse existing dump files; default is No
sample	Specify percentage of data to be sampled
schemas	schemas to export: format is ‘(schema1, .., schemaN)’
service_name	Service name that job will charge against
silent	silent: display information, default is NONE
status	Interval between status updates
tables	Tables to export: format is ‘(table1, table2, …, tableN)’
tablespaces	tablespaces to transport/recover: format is ‘(ts1,…, tsN)’
trace	Trace option: enable sql_trace and timed_stat, default is 0
transport_full_check	TTS perform test for objects in recovery set: default is N
transportable	Use transportable data movement: default is NEVER
transport_tablespaces	Transportable tablespace option: default is N
tts_closure_check	Enable/disable transportable containment check: def is Y
userid	user/password to connect to oracle: no default
version	Job version: Compatible is the default

Import Parameters :

Parameter	Description
abort_step	Undocumented feature
access_method	Data Access Method – default is Automatic
attach	Attach to existing job – no default
cluster	Start workers across cluster; default is Y
content	Content to import: default is ALL
data_options	Import data layer options
current_edition	Applications edition to be used on local database
directory	Default directory specification
dumper_directory	Directory for stream dumper
dumpfile	import dumpfile names: format is (file1, file2…)
encryption_password	Encryption key to be used
estimate	Calculate size estimate: default is BLOCKS
exclude	Import exclude option: no default
flashback_scn	system change number to be used for flashback import: no default
flashback_time	database time to be used for flashback import: no default
full	indicates a full Mode import
help	help: display description of import parameters, default is N
include	import include option: no default
ip_address	IP Address for PLSQL debugger
job_name	Job Name: no default)’
keep_master	keep_master: Retain job table upon completion
logfile	log import messages to specified file
master_only	only import the master table associated with this job
metrics	Enable/disable object metrics reporting
mp_enable	Enable/disable multi-processing for current session
network_link	Network mode import
nologfile	No import log file created
package_load	Specify how to load PL/SQL objects
parallel	Degree of Parallelism: default is 1
parallel_threshold	Degree of DML Parallelism
parfile	parameter file: name of file that contains parameter specifications
partition_options	Determine how partitions should be handle: Default is NONE
query	query used to select a subset of rows for a table
remap_data	Transform data in user tables
remap_datafile	Change the name of the source datafile
remap_schema	Remap source schema objects to new schema
remap_table	Remap tables to a different name
remap_tablespace	Remap objects to different tablespace
reuse_datafiles	Re-initialize existing datafiles (replaces DESTROY)
schemas	schemas to import: format is ‘(schema1, …, schemaN)’
service_name	Service name that job will charge against
silent	silent: display information, default is NONE
skip_unusable_indexes	Skip indexes which are in the unsed state)
source_edition	Applications edition to be used on remote database
sqlfile	Write appropriate SQL DDL to specified file
status	Interval between status updates
streams_configuration	import streams configuration metadata
table_exists_action	Action taken if the table to import already exists
tables	Tables to import: format is ‘(table1, table2, …, tableN)
tablespaces	tablespaces to transport: format is ‘(ts1,…, tsN)’
trace	Trace option: enable sql_trace and timed_stat, default is 0
transform	Metadata_transforms
transportable	Use transportable data movement: default is NEVER
transport_datafiles	List of datafiles to be plugged into target system
transport_tablespaces	Transportable tablespace option: default is N
transport_full_check	Verify that Tablespaces to be used do not have dependencies
tts_closure_check	Enable/disable transportable containment check: def is Y
userid	user/password to connect to oracle: no default
version	Job version: Compatible is the default

The Following commands are valid while in interactive mode.

Command                                           Description

--------------------                                   ----------------------------------------------------------

ADD_FILE                                                    Add dumpfile to dumpfile set.

CONTINUE_CLIENT                              Return to logging mode. Job will be re-started if idle.

EXIT_CLIENT                                          Quit client session and leave job running.

FILESIZE                                                  Default filesize (bytes) for subsequent ADD_FILE commands.

HELP                                                          Summarize interactive commands.

KILL_JOB                                                Detach and delete job.

PARALLEL                                              Change the number of active workers for current job.

START_JOB                                            Start/resume current job.

STATUS                                                   Frequency (secs) job status is to be monitored where  the default (0) will show new status when available.

                                                                 STATUS[=interval]

STOP_JOB                                      Orderly shutdown of job execution and exits the client.

                                                             STOP_JOB=IMMEDIATE performs an immediate shutdown of the Data Pump job.

Maximum Datafile Size In Oracle Database

Each Oracle datafile can contain maximum (2^22) i.e., 4194303 (4 Million) data blocks. So maximum file size is 4194303 multiplied by the database block size.

In a database there can have maximum of 65533 data files.

In database, db_block_size can have 2K, 4K, 8K, 16K and 32K

SQL> show  parameter  db_block_size;    gives your data block size.

Block Size | Maximum Datafile Size
---------------------------------------------
2k      4194303 * 2k = 8 GB
4k      4194303 * 4k = 16 GB
8k      4194303 * 8k = 32 GB
16k     4194303 * 16k = 64 GB
32k     4194303 * 32k = 128 GB

In Oracle Database 10g, BIGFILE tablespace was introduced. The BIGFILE tablespace can ONLY have a single datafile, but this datafile can contain maximum (2^32) i.e., 4294967295 (4 billion) data blocks.

Block Size | Maximum Datafile Size
---------------------------------------------
2k     4294967295 * 2k = 8 TB
4k     4294967295 * 4k = 16 TB
8k     4294967295 * 8k = 32 TB
16k    4294967295 * 16k = 64 TB
32k    4294967295 * 32k = 128 TB

Maximum database size= maximum datafile size * maximum datafile can be in a database.

So maximum data file and database size depends on data block size

data pump TABLE_EXISTS_ACTION parameter

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

The default value is "SKIP"


NOTE: If  CONTENT=DATA_ONLY  is specified then the default is "APPEND" not SKIP.

The parameter TABLE_EXISTS_ACTION applies only to the Data Pump Import operation. This parameter is used when you import a table which is already exists in import schema. So if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may be interested about rest of the three values-

APPEND - The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).
It Loads Rows from source and leaves existing rows UNCHANGED.

TRUNCATE - If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE - This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

More about Data pump :   http://chandu208.blogspot.com/2011/04/oracle-data-pump.html

Oracle spool command

What is SPOOL ?
Spool Command in ORACLE is used to print data from oracle tables into other files, meaning you can send all the sql outputs into any file you wish to.

How to SPOOL from ORACLE in CSV format ??

Login to sqlplus

Set echo off;

Set Heading off;
Set define Off;
Set feedback Off;
set verify off;
Set serveroutput On;
SET PAGESIZE 5000
SET LINESIZE 120

SQL >   Spool c:\file.csv     (Windows)

SQL >  SELECT COL1||','||COL2||','||COL3 FROM TABLE_NAME;

SQL>  Spool Off;

Set define On;
Set feedback On;

Set heading on;
Set verify on;

Ex:  Recently i written a spool command for making all the tables and indexes max extent sizes to unlimited because lot of tables and indexes max extent size have NULL value

Set echo off;

Set Heading off;
Set define Off;
Set feedback Off;
Set verify off;
Set serveroutput On;
SET PAGESIZE  5000
SET LINESIZE 120

SQL>   Spool   extent.sql

SQL>   select   'alter '||   object_type||’  ‘||object_name||’   '||’ storage (maxextents unlimited);'

            from  dba_objects   where   object_type in ('TABLE','INDEX')   and owner = 'XXX';

spool off

SQL> @extent.sql                       (for executing spool command)

If u didn’t specify anything after the file name(ex: extent  instead of extent.sql) then oracle by default generates output file as ‘.log’ extention(i.e., extent.log)

If we have very few tables in the database instead of writing spool command we can do manually one after another using

SQL >   alter  table  tab_name  move storage (maxextents unlimited);
 Table altered.

Or

SQL>   alter  index  ind_name  move  storage (maxextents unlimited);

 Index altered.

Using single command we can write dynamic sql script to do the same changes for all the objects

NOTE:

In Linux the output can be seen in the Directory from where you entered into the SQLPLUS

In Windows the output file is located where you specified in the spool

APPEND:

If you want the sql output to append into any existing file then you can do the below

login to sqlplus

SQL > spool /opt/oracle/File.log  append

Data pump Schema Refresh

Schema refresh is an regular job for any DBA specially during migration projects, so today I decide to write a post about how we do a schema refresh using Data pump.

Assuming here schema (SCOTT) is  being refreshed  from source (PROD) to Target (TEST) on oracle 11g server using SYSTEM user (use can do with any privileged user)

SQL>  select  banner  from  v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

On Source side (PROD) :

Create a directory or use an existing directory (ex: data_pump_dir) and grant read and write permissions on this directory to user ‘SYSTEM‘  --> If you do as SYS user this grant is not required

SQL >   grant  read, write  on  directory  data_pump_dir  to   system;

Grant Succeeded.

NOTE: Always need to make sure there is enough space to accommodate Dump files

Step 1:   Exporting the data from prod(source) 

$   vi   expdp_refresh_schema.sh

$  expdp  system/****@sourcehostname   dumpfile=expdpschema.dmp   Directory=data_pump_dir    logfile=export.log   schemas= scott

$  nohup  sh  expdp_refresh_schema.sh>refresh_schema.out &

Nohup is NOT mandatory as datapump process always runs on the server

Step 2 :  Copying the dumpfiles from source to target

For copying Dumpfiles from one server to another server we can use either Winscp(Graphical tool for copying files from windows to linux and  vice versa),FTP, SFTP, SCP, etc.

$ scp  expdpschema.dmp   system@TargetHostname:/home/oracle/datapump

Here I’m copying dumpfile from source to the target /home/oracle/datapump  location

Step 3 :  Importing data from dumpfile into target database

Before importing dunpfile into target(TEST) make sure you delete or backup all the objects in that schema, to clear all objects from particular schema run the script from here  

$ impdp  system/****@targethostname   dumpfile=expdpschema.dmp   Directory=data_pump_dir    logfile=import.log   remap_schema= scott:newscott

Step 4 :   Verify target database object counts with source db

SQL>   select   count(*)  from  dba_objects   where  owner=’NEWSCOTT’ ;

SQL>   select  count(*)  from  dba_tables  where  owner =’NEWSCOTT’;

The above results  should be same as that of source  ‘scott’  schema

Check More of Datapump.........
To Kill a running Data pump Job :  http://chandu208.blogspot.com/2011/09/data-pump-scenarios.html
About data pump :  http://chandu208.blogspot.com/2011/04/oracle-data-pump.html