Unix iTOps Tube

Thursday, February 23, 2012

Oracle Database Startup and Shutdown Procedure





For a DBA, starting up and shutting down of oracle database is a routine and basic operation. Sometimes Linux administrator or programmer may end-up doing some basic DBA operations on development database. So, it is important for non-DBAs to understand some basic database administration activities.

In this article, let us review how to start and stop an oracle database.


How To Startup Oracle Database

 

1. Login to the system with oracle username

Typical oracle installation will have oracle as username and dba as group. On Linux, do su to oracle as shown below.

$ su - oracle

 

2. Connect to oracle sysdba

Make sure ORACLE_SID and ORACLE_HOME are set properly as shown below.

$ env | grep ORA
ORACLE_SID=DEVDB
ORACLE_HOME=/u01/app/oracle/product/10.2.0


You can connect using either “/ as sysdba” or an oracle account that has DBA privilege.

 

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:28 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL>

 

3. Start Oracle Database

The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify PFILE.

Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.

1.    spfile$ORACLE_SID.ora

2.    spfile.ora

3.    init$ORACLE_SID.ora


Type “startup” at the SQL command prompt to startup the database as shown below.

SQL> startup
ORACLE instance started.
 
Total System Global Area  812529152 bytes
Fixed Size                  2264280 bytes
Variable Size             960781800 bytes
Database Buffers           54654432 bytes
Redo Buffers                3498640 bytes
Database mounted.
Database opened.
SQL>


If you want to startup Oracle with PFILE, pass it as a parameter as shown below.

SQL> STARTUP PFILE=/u01/app/oracle/product/10.2.0/dbs/init.ora

 

How To Shutdown Oracle Database

Following three methods are available to shutdown the oracle database:

1.    Normal Shutdown

2.    Shutdown Immediate

3.    Shutdown Abort

 

1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

2. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

3. Shutdown Abort

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:33 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to an idle instance.
 
SQL> shutdown abort
ORACLE instance shut down.
SQL>

 

Overview Of PoE - Power Over Ethernet Concepts and Devices List


 


1. What is PoE – Power Over Ethernet?

Power over Ethernet (PoE) does exactly what it says — it carries power over ethernet cables. So, an ethernet cables caries both data and current together to operate devices like wi-fi access points. This is very helpful to install wi-fi access points in ceilings, where it is hard to find power outlets. Only smaller devices can be powered using the PoE, as it carries limited power over the ethernet.


Fig 1: A Typical PoE Setup (Image From: PowerOverEthernet.com)

 

2. How does PoE works?

Following are the terminologies used in the PoE network:

§  PD – Powered Devices: PDs are end devices that can accept the power transmitted over Ethernet Cat-5 cable.

§  PSE – Power Source Equipment: PSEs add power to the ethernet cable.

§  Midspan device: These are power injectors that stands between the standard ethernet switch and the end device.

§  Endspan devices (PoE Switches): These are Ethernet Switches that includes the circuit to inject power to the ethernet cable.


Cat5 ethernet cable has 4 twisted pairs. Out of 4 pairs:

§  2 pairs are used for data transfer,

§  2 pairs are spare and not used.


There are two approaches to transfer power over ethernet.

Approach 1 uses data pairs:

The same 2 pairs used for data transfer, is used to carry power. The PSE injects power to the 2 data pairs (pin 1,2 and pin 3,6) via the center tap of the transformers without affecting the data transfer.


Fig 2: PoE Approach 1 uses data pairs to send power (Image From: PowerOverEthernet.com)

Approach 2 uses the spare pairs:

The 2 spare pairs that are not used, is used to carry power. It applies +ve voltage to pins 4 and 5 of ethernet cable. It applies -ve voltage to pins 7 and 8.


Fig 3: PoE Approach 2 uses spare pairs to send power (Image From: PowerOverEthernet.com)

 

3. What are the Benefits of PoE?

§  Portability: Install end-devices where it is hard to get power.  For example, you can install a wireless access point in the ceiling, where you cannot find power outlet.

§  Cost Savings: For a large scale deployment of wireless access point, you don’t need to install power outlet for the access points, which can be a huge cost saving.

§  Simplicity: You need to run only ethernet cable to the end devices, which eliminates the cable clutter.

§  Safer: No AC power is involved, as you don’t need to install A/C power outlet for the end-devices.

§  Easy Maintenance: You can restart the end-device remotely. No need to press the power button to reset the equipment.  You can easily move the end-devices wherever you can lay a LAN cable.

 

4. Sample List of Equipments that uses PoE.

PoE Adapters:

§  D-Link Power over Ethernet (PoE) Adapter – DWL-P50

§  D-Link Power Over Ethernet Adapter – DWL-P200

§  Linksys Power Over Ethernet Adapter Kit

PoE Wireless Access Point:

§  D-Link PoE Wireless Access Point – DWL-2200AP

§  D-Link PoE Outdoor Wireless Access Point – DWL-7700AP

PoE Switch:

§  Netgear 24 Port Switch with 2 Gigabit Ports and 12 Port POE – FS726TP ProSafe

PoE Network Surveillance Camera:

§  Airlink Power over Ethernet Network Camera – SkyIPCam310

§  Panasonic 21x Optical Zoom Pan/Tilt Network Camera – BB-HCM580A

§  Mobotix Outdoor, Wide Angle Lens, Motion Detection Camera D22M-IT

PoE VoIP Phone:

§  Polycom IP Phone with Integrated PoE – IP550


PowerOverEthernet.com site maintains a Huge List of all PoE products, including a PoE Analog Clocks and PoE Gas Detector.

Additional PoE References

§  PoE White Paper from PowerOverEthernet.com

§  PoE on Wikipedia

 

How To Backup and Restore PostgreSQL Database Using pg_dump and psql


 

 
pg_dump is an effective tool to backup postgres database. It creates a *.sql file with CREATE TABLE, ALTER TABLE, and COPY SQL statements of source database. To restore these dumps psql command is enough.

Using pg_dump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use pg_dump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore postgres database using pg_dump and psql:

Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

 

Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

How To Backup Postgres Database

1. Backup a single postgres database

This example will backup erp database that belongs to user geekstuff, to the file mydb.sql

$ pg_dump -U geekstuff erp -f mydb.sql


It prompts for password, after authentication mydb.sql got created with create table, alter table and copy commands for all the tables in the erp database. Following is a partial output of mydb.sql showing the dump information of employee_details table.

--

-- Name: employee_details; Type: TABLE; Schema: public; Owner: geekstuff; Tablespace:

--

 

CREATE TABLE employee_details (

employee_name character varying(100),

emp_id integer NOT NULL,

designation character varying(50),

comments text

);

 

ALTER TABLE public.employee_details OWNER TO geekstuff;

 

--

-- Data for Name: employee_details; Type: TABLE DATA; Schema: public; Owner: geekstuff

--

COPY employee_details (employee_name, emp_id, designation, comments) FROM stdin;

geekstuff 1001 trainer

ramesh 1002 author

sathiya 1003 reader

\.

--

-- Name: employee_details_pkey; Type: CONSTRAINT; Schema: public; Owner: geekstuff; Tablespace:

--

ALTER TABLE ONLY employee_details

 

ADD CONSTRAINT employee_details_pkey PRIMARY KEY (emp_id);

2. Backup all postgres databases

To backup all databases, list out all the available databases as shown below.

Login as postgres / psql user:

$ su postgres

List the databases:

$ psql -l

 

List of databases

Name | Owner | Encoding

-----------+-----------+----------

article | sathiya | UTF8

backup | postgres | UTF8

erp | geekstuff | UTF8

geeker | sathiya | UTF8

Backup all postgres databases using pg_dumpall:

You can backup all the databases using pg_dumpall command.

$ pg_dumpall > all.sql

Verify the backup:

Verify whether all the databases are backed up,

$ grep "^[\]connect" all.sql

\connect article

\connect backup

\connect erp

\connect geeker

3. Backup a specific postgres table

$ pg_dump --table products -U geekstuff article -f onlytable.sql

To backup a specific table, use the –table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the –schema SCHEMANAME option.

 

How To Restore Postgres Database

1. Restore a postgres database

$ psql -U erp -d erp_devel -f mydb.sql

This restores the dumped database to the erp_devel database.

Restore error messages

While restoring, there may be following errors and warning, which can be ignored.

psql:mydb.sql:13: ERROR:  must be owner of schema public
psql:mydb.sql:34: ERROR:  must be member of role "geekstuff"
psql:mydb.sql:59: WARNING:  no privileges could be revoked
psql:mydb.sql:60: WARNING:  no privileges could be revoked
psql:mydb.sql:61: WARNING:  no privileges were granted
psql:mydb.sql:62: WARNING:  no privileges were granted

2. Backup a local postgres database and restore to remote server using single command:

$ pg_dump dbname | psql -h hostname dbname

The above dumps the local database, and extracts it at the given hostname.

3. Restore all the postgres databases

$ su postgres
$ psql -f alldb.sql

4. Restore a single postgres table

The following psql command installs the product table in the geek stuff database.

$ psql -f producttable.sql geekstuff

 

Vi and Vim Macro Tutorial: How To Record and Play


Using Vim Macro feature you can record and play a sequence of actions inside the editor.

This article explains how to perform record and play inside Vi and Vim editor using two detailed examples.


High Level Steps to Record and Play inside Vim

