ORACLE 10g : Data Pump Export&Import

login as: oracle
oracle@192.168.1.31’s password:
Last login: Sat Dec 13 11:59:51 2014 from 192.168.1.174
[oracle@oracle ~]$ export ORACLE_SID=india
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Dec 13 12:44:35 2014

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> create user mohan identified by mohan;

User created.

SQL> grant resource,connect to mohan;

Grant succeeded.

SQL> select name from v$tablespace;

NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/india/system01.dbf
/u01/app/oracle/oradata/india/undotbs01.dbf
/u01/app/oracle/oradata/india/sysaux01.dbf
/u01/app/oracle/oradata/india/users01.dbf

SQL> create tablespace mohan_tbs datafile ‘/u01/app/oracle/oradata/india/mohan.dbf’ size 10m;

Tablespace created.

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/india/system01.dbf
/u01/app/oracle/oradata/india/undotbs01.dbf
/u01/app/oracle/oradata/india/sysaux01.dbf
/u01/app/oracle/oradata/india/users01.dbf
/u01/app/oracle/oradata/india/mohan.dbf

SQL> alter user mohan default tablespace mohan_tbs quota unlimited on mohan_tbs;

User altered.

SQL> conn mohan/mohan
Connected.

SQL> create table mohan_dpump(id number,name varchar2(8));

Table created.

SQL> begin
2  for id in 1..10000 loop
3  insert into mohan_dpump values(id,’mk’);
4  end loop;
5  end;
6  /

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from mohan_dpump;

COUNT(*)
———-
20000

SQL> insert into mohan_dpump(id,name) values(5,’mmk’);

1 row created.

SQL> select name from mohan_dpump where id=5;

NAME
——–
mk
mk
mmk

SQL> select name from mohan_dpump where id=1;

NAME
——–
mk
mk

SQL> select name from mohan_dpump where id=6;

NAME
——–
mk
mk

SQL> commit;

Commit complete.

SQL> !
[oracle@oracle ~]$ pwd
/home/oracle
[oracle@oracle ~]$ mkdir mohan_dp
[oracle@oracle ~]$ cd mohan_dp/
[oracle@oracle mohan_dp]$ ls
[oracle@oracle mohan_dp]$ exit
exit

SQL> conn sys/sys sysdba
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SQL> conn sys/sys as sysdba
Connected.
SQL> CREATE OR REPLACE DIRECTORY mohan_dp AS ‘/home/oracle/mohan_dp/’;

Directory created.

SQL> grant read, write ON DIRECTORY mohan_dp TO mohan;

Grant succeeded.

SQL>
SQL> desc dba_directories;
Name                                      Null?    Type
—————————————– ——– —————————-
OWNER                                     NOT NULL VARCHAR2(30)
DIRECTORY_NAME                            NOT NULL VARCHAR2(30)
DIRECTORY_PATH                                     VARCHAR2(4000)

SQL> conn mohan/mohan
Connected.
SQL> !
[oracle@oracle ~]$ expdp tables=mohan_dpump directory=mohan_dp dumpfile=mohan_dpump.dmp logfile=mohan_dpump.log;

Export: Release 10.2.0.1.0 – Production on Saturday, 13 December, 2014 13:32:07

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

