Restoring SQL database from a backup file

Restoring a database require skills. You can use commandline or GUI tool to restore a database. Please take note of this:

1. You can restore a database from a backup file (.bak file);
2. You can restore a database from a backup file to a fresh database (database will be created during the process); and
3. You can use database restore from backup as a Data Migration (DM) or Disaster Recovery (DR) plan.

Syntax for restore:

--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var } 
 [ FROM <backup_device> [ ,...n ] ]
 [ WITH 
   {
    [ RECOVERY | NORECOVERY | STANDBY = 
        {standby_file_name | @standby_file_name_var } 
       ]
   | ,  <general_WITH_options> [ ,...n ]
   | , <replication_WITH_option>
   | , <change_data_capture_WITH_option>
   | , <FILESTREAM_WITH_option>
   | , <service_broker_WITH options> 
   | , <point_in_time_WITH_options—RESTORE_DATABASE> 
   } [ ,...n ]
 ]
[;]

Example of restore situation:

Using comandline, type the following to restore a database from a backup file:

RESTORE DATABASE TestPatient FROM DISK = 'C:\Testbackup.BAK'
GO

Using the GUI tool is the easiest, but you have to be careful of any option you're choosing.

Step 1: Right click on "Database" menu and select "Restore Database". 
A new dialogue box will appear with various options. This wizard will guide you through the whole database restore process.
NOTE: Make sure your backup file (.bak file) has been copied to the server and you have the required privilege to open the file or write on the file location.

Step 2: This is where you specify the database name. 
If you want to create a new database for this restore process, type in the database name.

Step 3: Source
Under the "Specify source" option, choose "From device" from the radio button and click on the menu on the right corner to expand your options.

Step 4: Add file
Click on "Add" to point the location of your file

Step 5: Choose file
From the box, choose the backup file. The file usually will have a ".bak" extension. Ensure the the correct file is selected, otherwise the restore will fail.
Click "OK" to continue

Step 6: Select file
Once the file the has been selected, click "OK" to move to the next step

Step 7: Restore
At this point, check the "Restore" box to indicate the exact file the database will be restored from, and then click "OK"

Step 8: Run process
Once you click "OK", the restore process runs. You can monitor the restore process through the "Completion" rate on the left tab. It will display in percentage the rate of completion.

Step 9: Done
Once the restore is completely done, you will get a message like this: "The restore of database XXX completed successfully".

Step 10: Verify
To verify that the restore was successfully completed, click on Databases and look from the newly created database in the drop-down.

Thanks for reading!

EXP-00091 Exporting questionable statistics

Problem:

We were trying to upgrade from 10g to 11g. After running an export of the schemas from the 10g database, we installed Oracle 11g and tried to import the schema back into the new database (11g). Then we ran into error EXP-00091 Exporting questionable statistics.

Cause:

The "EXP-00091 Exporting questionable statistics" error occurs when a DBA tries to export table data with its related optimizer statistics, and Oracle cannot verify the currency of these statistics. Please note that Oracle has to verify statistics whenever it does an export; otherwise this error will be thrown.
DBMS_STATS controls this process. You can always skip and let Oracle handle the statistics after the data has been imported into the new database. This process is known as statistics recalculation.

Solution:

Run 'exp' with "statistics=none" option to skip the statistics collection and bypass the error. For instance:

exp scott/tiger file=scott.imp log=scott.log statistics=none

This will create a dumpfile with the name "scott.imp" and the statistics will be ignored.

 

Datapump in Oracle 11g

Datapump import/export are used for data migration. You can export TABLES, SCHEMA, TABLESPACE or DATABASE and restore it on another server.

Quickfacts:

1. Datapump produces a dumpfile with extention .dmp or .imp
2. Privilege 'exp_full_database' is required to export full database
3. Privilege 'imp_full_database' is required to impport full database
4. In datapump, you can user a parfile to store your commands

Getting the help page

expdp help=y

Keyword                                  Description
------------------------------------------------------------------------------
DIRECTORY                          Directory object to be used for dumpfiles and logfiles.
DUMPFILE                             List of destination dump files (expdat.dmp),
                                                 e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
EXCLUDE                              Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FULL                                       Export entire database (N).
HELP                                       Display Help messages (N).
INCLUDE                                Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME                            Name of export job to create.
LOGFILE                                 Log file name (export.log).
NETWORK_LINK                  Name of remote database link to the source system.
NOLOGFILE                           Do not write logfile (N).
PARALLEL                             Change the number of active workers for current job.
PARFILE                                 Specify parameter file.
SCHEMAS                              List of schemas to export (login schema).
TABLES                                  Identifies a list of tables to export - one schema only.
TABLESPACES                     Identifies a list of tablespaces to export.
VERSION                              Version of objects to export where valid keywords are:
                                                (COMPATIBLE), LATEST, or any valid database version.
EXPORT

Example 1: Joe is a user on a database, Joe needs to export the whole database.
Solution:
c:\> sqlplus sys/**** as sysdba
     grant exp_full_database to joe;
     commit;
     exit;
c:\> expdp joe/**** full=y dumpfile=joe_database.dmp logfile=joe.log compression=n

^full database is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'

Example 2: System needs to export Joe's schema with compression
Solution:
c:\> expdp system/**** full=n schemas=joe dumpfile=joe_schema.dmp logfile=joe1.log compression=y

^Joe's schema is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'
^Dumpfile will be compressed

Example 3: Joe needs to export 2 tables (Student, Lecturer) to a location on disc ('c:\oracle')
Solution:
c:\> sqlplus joe/****
     create directory oracle as 'c:\oracle';
     commit;
     exit;
c:\> expdp joe/**** full=n tables=Student,Lecturer directory=oracle dumpfile=joe_tables.dmp logfile=joe2.log

^Joe's table are exported
^Dumpfile will be created in 'c:\oracle'
^There should be no space between the tables...e.g tables=(student1,student2,student3)

IMPORT

Example 4: Joe needs to import 2 tables (Student, Lecturer) from a directory called 'oracle'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp tables=Student,Lecturer logfile=joe3.log

^Joe's table will be imported

Example 5: Joe needs to import 2 tables (Student, Lecturer) and change the tables name to 'student1' and 'student2'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp logfile=joe3.log remap_table=joe.Student:student1,joe.Lecturer:student2

^Joe's table will be imported and renamed
^Tables must be separated by comma, the colon (:) must separate the oldname and new name

Example 6: System needs to import the whole database from a dumpfile on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=y dumpfile=database.dmp logfile=db.log

Example 7: System needs to import joe's schema from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db1.log

^Joe's schema will be imported


Example 8: System needs to import joe's schema and change name to DANIEL and also change the tablespace to daniel's tablespace
from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n dumpfile=database.dmp logfile=db2.log remap_schema=joe:daniel remap_tablespace=users:daniel_tbs

^Joe's schema will be imported and changed to Daniel
^Tablespace will be changed from users to Daniel_tbs
^you MUST physically create this tablespace and grant access to Daniel.

Example 9: System needs to import joe's schema using a parameter file
Solution:

create a parfile called 'joe.txt'

USERNAME=system/****
FULL=N
SCHEMA=(joe,daniel,class)
DIRECTORY=oracle
DUMPFILE=database.dmp
LOGFILE=db4.log

Save file and exit

c:\> impdp parfile=joe.txt

Parfile makes it more convenient to adjust datapump commands. It also help when you need to constantly run same commands.

Example 10: System needs to import joe's schema from dumpfile and replace all tables
Solution:
c:\> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db6.log table_exists_action=replace

^Joe's schema will be imported
^Joe's tables will be replaced if they already exist on the database prior to import