Uncategorized

by

Allowing Read Only FTP Access to another account

On an ftp server, needed to create the ability for a user to have read only permission against another account.

Scenario

Master Software Distribution – account = source  with Read Write permission on its home directory

customer account needing read only access to a nominated share folder upon the master software directory.

AN easy fix is to  mount the source directory onto the customers home folder

 

 

master source file : = /home/master/source

customer = /home/customer

mkdir /home/customer/master
mount --bind /home/master/source /home/customer/masterfiles

So now, when the user logs in they will see a directory called masterfiles which has read only permission

 

by

oracle Export

Exporting Data from oracle

Oracle provides a command line method for extracting data from the database: export. Use the corresponding import utility to load the data into a destination system.

Note. 11gR2. See for notes on potential problems when taking data from 11gR2

this comes in two forms

  1. exp (deprecated from oracle 10g )
  2. expdp (from Oracle 10g onwards)

these programs are located in the ORACLE_HOME\bin directory. eg \oracle\product\11.2.0\bin\ – \ – your path may be different depending on installation preference and operating system.

  • Note. Imp can only read files generated by exp. Similarly, impdp can only read files generated by expdp. They are not mutually compatible.
  • source exports generated from a higher release than that of the destination database generally will not work – see expdp Compatibility parameter for exception to this rule.

Run the following from the command line as appropriate to your operating system.

eg windows :  start > run > cmd

Depending on your local path setting, you may need to change the program source directory to run the program

eg

c:\>  cd c:\oracle\product\11.2.0\bin\

Using exp

Exp is very useful, especially when executing from a client pc. It is however deprecated and will not be available in future releases.
the command can be run interactively, or as one whole command

to export a schema to a single file:

EXPusername/password FILE=path[filename] LOG=PATH[LOGFILENAME] CONSISTENT=Y

eg exp aesuser/password file=\dumps\mydumpfile LOG=\dumps\mylogfile CONSISTENT=Y
  • this will export the logged in schema to the given filename.

Additional operators can be added onto this command. :

NO ROW DATA

If you do not require the content data and wish only the METADATA, add the operator ” ROWS=N”
eg

exp aesuser/password file=\dumps\mydumpfile  LOG=\dumps\mylogfile  ROWS=N

Good practices

  • Always take care about CHARSETS when you do export and import. Using the wrong ones can convert your data in a lossy manner. The best situation is when your source and destination database have the same character sets, so you can avoid completely any character conversion. You control this behaviour by setting NLS_LANG environment variable appropriately. When not set properly you may see ‘Exporting questionable statistics’ messages
  • You may need to patch your Oracle client (where you are running exp/imp) to the same level as the Oracle server to prevent errors

2. EXPDP

expdp is a powerful took available from Oracle 10g onwards
Requirements:

  1. expdp program
  2. [EMEAOPS:oracle_directory] pre-created. An oracle directory is a logical link from the database to a physical location on the network which is visible from the database server
    to export a schema to a single file:
EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename]  LOGFILE=PATH[LOGFILENAME]

eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile

  • this will export the logged in schema to the given filename to the location associated with oracle directory “DUMPS”. A logfile will also be output to that same location. Filename of both as specified in the command.

Expdp comes with many operators, below is not exhaustive, but common requirements below.

NO ROW DATA

the operator to use here is
CONTENT=

choices:

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
  • ALL unloads both data and metadata. This is the default.
  • DATA_ONLY unloads only table row data; no database object definitions are unloaded.
  • METADATA_ONLY unloads only database object definitions; no table row data is unloaded.

example:

EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename] LOGFILE=PATH[LOGFILENAME] CONTENT=xxx

eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile CONTENT=metadata_only

Exclude named objects

The exclude operator allows us to

  • EXCLUDE=SEQUENCE
    EXCLUDE=TABLE:”IN (’EMP’,’DEPT’)”
    EXCLUDE=INDEX:”= ‘MY_INDX’”

eg

EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename] LOGFILE=PATH[LOGFILENAME] EXCLUDE=XXXX

eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile EXCLUDE=TABLE:"IN ('PRITEMDATA','PRITEMDATARCHIVE')"
  • the above will export the entire schema with the exception of table PRITEMDATA and PRITEMDATAARCHIVE. ** Note exclude/include list is enclosed in double quotes** object name need to be enclosed in single quotes.

Include names objects

  • INCLUDE=PROCEDURE:”LIKE ‘MY_PROC_%'”
    INCLUDE=TABLE:”‘> ‘E'”

Compatibility

you can set a version to use with expdp for example, if you needed to load the data into an older release than that of the source.

VERSION=
{COMPATIBLE | LATEST | version_string}
  • COMPATIBLE – This is the default value. The version of the metadata corresponds to the database compatibility level. Database compatibility must be set to 9.2 or higher.
  • LATEST – The version of the metadata corresponds to the database version.
  • version_string – A specific database version (for example, 11.1.0). In Oracle Database 11g, this value cannot be lower than 9.2.
EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename] LOGFILE=PATH[LOGFILENAME] EXCLUDE=XXXX VERSION=xxx

eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile EXCLUDE=TABLE:"IN ('PRITEMDATA','PRITEMDATARCHIVE')" VERSION=11.1