Feeds:
Posts
Comments

Archive for the ‘Backup database’ Category

Hi friends,

 

Last one year i have work a lot on export and import for small to huge database, from specific objects to schema to and query based backup.

 

here I am listed some of my syntax which you can note down for your reference.

  1. export objects from specific schema which match the specific criteria.

expdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_%u.dmp logfile=BG_objects.log
INCLUDE=FUNCTION,INDEX,CONSTRAINT,PACKAGE,PROCEDURE,SEQUENCE,VIEW,TABLE:\”LIKE \’BG_%\’\” schemas=<schema_name>;

2. Import objects to the schema which were own by other schema but exclude some specific objects.

impdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_01.dmp logfile=BG_objects_imp.log EXCLUDE=TABLE:\”LIKE \’%POSTED%\’\” table_exists_action=REPLACE
remap_schema=<from_schema_name>:<to_schema_name>;

3. Import objects from one schema to other schema where tablespace for both the schema were different and from different version(like from 10g to 11g).

impdp system/oracle directory=DUMP_BACKUP dumpfile=dumpfile_17032015.dmp logfile=logfile_imp.log remap_schema=<from_schema>:<to_schema>
remap_tablespace=<from_tablespace>:<to_tablespace> table_exists_action=replace EXCLUDE=STATISTICS;

4. Export specific objects from the schema.

expdp username directory=BCKUP_DUMP dumpfile=schema_pk_objects_%U.dmp logfile=schema_pk_objects.log include=TABLE:\”LIKE \’%PK_%\’\”;

5. Export only one table.

expdp oracle directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=<Table_name>;

6.Export a big database and break the dumpfile to matching criteria to fit to the space available in the disk.

expdp SYSTEM/oracle dumpfile=DUMP1:SCHEMA_%u.dmp dumpfile=DUMP2:SCHEMA_%u.dmp filesize=96G logfile=DUMP1:schema.log full=y exclude=statistics;

7. Import a full dump which contains multiple user in different tablespace to a single common user in a single common tablespace to same database or in a different database.

impdp system/oracle directory=dump dumpfile=EPIXDB_01.dmp,EPIXDB_02.dmp full=y logfile=epixdb_imp.log remap_schema=<Schema1>:<schema_common>
remap_schema=<Schema2>:<schema_common> remap_schema=<Schema3>:<schema_common> remap_schema=<Schema4>:<schema_common> remap_schema=<Schema5>:<schema_common>
remap_schema=<Schema6>:<schema_common> remap_schema=<Schema7>:<schema_common> remap_schema=<Schema8>:<schema_common> remap_schema=<Schema9>:<schema_common> remap_schema=<Schema10>:<schema_common>
remap_schema=<Schema11>:<schema_common> remap_schema=<Schema12>:<schema_common> remap_tablespace=<tablespace1>:<common_tablespace> remap_tablespace=<tablespace2>:<common_tablespace>
remap_tablespace=<tablespace3>:<common_tablespace> table_exists_action=replace;

8.export of a object to a specific time or from a specific time this is also known as incremental backup (note:flashback log must be present)

expdp username directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=TABLE_NAME
FLASHBACK_TIME=\”to_timestamp\(to_char\(sysdate,\’yyyy-mm-dd hh24:mi:ss\’\),\’yyyy-mm-dd hh24:mi:ss\’\)\”;

9. Traditional export or exp utility to take backup of specific data.

exp user/pass file=exp.dmp log=exp.log TABLES=test query=”””where rownum< 101″””;

exp uwclass/uwclass owner=SCOTT tables=emp query=\” WHERE job=\’MANAGER\’ AND sal \>50000\” STATISTICS=NONE;

10.Export of multiple table from multiple schemas.

expdp system/password directory=BACKUP dumpfile=schema_old_table%u.dmp logfile=schema_old_tables.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′
INCLUDE=TABLE:\”IN \(\’CC_ACCOUNT\’, \’CC_ACCOUNTSERVICE\’, \’CC_ACCOUNTSERVICEOFFERING\’, \’CC_OFFERINGPROPERTY\’, \’CC_ACCOUNTPROPERTY\’, \’BG_INVOICEPOSTED\’,
\’PR_MANUALSITEDETAILS\’, \’PR_MANUALCNDNINVCODE\’, \’PR_MANUCNDN_MANUALSITEDETAILS\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG10\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG11\’,
\’BG_FINALOUTPUTXLS_MR_ADV_AUG12\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG13\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG14\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG15\’,
\’BG_INVOICEDETAILPOSTED\’, \’BG_INVOICEDETAILPOSTED\’, \’BG_INVOICEPOSTED\’, \’BG_INVOICEDETAILPOSTED\’, \’BG_INVOICEPOSTED\’, \’CRDR_INVOICEBILLHEADPOSTED\’,
\’CNDN_INVOICEBILLHEADPOSTED\’, \’CRDR_INVOICEBILLHEADPOSTED\’, \’R3G_INVOICEBILLHEADPOSTED\’\)\”;

11.Export from multiple schema but table name can be like matching criteria.

expdp system/U6dba#15@projdb directory=backup dumpfile=projdbdmp_%u.dmp logfile=projdbdmp_full.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′ INCLUDE=TABLE:\”IN\(\SELECT table_name FROM dba_tables where table_name like \’CC_%\’ \)\”;

 

Thats it..

 

Above are a snap-chat 🙂 of my huge collection of syntax which are most common in worst scenario.Still if you have any other critical situation which you are facing please let me know in the comment, It would be a honor to help.

 

Hope it helps.

 

With Regards

Nimai Karmakar

 

 

Read Full Post »

Today Hemant Sir have posted a good link that represent some basic for oracle HIGH AVAILABILITY with demonstration.

 

Please click the link…

 

oracle High availability demos…

 

Hope this helps…

Read Full Post »

how to export a backup of a remote database

Today we will learn on of important datapump utility introduced from oracle 10g.

how to impdp/expdp from a remote database or to a remote database.

first update the Tnsnames.ora file in your system

vi $ORACLE_HOME/network/admin/tnsnames.ora

PRODDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 194.9.*.*)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRODDB)
)
)

 

save and exit

now open the sql editor in console.

 

SQL> create directory dump_dir as ‘/tmp’;
SQL> create public database link source_db using ‘PRODDB’;
SQL> exit
impdp system/system directory=dpump_dir network_link=source_db schemas=scott remap_schema=scott:dest_user;

The command above will import the scott schema from source_db to the current database, and rename the schema to dest_user. If the dest_user does not exist, it will be created.

expdp system/password directory=dump_dir network_link=source_db schemas=scott;

 

Read Full Post »

  How to crontab a schedule job in linux of oracle database

first create a scheduler at the location 
vi /home/oracledb/Desktop/Nimai/export.sh

now in insert mode write the lines of you export backup
(in my enviorment my script to export backup is)

edate=`date +%d_%m_%Y` #this is declaring a variable edate for today date
export ORACLE_SID=PRODDB #My System Identifier for Oracle
export ORACLE_HOME=/apps/oracledb/product/10.2.0/orahome #export the oracle home
export PATH=/apps/oracledb/product/10.2.0/orahome/bin #export bin path 
expdp Newton/password directory=DUMP_DIR dumpfile=schedule_nimai_$edate.dmp logfile=schedule_$edate.log schemas=newton;

now save it through esc+:w
and then exit

now we have to schedule this schedule at any specific time when you need.

In my enviorment I have set the schedule at everyday 2 pm in weekdays.

00 14 * * 1-6 sh /home/oracledb/Desktop/export.sh >> /dev/null

to edit the cronjob crontab -e
to list cronjob crontab -l

note-
00 defines minute 
14 defines 2 pm
* means every day
* means every month
1-6 defines from monday to saturday (sunday-0)

Scheduling explained 
As you can see there are 5 stars. The stars represent different date parts in the following order:
minute (from 0 to 59) 
hour (from 0 to 23)
day of month (from 1 to 31)
month (from 1 to 12)
day of week (from 0 to 6) (0=Sunday)

Execute every minute
If you leave the star, or asterisk, it means every. Maybe that's a bit unclear. Let's use the the previous example again:

* * * * * sh /bin/execute/this/script.sh
They are all still asterisks! So this means execute /bin/execute/this/script.sh:

every minute of every hour

of every day of the month

of every month

and every day in the week.

In short: This script is being executed every minute. Without exception.

Execute every Friday 1AM

So if we want to schedule the script to run at 1AM every Friday, we would need the following cronjob:

0 1 * * 5 sh /bin/execute/this/script.sh

Get it? The script is now being executed when the system clock hits:

minute: 0

of hour: 1

of day of month: * (every day of month)

of month: * (every month)

and weekday: 5 (=Friday)

Execute on workdays 1AM

So if we want to schedule the script to Monday till Friday at 1 AM, we would need the following cronjob:

0 1 * * 1-5 sh /bin/execute/this/script.sh

Get it? The script is now being executed when the system clock hits:

minute: 0

of hour: 1

of day of month: * (every day of month)

of month: * (every month)

and weekday: 1-5 (=Monday til Friday)

Execute 10 past after every hour on the 1st of every month

Here's another one, just for practicing

10 * 1 * * sh /bin/execute/this/script.sh

Fair enough, it takes some getting used to, but it offers great flexibility.

Neat scheduling tricks

What if you'd want to run something every 10 minutes? Well you could do this:

0,10,20,30,40,50 * * * * sh /bin/execute/this/script.sh

But crontab allows you to do this as well:

*/10 * * * * sh /bin/execute/this/script.sh

Special words

If you use the first (minute) field, you can also put in a keyword instead of a number:

@reboot     Run once, at startup

@yearly     Run once  a year     "0 0 1 1 *"

@annually   (same as  @yearly)

@monthly    Run once  a month    "0 0 1 * *"

@weekly     Run once  a week     "0 0 * * 0"

@daily      Run once  a day      "0 0 * * *"

@midnight   (same as  @daily)

@hourly     Run once  an hour    "0 * * * *

Leave the rest of the fields empty so this would be valid:

$ @daily sh /bin/execute/this/script.sh

*/10 * * * * sh /bin/execute/this/script.sh | mail -s "Cronjob ouput" yourname@yourdomain.com

trashing the output

* * * * * sh /apps/oracle/scripts/cloning.sh >> /dev/null

This  will for the output to this logfile

* * * * * sh /apps/oracle/scripts/cloning.sh >> /apps/oracle/scripts/file_name.log

Read Full Post »

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have

ursvenkat's Blog

Oracle Applications DBA Blog

The Ivica Arsov Blog

Database Management & Performance

gumpx

DBA's online diary