Tuesday, February 14, 2012

Block change tracking (BCT)


With Oracle 10gr1 Enterprise Edition Oracle introduced Block change tracking (BCT) feature which is used to FAST / Speed up RMAN Incremental Backup. In Oracle 9i we could create incremental backups with level 0 to 4, level 0 backup is a full backup. In Oracle 10g there are still these levels but we only use incremental level 0 and 1 backups with Oracle’s suggested backup strategy.

Before oracle 10g, During RMAN Incremental backup oracle used to read every block in database and compare the SCN in the block with the SCN in the base backup. If the block’s SCN is greater than the SCN in the backup then the block is a candidate for the New Incremental backup.  But with this BCT feature oracle instead of reading the entire database blocks it just directly reads only changed blocks results in saving lot of time.

Once BCT is enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles instead to detect changed blocks.

Logging of changed blocks is performed by the CTWR (change tracking writer) process which is also NEW background process introduced in 10g and responsible for writing data to the BCT file.  By default, the Block change tracking file is created as Oracle managed file in DB_CREATE_FILE_DEST specified location.

By default, Oracle will not record block change information. You can Enable or disable the change tracking when the database is open or mounted.

Enable Block Change tracking and set location

SQL> alter system set db_create_file_dest='location' SCOPE=BOTH;
SQL> alter database enable block change tracking;

Manually specify location for the block change tracking
SQL>alter database enable block change tracking using file 'location';

Disable block change tracking
SQL> alter database disable block change tracking;

Moving Block Change tracking file without Database shutdown:
If your database is 24x7 critical production and you cannot shut down, then follow the below steps. Please note that you must disable change tracking and you will lose the old contents of the change tracking file, if you choose this method.

Step1: Disable the change tracking
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Step2: Re-enable it at the new location
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

Moving Block Change tracking file with Database shutdown:
Step1: Determine the change tracking file:
SQL> SELECT filename FROM
 V$BLOCK_CHANGE_TRACKING;

Step2: shutdown and move or copy the tracking file.
SQL> SHUTDOWN IMMEDIATE
$ cp ‘/old_lockation/block_change_tracking.ora’ ‘/new_location/ block_change_tracking.ora’

Step3: Mount the database and rename change tracking file to new location.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME FILE ‘old_location’ TO ‘new_location’;

Step4: Open the database
SQL> ALTER DATABASE OPEN;

Block change tracking in RAC (Real Applications Clusters) environment, the file must be located on shared storage so that the file is accessible for all the nodes. BCT file is one per db, therefore BCT file is created in location defined by parameter db_create_file_dest

SQL> desc    v$block_change_tracking;

Name                        Null?                      Type
-------------------      --------------             ------------------
STATUS                                                 VARCHAR2(10)
FILENAME                                            VARCHAR2(513)
BYTES                                                    NUMBER


Change Tracking Writer (CTWR)

As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory.

When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.


You can view the size of the CTWR dba buffer by looking at v$sgastat
SQL> SELECT *
 FROM v$sgastat  WHERE name like 'CTWR%'; 


Friday, February 10, 2012

High Water Mark (HWM)


Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. But when you delete the Rows then HWM cannot come down automatically, extra steps needs to done to bring down HWM.

The HIGH WATER MARK is set at the beginning of the segment when the table is created. Deleting rows will never reset the HWM even if all the rows are deleted from the table. 

To reduce the high water mark:
a. Take export of table and re-import back
b. Truncate the table (only if you want to remove data/rows completely)

Suppose we create an empty table, the high-water mark would be at the beginning of the table segment


X
Unused space
HWM

When you insert rows into the table the high watermark will be bumped up step by step. This is done by the server process which makes the inserts.

                           
Used DATA/Rows
X
Un used Space
                                                               HWM                                                            
Now if we insert a table with 10,000 rows. And let’s assume that we deleted 5,000 rows later.        
                    
Used data
Empty blocks
X
Un used space
                           <--------------------------------------------------------------->HWM       
                                 Full table scan

  As you seen above by deleting the data, HWM does not move.  The main disadvantage of this is that oracle always read the blocks up to high water mark in case of full table scan.  You may have ever notice that doing a count (*) on empty table, takes time to show you 0 rows.  The reason for delay is setting of HWM at higher position.

NOTE:  Whenever optimizer takes full table scan, it scans all the blocks below HWM. This would degrade the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to reset the HWM.

So, now how we set the high-water mark at lower position?
The only way to set the HWM is to truncate a table. Let us see how truncate set the HWM.


No data in the segment
                                 

HWM

HWM is reset now, after truncating data.   

HWMBLOCKS = “Blocks Containing data” + “Empty blocks”

         HWMBLOCKS – “Blocks containing data”
HWM % = (----------------------------------)  * 100
                  HWMBLOCKS

You can also use DBMS_SPACE.UNUSED_SPACE procedure to determine HWM.

High Watermark = Total blocks - Unused blocks – 1

SQL> select blocks from dba_segments where owner=upper('HR') and segment_name = upper('JOBS');

