Oracle database backup and restore

Here is command to take backup from the oracle database:

exp userid=system/{system_passwd}@{schema} owner={schema_owner} file={backup_file}.dmp buffer=102400 statistics=none grants=n log={backup_log_file}.log

Where

system_passwd is password for system user

schema is database schema name

schema_owner is user name for the database

backup_file is backup file name

backup_log_file is log file name for backup logs

To restore database from the backup (dmp file) use following command

imp system/{system_passwd}@{schema} file={backup_file}.dmp log={import_log_file}.log fromuser={existing_user} touser={new_user}


Where
system_passwd is password for system user

schema is database schema name

backup_file is backup file name

import_log_file is log file name where all the logs will be written

existing_user is database user name from the dmp file

new_user is database user name which is being restored.

Creating DBLink in Oracle

Login to database execute below command:

create public database link {link_name} connect to {user_name} identified by using '{connect_descriptor}'

Where

1. link_name is the name of the database link

2. user_name is the username of the database to get connected

3. connect_descriptor is the tns entry ($OARCLE_HOME/network/admin/tnsname.ora)

ORA-06550: line 1, column 91: PLS-00201: identifier 'NameFromLastDDL' must be declared

You may face below error while executing loadjava command:

ORA-06550: line 1, column 91: PLS-00201: identifier 'NameFromLastDDL' must be declared

This is majorly because of java is not enabled.

So, Check Java Enabled on Database.

SQL> select owner, object_name, object_type from all_objects where object_name like '%NameFromLastDDL%' ;
no rows selected
SQL> select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;
no rows selected

and then install java on exist database by initjvm.sql script.
http://download.oracle.com/docs/cd/B19306_01/install.102/e10319/java.htm#DFSIG276http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/scripts005.htm#sthref2935
run $ORACLE_HOME/javavm/install/initjvm.sql script.

SQL>@?/javavm/install/initjvm.sql
.
.
.

SQL> select owner, object_name, object_type from all_objects where object_name like '%NameFromLastDDL%' ;
OWNER OBJECT_NAME OBJECT_TYPE--------------- ------------------------------ -------------------SYS NameFromLastDDL FUNCTIONPUBLIC NameFromLastDDL SYNONYM
SQL> select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;
COMP_NAME STATUS------------------------------------------------JServer JAVA Virtual Machine VALID

Reference: http://www.ora600.be/node/4628