Username: mohan
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “MOHAN”.”SYS_EXPORT_TABLE_01″:  mohan/******** tables=mohan_dpump directory=mohan_dp dumpfile=mohan_dpump.dmp logfile=mohan_dpump.log
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “MOHAN”.”MOHAN_DPUMP”                       219.6 KB   20001 rows
Master table “MOHAN”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MOHAN.SYS_EXPORT_TABLE_01 is:
/home/oracle/mohan_dp/mohan_dpump.dmp
Job “MOHAN”.”SYS_EXPORT_TABLE_01″ successfully completed at 13:33:10

You have new mail in /var/spool/mail/oracle
[oracle@oracle ~]$ cd mohan_dp/
You have new mail in /var/spool/mail/oracle
[oracle@oracle mohan_dp]$ ls
mohan_dpump.dmp  mohan_dpump.log
[oracle@oracle mohan_dp]$

SQL> drop table mohan_dpump;
drop table mohan_dpump
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> commit;

Commit complete.

SQL> drop table mohan_dpump;

Table dropped.

SQL> desc mohan_dpump;
ERROR:
ORA-04043: object mohan_dpump does not exist

SQL> commit;

Commit complete.

SQL> !

[oracle@oracle mohan_dp]$ impdp tables=mohan_dpump  directory=mohan_dp dumpfile=mohan_dpump.dmp logfile=impmohan_dpump.log;

Import: Release 10.2.0.1.0 – Production on Saturday, 13 December, 2014 13:53:17

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

Username: mohan
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “MOHAN”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “MOHAN”.”SYS_IMPORT_TABLE_01″:  mohan/******** tables=mohan_dpump directory=mohan_dp dumpfile=mohan_dpump.dmp logfile=impmohan_dpump.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “MOHAN”.”MOHAN_DPUMP”                       219.6 KB   20001 rows
Job “MOHAN”.”SYS_IMPORT_TABLE_01″ successfully completed at 13:53:29

You have new mail in /var/spool/mail/oracle
[oracle@oracle mohan_dp]$
[oracle@oracle mohan_dp]$ exit
exit

SQL> show user;
USER is “MOHAN”
SQL> desc mohan_dpump;
Name                                      Null?    Type
—————————————– ——– —————————-
ID                                                 NUMBER
NAME                                               VARCHAR2(8)

SQL> select count(*) from mohan_dpump;

COUNT(*)
———-
20001

SQL>

Share Your Thoughts!

Windows Postgresql Auto Backup_Restore from Production To Pre-Production

Copy the below script’s in notepad and save as Batch file.

Backup_Restore.bat script as follows;

———————————————————————————————————————————————————————————————————————

@echo off

set BackupName=pothys_restore
set ProductionDB=pothysdev
set ProductionServerIP=192.168.0.231
set PreProductionDB=pothystest
set ProductionDBAdminPwd=postgres
set PreProductionDBAdminPwd=postgres
set AdempierePwd=123456

cd\
cd C:\Program Files\PostgreSQL\8.4\bin\
SET PGPASSWORD=%ProductionDBAdminPwd%

echo ========================
echo Production Server Backup
echo ========================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

pg_dump.exe –host %ProductionServerIP% –port 5432 –username postgres –format plain –no-owner –verbose –file “C:\%BackupName%.sql” %ProductionDB%

echo ===========================
echo Deleting The DB In Pre-Prod
echo ===========================

SET /P ANSWER=Do you want to continue (Y/N)?
echo You chose: %ANSWER%
if /i {%ANSWER%}=={y} (goto :yes)
if /i {%ANSWER%}=={yes} (goto :yes)
goto :no

:no
echo You pressed no!
exit /b 1

:yes
echo You pressed yes!
rem exit /b 0
echo =========================
echo Terminating Connections..
echo =========================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

SET PGPASSWORD=%PreProductionDBAdminPwd%
psql -U postgres -c “SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE pg_stat_activity.datname = ‘%PreProductionDB%’ AND procpid <> pg_backend_pid();”
psql -U postgres -c “DROP DATABASE %PreProductionDB%;”

echo ===================
echo DB Deletion Done !!
echo ===================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

echo ===========================
echo Creating New DB In Pre-Prod
echo ===========================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

psql -U postgres -c “CREATE DATABASE %PreProductionDB% WITH ENCODING=’UTF8′ OWNER=adempiere CONNECTION LIMIT=-1;”
psql -U postgres -c “\q”

echo ==================
echo DB restore started
echo ==================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

SET PGPASSWORD=%AdempierePwd%
psql -f c:\%BackupName%.sql -d %PreProductionDB% -U adempiere -h localhost

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

echo ========================
echo DB Successfully Restored
echo ========================

——————————————————————————————————————————————————————————————————————–

DB_Restore.bat script as follows;

——————————————————————————————————————————————————————————————————————–

@echo off
Backup_Restore.bat 2>&1 | wtee -a DB_Restore_log.log
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-3 delims=/:/ ” %%a in (‘time /t’) do (set mytime=%%a-%%b-%%c)
move C:\DB_Restore_log.log C:\DB_Restore_log-%mydate%_%mytime%.log

———————————————————————————————————————————————————————————————————————-

Use the below link to download the ‘wtee.exe’

http://code.google.com/p/wintee/downloads/detail?name=wtee.exe&can=2&q=

 

Step 1:

Copy the two batch files(Backup_Restore.bat & DB_Restore.bat) and one executable file(wtee.exe) in c: drive.

Step 2:

Open the Backup_Restore.bat file and edit the following variables according to your environment.

• set BackupName=pothys_restore

Here replace the ‘pothys_restore’ with the name that the backup file should be saved.

(Backup file name eg: pothys_restore.sql)

• set ProductionDB=pothysdev

Here replace the ‘pothysdev’ with the name of production database to be backup.

(DB name to be backup from production server eg: pothys)

• set ProductionServerIP=192.168.0.231

Here replace the ‘192.168.0.231’ with the ip of production server.

(IP Address of production server to take backup eg:192.168.3.1)

• set PreProductionDB=pothystest

Here replace the ‘pothystest’ with the name of pre-production database to be restore

(DB name to be restore in pre-production eg: pothys)

• set ProductionDBAdminPwd=postgres

Here replace the ‘postgres’ with the production server database admin password.

(Production DataBase admin password eg: postgres)

• set PreProductionDBAdminPwd=postgres

Here replace the ‘postgres’ with the pre-production server database admin password

(PreProduction DataBase admin password eg: postgres)

• set AdempierePwd=123456

Here replace the ‘123456’ with the database user adempiere password.

(Database user adempiere password eg: 123456)

After changes made, save and close the Backup_Restore.bat file.

Step 3:

Double click the DB_Restore.bat to start the process.

Step 4:

Before restoring, we need to drop the database in pre-production server with the name entered in the script if already exist.

Script will ask your confirmation to drop database in pre-production server, press ‘y’ to confirm the drop DB. If you press ‘n’, process will be stopped without proceeding next.

Step 5:

Log file was created for all activities of script with the name DB_Restore_log in c: drive.

Strictly advised to verify the log.

Finally add the ‘DB_Restore.bat’ to windows Scheduled Task to perform automatically.

Share your thoughts!

 

 

Mysql backup script with date & time stamp

Mysql Script:

#!/bin/bash
mysqldump -u root -p(yourpasswd) –routines –databases databasename > destination/folderpath/in which dump file to be stored/name.sql
thetime=`date +%Y-%m-%d–%H:%M:%S`
mv /dump file location/bugs.sql /destination of dump file with date and time stamp/name-$thetime.sql

Sample Script:

#!/bin/bash
mysqldump -u root -proot –routines –databases bugs > /home/jug/Desktop/mysql/bugs.sql
thetime=`date +%Y-%m-%d–%H:%M:%S`
mv /home/jug/Desktop/mysql/bugs.sql /home/jug/Desktop/mysql/bugs-$thetime.sql

Thats it..

Linux / UNIX: Encrypt Backup Tape Using Tar & OpenSSL

How do I make sure only authorized person access my backups stored on the tape drives (DAT, DLT, LTO-4 etc) under Linux or UNIX operating systems? How do I backup /array22/vol4/home/ to /dev/rmt/5mn or /dev/st0 in encrypted mode?

You can easily encrypt data to tape using combination of tar and openssl commands. The following is software based solution based upon encryption algorithms supported by openssl tool. Encrypted backup should be used when storing sensitive data on removable media or when storing backups on shared NAS / SAN servers or online backup servers. When using encryption the openssl ask for a password before you can create, view, open, or restore the files included in the backup. This is based upon pipes concept.

Backup Data

The following shows an example of writing the contents of “tapetest” to tape:

tar zcvf - /array22/vol4/home | openssl des3 -salt | dd of=/dev/st0

An encryption password would be entered by the administrator or backup operator i.e. the above will encrypt a tape using triple DES in CBC mode using a prompted password. You can put password in script itself:

tar zcvf – /array22/vol4/home | openssl des3 -salt -k “Your-Password-Here” | dd of=/dev/st0

Reading (listing) Files

Type the command as follows:

dd if=/dev/st0 | openssl des3 -d -salt | tar ztvf -

OR

dd if=/dev/st0 | openssl des3 -d -salt -k "Your-Password-Here" | tar ztvf -

Restore The Data

Use the following command to read and restore data back:

dd if=/dev/st0 | openssl des3 -d -salt | tar xzf -

OR

dd if=/dev/st0 | openssl des3 -d -salt -k "Your-Password-Here" | tar xzf -

Where,

* dd : Convert and copy a file.
* /dev/st0 : Tape device name.
* openssl : The OpenSSL toolkit command line utility.
* tar : The tar archiving utility.
* des3 : Triple-DES Cipher (Triple DES is the common name for the Triple Data Encryption Algorithm).
* -salt : The -salt option should ALWAYS be used if the key is being derived from a password unless you want compatibility with previous versions of OpenSSL and SSLeay. Without the -salt option it is possible to perform efficient dictionary attacks on the password and to attack stream cipher encrypted data. The reason for this is that without the salt the same password always generates the same encryption key. When the salt is being used the first eight bytes of the encrypted data are reserved for the salt: it is generated at random when encrypting a file and read from the encrypted file when it is decrypted. (source enc man page)

Hardware vs Software Encryption

The software encryption is different from the hardware encryption. The hadrware based encryption needs additional software+hardware and it use keys (and/or password) to protect data. I suggest you read vendor site such as HP or IBM to get further details on hardware encryption which may or may not be supported by your backup devices.

Share your thoughts by comments!

Videos Of Router Configuration:

Router Startup:

Router Configuration Modes:

Access List Concept:

Access List Configuration:

NAT & Rip Configuration:

TFTP Backup: