How to Perform Import/Export in SQL Developer 3.1

In my old days i have to remember all the Import Export Commands and still remember the hard way i learned how to import/Export data from oracle database.
In SQL Developer 3.1 its as easy as it could to import/export data from database.


Question: Where is this Functionality?


Answer: In SQL Developer 3.1 its called Data Pump Functionality and it located in
View > DBA

Once you create a Connection and then its all wizard to perform import export functionality. For details steps go to the webpage

http://www.oracle-base.com/articles/misc/SqlDeveloper31DataPumpWizards.php

How to Configure RMAN

Use the following Steps to Configure RMAN

1.export ORACLE_SID= 'rmandb'
2.dbca
3.netmgr
4.we have 2 database 
5. start lsnrctl orcl em rmandb shut em orcl rmandb lsnrctl
6.startup nomount
7. alter system set sga_target=512M scope=spfile;
8. shutdown immediate;
9.conn to Rmandb
10. alter system set sga_target=512M scope=spfile;
11 emctl stop dbconsole 
12. export ORACLE_SID='rmandb' emctl stop dbconsole
13.sys/oracle@rmandb as sysdba
14.create tablespace rman_tbs datafile '/u01/app/oracle/oradata/rmandb/rman_tbs01.dbf' size 100M;

15. create user rman1 identified by rman1 
15.1 grant connect ,resource,dba to rman1


16 grant recovery_catalog_owner to rman1

17 alter user rman1 default tablespace rman_tbs
18.
conn rman1/rman1@rmandb select * from cat;

19. rman catalog rman1/rman1@rmandb  create catalog tablespace rman_tbs

Open new terminal 

20. rman target sys/oracle@orcl catalog rman1/rman1@rmandb

21. register database;


22. EM recovery catalog setting

23. preferred credentials


24  RMAN show all

Detail Steps are as under:

1. orcl (Target database)
2. create rmandb (catalog database)
3. To create catalog database perform the following steps
3.1.export ORACLE_SID= 'rmandb'
3.2.dbca
3.2.1.netmgr (register your newly created database with lisner
4.start listner and verify that both db instance are registered
(start em Target database and stop em of catalog database)
4.1 lsnrctl start
4.2 Open new terminal and start both databases using command
4.3 startup nomount (alter database open)
4.4 alter system set sga_target=512M scope=spfile;
4.4 export ORACLE_SID= 'rmandb'
4.4.1 startup nomount (this will startup rmandb database in nomount state)
4.4.2 alter system set sga_target=512M scope=spfile;

5
Now Start both databases to open state

To mount database use command -->alter database mount;
To open database use command -->alter database open
To shutdown database -->alter database immediate;
starup data base -->startup





5.2 on rmandb terminal
5.2.1 startup
5.2.2 sys/oracle@rmandb as sysdba

6. Create database in rmandb
6.1 create tablespace rman_tbs datafile '/u01/app/oracle/oradata/rmandb/rman_tbs01.dbf' size 100M;
6.2 create user rman1 identified by rman1  grant connect ,resource,dba to rman1
6.3 grant recovery_catalog_owner to rman1
6.4 alter user rman1 default tablespace rman_tbs
6.5 connect via user rman1
6.5.1 conn rman1/rman1@rmandb
6.5.2 select * from cat;

in new terminal
7. rman catalog rman1/rman1@rmandb
7.1  create catalog tablespace rman_tbs
Open new terminal

8 rman target sys/oracle@orcl catalog rman1/rman1@rmandb

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 28 23:01:23 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1300214037)
connected to recovery catalog database



8.2 RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

What happen before and after user Commit Fire in Oracle

The COMMIT statement ends a transaction successfully. All changes made by all SQL statements since
the transaction began are recorded permanently in the database. Before the COMMIT statement is
issued, the changes may not be visible to other transactions.
You can commit a transaction by using either of the following statements, which make the
changes permanent:
SQL> COMMIT;
3 things that happen Before Commit:
1. Oracle generates undo records in the undo segment buffers in the SGA. As you know, the
undo records contain the old values of the updated and deleted table rows.
2. Oracle generates redo log entries in the redo log buffers in the SGA.
3. Oracle modifies the database buffers in the SGA.
3 things that happen AFTER Commit:
1. The transaction tables in the redo records are tagged with the unique system change number
(SCN) of the committed transaction.
2. The log writer writes the redo log information for the transaction from the redo log buffer to
the redo log files on disk, along with the transaction’s SCN. This is the point at which a commit
is considered complete in Oracle.
3. Any locks that Oracle holds are released, and Oracle marks the transaction as complete.

How to Enable Archiving

Perform the following operation step wise to enable Archiving in Oracle 10g archiving.

1. SQL> startup 
First start oracle to check that oracle is installed or not
2. SQL> shutdown immediate
Shutdown oracle to start in mount state
3. SQL> startup mount;
 Your oracle is now in mount state
4. SQL> alter database archivelog;
Use the above command to alter database to use archivelogs
5. SQL> alter database open;
Use command # 5 to open database 

Oracle Memory Structures

My teacher often ask in every lecture.. Explain oracle memory structure and explain different SGA Buffers. Every time i was asked about different pools i mix up, So finally i decide to write them on my blog so that i can quickly refresh them.

Oracle Memory Structure

Oracle Memory Structure:
The oracle memory structure associated with Oracle instance include.
1. System Global Area (SGA)
2. Program Global Area (PGA)


1. System Global Area (SGA)
SGA Include data structures
1.1 Database buffer cahce:  Cache blocks of data retrieved from the database.
1.2 Redo log Buffer:  This buffer stores redo information (if you instance crash then recovery process can be initiated using this buffer)
1.3 Shared Pool: This pool caches various constructs that can be shared amount different DB users
1.4 Large Pool: This is an optional pool that provide large memory space to the processes that require large memory like Oracle Backup and Recovery Operations.
1.5 Java Pool: Is used for all session-specific Java Code and data within the Java Virtual Machine (JVM)
1.6 Streams Pool:  Is used by oracle streams to copy one data file to other location/files.


How to Check Memory Allocated for the SGA?
When Oracle instance is started using Enterprise Manager or SQL*Plus, the amount of memory allocated to SGA is displayed.

2. Program Global Area (PGA)
PGA is a memory region that contains data and control information for each server process. When an oracle client connect to an oracle server a PGA is allocated to that client.

How To Use FileZilla Client

FileZilla is one of the best tool available on market to perform Upload and Downloads from FTP servers

here are the steps to download and use FileZilla



1. Download FileZilla Client from website(http://filezilla-project.org)
Download related version in my case i am using Window 7 32 bit machine

2. Download and Run Installer (Install with default settings)

3. Once Installation is complete Run Filezilla and you will see screen like the one shown below




4. Its time to connect  Remote Machine





5. Click on New Site