1.    Start recording by pressing q, followed by a lower case character to name the macro

2.    Perform any typical editing, actions inside Vim editor, which will be recorded

3.    Stop recording by pressing q

4.    Play the recorded macro by pressing @ followed by the macro name

5.    To repeat macros multiple times, press : NN @ macro name. NN is a number

 

Example 1: Sequence number generation inside a file using Vim Macro

1. Start a sequence-test.txt file to generate the sequence.

$ vim sequence-test.txt

2. Go to insert mode and type “1 .” as shown below

Type: Esc i followed by 1.

$ vim sequence-test.txt

1.

3. Start the Recording and store it in register a.

Type: Esc q followed by a

§  q indicates to start the recording

§  a indicates to store the recordings in register a

§  When you do q a, it will display “recording” at the bottom of the vi.

4. Copy the 1st line to 2nd line to have two lines with 1 . as shown below

Type: Esc yy followed by p

§  yy will copy the current line

§  p will paste the line that was just copied

 

$ vim sequence-test.txt

1.

1.

Note: Vim will still show recording at the bottom as shown below.

 

 


Fig: Vim showing recording message at the bottom

5. Increment the number.

Type: Control a

By placing the cursor at the 2nd line, press Ctrl+a which increment the number to 2. as shown below.

$ vim sequence-test.txt

1.

2.

Note: vim will still show recording at the bottom.

 

 

6. Stop the recording

Type: q

Press q to stop the recording. You’ll notice that recording message at the bottom of the vim is now gone.

 

7. Repeat the recording 98 times.

Type: 98@a

§  Now repeat this job, by typing 98 @ a

§  @a repeats the macro “a” one time.

§  98@a repeats the macros “a” 98 times generating the sequence number 1 – 100 as shown below using macros.

 


Fig: Generate Sequence Number in Vim using Macro

 

Example 2: Repeat Vim Macro with different arguments

This example explains how you can executing the same command, with different input for it. i.e Framing the same command, with different arguments.

 

Before Executing the Macro: change-password.sql

$ vim change-password.sql

Annette

Warren

Anthony

Preston

Kelly

Taylor

Stiller

Dennis

Schwartz

 

After Recording and executing the Macro: change-password.sql

$ vim change-password.sql

ALTER USER Annette IDENTIFIED BY 'Annette';

ALTER USER Warren IDENTIFIED BY 'Warren';

ALTER USER Anthony IDENTIFIED BY 'Anthony ';

ALTER USER Preston IDENTIFIED BY 'Preston';

ALTER USER Kelly IDENTIFIED BY 'Kelly ';

ALTER USER Taylor IDENTIFIED BY 'Taylor';

ALTER USER Stiller IDENTIFIED BY 'Stiller';

ALTER USER Dennis IDENTIFIED BY 'Dennis';

ALTER USER Schwart IDENTIFIED BY 'Schwart';

 

1. Open the change-password.sql that has only the names.

$ vim change-password.sql

Annette

Warren

Anthony

Preston

Kelly

Taylor

Stiller

Dennis

Schwartz

 

2. Start the Recording and store it in register a

Type: q a

§  q indicates to start the recording

§  a indicates to store the recordings in register a

§  When you do q a, it will display the message recording at the bottom of the vi.

 

3. Go to Insert Mode and Type ALTER USER

Type: I (Upper case i) followed by “ALTER USER ”

Place the cursor anywhere in the first line, and then press I. Which will take you to the first character of the line. Type ALTER USER

 

4. Copy the Next Word (i.e the name)

Type: Esc w yw

§  Press Esc, and then press w to go to the next word ( name ).

§  yw, copies the current word ( name ).

 

5.Go to the end and type IDENTIFIED BY ‘

Type: Esc A followed by ” IDENTIFIED BY ‘”

§  Press Esc, and A to move the cursor to the end of the line, and then type space.

§  Type IDENTIFIED BY ‘

 

6. Paste the copied Name

 

Type: Esc p

Press Esc, and then type p to paste the name that was copied in the step #4.

 

7. Complete the quote at the end.

Type: Esc A followed by ‘;

Press Esc, and A to go to the end of the line, and ‘;

 

8. Jump to the next line and stop the record.

Type: Esc j followed by q

§  j to move to the next line.

§  q to stop the recording

Note: The recording message shown in the bottom of the vi will now disappear. At this stage, the change-password.sql will look like the following.

 


Fig: Vim Macro completed the recording

 

9. Repete the Macro with the arguments in the corresponding line

Type: 8 @ a

§  Now repeat this job 8 times by typing 8@a

§  @a repeats the macro “a” one time.

§  8@a repeats the macros “a” 8 times completing the rest of the line automatically as shown below

§   


Fig: Vim Macro Play completed