SQL> analyze table hr.jobs estimate statistics;

SQL> select empty_blocks from dba_tables where owner=upper('HR') and table_name = upper('jobs');
Ways for Resetting HWM………..
Option 1
Alter table  tab_name  move tablespace
This option requires rebuilding of indexes. Since the index will be invalid/unusable after running the above command. Also users cannot use the application or reports while rebuilding the index. Make sure you stop Goldengate if this table is in extract process otherwise it will go ABEND (because of Unusable indexes)

Option 2
1. Export the data
2. Truncate the table
3. Import the table
4. Analyze the table
  Option 3
  1. Copy the table data
  2. Truncate the original table
  3. Insert back.
  
    Option 4
    Use DBMS_REDEFINITION package to copy the table (redefining the Table structure)

People confused about setting of HWM through ALTER TABLE DEALLOCATE UNUSED clause. This clause only frees unused space above the high water mark but cannot reset HWM position.

To determine the exact number of blocks that contain data i.e. space used by table below the high water mark, query rowid and get the first and last used block from the rowid.

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from table_name;

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management.

How to View High Water Mark - Step-by-Step Instructions (Doc ID 262353.1)


Thursday, February 9, 2012

Clone Database from Snapshot Standby Database

In 11g, Oracle introduced the new feature Snapshot Standby Database for Data Guard. whether it is possible to create a Database Clone out of such a Snapshot Standby Database. Maybe you want to preserve the changes that have been done during the time, your Physical Standby Database was turned into a READ-WRITE opened (Test-)Database that this is most likely possible  :-)
DGMGRL> convert database physt to snapshot standby;
Converting database "physt" to a Snapshot Standby database, please wait...
Database "physt" converted successfully
DGMGRL> show configuration

Configuration - myconf

 Protection Mode: MaxPerformance
 Databases:
 prima - Primary database
 physt - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
I continue to prepare a Clone Database by creating an spfile and a password file for it. Then I start it into NOMOUNT:
uhesse $ sqlplus sys/oracle@clon as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:00:26 2010
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             209718360 bytes
Database Buffers           96468992 bytes
Redo Buffers                6336512 bytes
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
The version in use is 11gR2, but it should work with 11gR1 the same way. Now RMAN is used to create the Clone from the Snapshot Standby:
RMAN> connect target sys/oracle@physt
connected to target database: PRIMA (DBID=1970640575)

RMAN> connect auxiliary sys/oracle@clon
connected to auxiliary database: CLON (not mounted)

RMAN> duplicate target database to clon from active database;

Starting Duplicate Db at 21-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/21/2010 17:03:19
RMAN-05541: no archived logs found in target database

RMAN> exit
Oops! I need Archivelogs on the Snapshot Standby. Note that they are in the same directory but with a different Resetlogs Identifier as the Archivelogs created when the Database was a Physical Standby Database.
uhesse $ sqlplus sys/oracle@physt as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:03:49 2010
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;
System altered.

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
/home/oracle/physt/archive/1_12_738347903.dbf
/home/oracle/physt/archive/1_8_738347903.dbf
/home/oracle/physt/archive/1_7_738347903.dbf
/home/oracle/physt/archive/1_9_738347903.dbf
/home/oracle/physt/archive/1_11_738347903.dbf
/home/oracle/physt/archive/1_10_738347903.dbf
/home/oracle/physt/archive/1_13_738347903.dbf
/home/oracle/physt/archive/1_14_738347903.dbf
/home/oracle/physt/archive/1_15_738347903.dbf
/home/oracle/physt/archive/1_1_738349098.dbf

10 rows selected.
Again we go with the duplicate:
RMAN> connect target sys/oracle@physt
connected to target database: PRIMA (DBID=1970640575)

RMAN> connect auxiliary sys/oracle@clon
connected to auxiliary database: CLON (not mounted)

RMAN> duplicate target database to clon from active database;

Starting Duplicate Db at 21-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK

contents of Memory Script:
{
 sql clone "alter system set  db_name =
 ''PRIMA'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
 sql clone "alter system set  db_unique_name =
 ''CLON'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
 shutdown clone immediate;
 startup clone force nomount
 backup as copy current controlfile auxiliary format  '/home/oracle/clon/control01.ctl';
 alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIMA'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLON'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                209718360 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6336512 bytes

Starting backup at 21-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_physt.f tag=TAG20101221T172429 RECID=6 STAMP=738350670
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-DEC-10

database mounted

contents of Memory Script:
{
 set newname for datafile  1 to
 "/home/oracle/clon/system01.dbf";
 set newname for datafile  2 to
 "/home/oracle/clon/sysaux01.dbf";
 set newname for datafile  3 to
 "/home/oracle/clon/undotbs01.dbf";
 set newname for datafile  4 to
 "/home/oracle/clon/users01.dbf";
 backup as copy reuse
 datafile  1 auxiliary format
 "/home/oracle/clon/system01.dbf"   datafile
 2 auxiliary format
 "/home/oracle/clon/sysaux01.dbf"   datafile
 3 auxiliary format
 "/home/oracle/clon/undotbs01.dbf"   datafile
 4 auxiliary format
 "/home/oracle/clon/users01.dbf"   ;
 sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 21-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/physt/system01.dbf
output file name=/home/oracle/clon/system01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/physt/sysaux01.dbf
output file name=/home/oracle/clon/sysaux01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/physt/undotbs01.dbf
output file name=/home/oracle/clon/undotbs01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/physt/users01.dbf
output file name=/home/oracle/clon/users01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 21-DEC-10

sql statement: alter system archive log current

contents of Memory Script:
{
 backup as copy reuse
 archivelog like  "/home/oracle/physt/archive/1_3_738349098.dbf" auxiliary format
 "/home/oracle/clon/archive/1_3_738349098.dbf"   ;
 catalog clone archivelog  "/home/oracle/clon/archive/1_3_738349098.dbf";
 switch clone datafile all;
}
executing Memory Script

Starting backup at 21-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=3 RECID=12 STAMP=738350751
output file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:15
Finished backup at 21-DEC-10

cataloged archived log
archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=12 STAMP=738350766

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=738350766 file name=/home/oracle/clon/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=738350767 file name=/home/oracle/clon/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=738350767 file name=/home/oracle/clon/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=738350767 file name=/home/oracle/clon/users01.dbf

contents of Memory Script:
{
 set until scn  226204;
 recover
 clone database
 delete archivelog
 ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 21-DEC-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/clon/archive/1_3_738349098.dbf
archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-DEC-10

contents of Memory Script:
{
 shutdown clone immediate;
 startup clone nomount;
 sql clone "alter system set  db_name =
 ''CLON'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
 sql clone "alter system reset  db_unique_name scope=spfile";
 shutdown clone immediate;
 startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                209718360 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6336512 bytes

sql statement: alter system set  db_name =  ''CLON'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                209718360 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6336512 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLON" RESETLOGS ARCHIVELOG
 MAXLOGFILES     26
 MAXLOGMEMBERS      5
 MAXDATAFILES      100
 MAXINSTANCES     1
 MAXLOGHISTORY      292
 LOGFILE
 GROUP  1 ( '/home/oracle/clon/log_g1m1.rdo' ) SIZE 100 M  REUSE,
 GROUP  2 ( '/home/oracle/clon/log_g2m1.rdo' ) SIZE 100 M  REUSE
 DATAFILE
 '/home/oracle/clon/system01.dbf'
 CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
 set newname for tempfile  1 to
 "/home/oracle/clon/temp01.dbt";
 switch clone tempfile all;
 catalog clone datafilecopy  "/home/oracle/clon/sysaux01.dbf",
 "/home/oracle/clon/undotbs01.dbf",
 "/home/oracle/clon/users01.dbf";
 switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/clon/temp01.dbt in control file

cataloged datafile copy
datafile copy file name=/home/oracle/clon/sysaux01.dbf RECID=1 STAMP=738350787
cataloged datafile copy
datafile copy file name=/home/oracle/clon/undotbs01.dbf RECID=2 STAMP=738350787
cataloged datafile copy
datafile copy file name=/home/oracle/clon/users01.dbf RECID=3 STAMP=738350787

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=738350787 file name=/home/oracle/clon/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=738350787 file name=/home/oracle/clon/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=738350787 file name=/home/oracle/clon/users01.dbf

contents of Memory Script:
{
 Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 21-DEC-10
So this was pretty much the same as with an ordinary Clone from a Production Database – as expected. Now we turn the Snapshot Standby back into Physical Standby, thereby losing all the changes that might be done there by using Flashback Database internally. But the Clone Database will remain, now being an independent Database with another Name and DBID than the Primary resp. Snapshot Standby.
DGMGRL> convert database physt to physical standby;
Converting database "physt" to a Physical Standby database, please wait...
Operation requires shutdown of instance "physt" on database "physt"
Shutting down instance "physt"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Continuing to convert database "physt" ...
Operation requires shutdown of instance "physt" on database "physt"
Shutting down instance "physt"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Database "physt" converted successfully
DGMGRL> show configuration

Configuration - myconf

 Protection Mode: MaxPerformance
 Databases:
 prima - Primary database
 physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
SQL> connect sys/oracle@clon as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;

NAME            DBID DATABASE_ROLE
--------- ---------- ----------------
CLON      2202079427 PRIMARY

SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;

NAME            DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA     1970640575 PHYSICAL STANDBY

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;

NAME            DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA     1970640575 PRIMARY
Conclusion: With 11g, we can very comfortably transform our Physical Standby into a fully changeable Snapshot Standby, clone that modified Database into an autonomous Database to keep the changes and afterwards transform the Snapshot Standby back into Physical Standby. During the whole action, Redo Protocol from the Primary Database can still be received – but not applied – on the Standby Site.

Tuesday, February 7, 2012

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




Auto Scroll Stop Scroll