PostgreSQL - Dump and Restore

#pg_dump -Fc imlu > imlu.sql

1. My Dump - explicit column names
#pg_dump -c -D -v sacco>dump.sacco.sql
-c drops db objects b4 recreating them
-C starts with command to create the db and reconnect to it
-D dump with explicit column names (slow)

2. Simplest dump
# pg_dump -d sacco > simplestdump.sacco.sql
-d Dump data as INSERT commands

3. single table dump
#pg_dump -d -t periods sacco >dump.periods.sacco.sql

RESTORE
#createdb sacco (after dropping) - or createdb -T template0 sacco
#psql -f dump.sacco.sql sacco - or psql dbname < infile

23.1.2. Using pg_dumpall
The above mechanism is cumbersome and inappropriate when backing up an entire database cluster. For this reason the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as users and groups. The basic usage of this command is:

pg_dumpall > outfile

The resulting dump can be restored with psql:
psql -f infile postgres

(Actually, you can specify any existing database name to start from, but if you are reloading in an empty cluster then postgres should generally be used.) It is always necessary to have database superuser access when restoring a pg_dumpall dump, as that is required to restore the user and group information.

List All Databases
\l
Alternatively, you can list the databases from the command line using:
psql -l

Oracle 10g - Configure Email

UTL Mail Package for sending mail

1. Install the package
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

Package Paths my be something like this:
i)On live db > @/global/oracle/app/product/10.2.0/db_1/rdbms/admin/utlmail.sql
ii)On local db > @> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/utlmail.sql

2. GRANT
GRANT execute ON utl_mail TO ;
(or)
GRANT execute ON utl_mail TO PUBLIC;

3. Configure smtp server
> alter system set smtp_out_server = '172.100.3.45:25' scope=both;

I prefer setting this inside the procedure/function sending the mail


4. Restart the DBMS
>shutdown immediate
>startup


SENDING EMAIL

Showing only the relevant part of the function :

IF(email = '1')THEN

--EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '172.100.3.98:25';
UTL_MAIL.send(sender => 'imisadmin@cit.go.ke', --to confirm that this email is from imis
recipients => recepts, --client email address(es)
cc => rec_user.email, --carbon copy to relevant officer
--bcc => 'iitambo@dewcis.com', --bind cc to developer
subject => sub,
message => msg,
mime_type => 'text; charset=us-ascii');
END IF;

Oracle 10g - Command Line

NB: Linux Server Assummed

1. Manual Startup

#xhost + //disables access control
#su - oracle //switch to user oracle (execute home folder)
#export ORACLE_SID=crm //for crm SID
#sqlplus / as sysdba //connect to sqlplus as sysdba
SQL>startup //start up the db
SQL>quit //
#lsnrctl start lsnrcrm //start listener
#emctl start dbconsole //start Enterprise Manager

NB: shutting down = shutdown immediate (from withing sqlplus)

2. Related (run as user oracle)

#dbca //db config assistant
db type: General Purpose
Global Db Name : eg dump.fsm
SID : eg dumpfsm

Option - > Generate DB Creation Scripts (Default location: /home/oracle/oracle/admin/dump/scripts)

#netca //configure listener