Ubuntu iTOps Tube

advertisement center

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>

 

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

 

15 Practical Usages of Mysqladmin Command For Administering MySQL Server


 

In all the 15 mysqladmin command-line examples below, tmppassword is used as the MySQL root user password. Please change this to your MySQL root password.

 

1. How to change the MySQL root user password?

# mysqladmin -u root -ptmppassword password 'newpassword'
 
# mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

 

2. How to check whether MySQL Server is up and running?

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

 

3. How do I find out what version of MySQL I am running?

Apart from giving the ‘Server version’, this command also displays the current status of the mysql server.

# mysqladmin -u root -ptmppassword version
mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
 
Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 107 days 6 hours 11 min 44 sec
 
Threads: 1  Questions: 231976  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

 

4. What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1  Questions: 231977  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

The status command displays the following information:

§  Uptime: Uptime of the mysql server in seconds

§  Threads: Total number of clients connected to the server.

§  Questions: Total number of queries the server has executed since the startup.

§  Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.

§  Opens: Total number of tables opened by the server.

§  Flush tables: How many times the tables were flushed.

§  Open tables: Total number of open tables in the database.

 

5. How to view all the MySQL Server status variable and it’s current value?

# mysqladmin -u root -ptmppassword extended-status
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Aborted_clients                   | 579       |
| Aborted_connects                  | 8         |
| Binlog_cache_disk_use             | 0         |
| Binlog_cache_use                  | 0         |
| Bytes_received                    | 41387238  |
| Bytes_sent                        | 308401407 |
| Com_admin_commands                | 3524      |
| Com_assign_to_keycache            | 0         |
| Com_alter_db                      | 0         |
| Com_alter_db_upgrade              | 0         |

 

6. How to display all MySQL server system variables and the values?

# mysqladmin  -u root -ptmppassword variables
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| auto_increment_increment        | 1                               |
| basedir                         | /                               |
| big_tables                      | OFF                             |
| binlog_format                   | MIXED                           |
| bulk_insert_buffer_size         | 8388608                         |
| character_set_client            | latin1                          |
| character_set_database          | latin1                          |
| character_set_filesystem        | binary                          |
 
skip.....
 
| time_format                     | %H:%i:%s                        |
| time_zone                       | SYSTEM                          |
| timed_mutexes                   | OFF                             |
| tmpdir                          | /tmp                            |
| tx_isolation                    | REPEATABLE-READ                 |
| unique_checks                   | ON                              |
| updatable_views_with_limit      | YES                             |
| version                         | 5.1.25-rc-community             |
| version_comment                 | MySQL Community Server (GPL)    |
| version_compile_machine         | i686                            |
| version_compile_os              | redhat-linux-gnu                |
| wait_timeout                    | 28800                           |
+---------------------------------+---------------------------------+

 

7. How to display all the running process/queries in the mysql database?

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below.

# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
 
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

 

8. How to create a MySQL Database?

# mysqladmin -u root -ptmppassword create testdb
 
# mysql -u root -ptmppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 705
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)



Note: To display all tables in a database, total number of columns, row, column types, indexes etc., use the mysqlshow command that we discussed in our previous articles.

 

9. How to Delete/Drop an existing MySQL database?

# mysqladmin -u root -ptmppassword drop testdb
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
 
Do you really want to drop the 'testdb' database [y/N] y
Database "testdb" dropped
 
# mysql -u root -ptmppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 707
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
+--------------------+
3 rows in set (0.00 sec)

 

10. How to reload/refresh the privilege or the grants tables?

# mysqladmin -u root -ptmppassword reload;

Refresh command will flush all the tables and close/open log files.

# mysqladmin -u root -ptmppassword refresh

 

11. What is the safe method to shutdown the MySQL server?

# mysqladmin -u root -ptmppassword shutdown

 

# mysql -u root -ptmppassword

ERROR 2002 (HY000): Can't connect to local MySQL server

through socket '/var/lib/mysql/mysql.sock'

Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To start the server, execute “/etc/rc.d/init.d/mysql start”

 

12. List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts

# mysqladmin -u root -ptmppassword flush-logs

# mysqladmin -u root -ptmppassword flush-privileges

# mysqladmin -u root -ptmppassword flush-status

# mysqladmin -u root -ptmppassword flush-tables

# mysqladmin -u root -ptmppassword flush-threads

§  flush-hosts: Flush all information in the host cache.

§  flush-privileges: Reload the grant tables (same as reload).

§  flush-status: Clear status variables.

§  flush-threads: Flush the thread cache.

 

13. How to kill a hanging MySQL Client Process?

First identify the hanging MySQL client process using the processlist command.

# mysqladmin -u root -ptmppassword processlist

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

| Id | User | Host      | db | Command | Time | State | Info             |

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

| 20 | root | localhost |    | Sleep   | 64   |       |                  |

| 24 | root | localhost |    | Query   | 0    |       | show processlist |

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

Now, use the kill command and pass the process_id as shown below. To kill multiple process you can pass comma separated process id’s.

# mysqladmin -u root -ptmppassword kill 20

 

# mysqladmin -u root -ptmppassword processlist

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

| Id | User | Host      | db | Command | Time | State | Info             |

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

| 26 | root | localhost |    | Query   | 0    |       | show processlist |

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

 

14. How to start and stop MySQL replication on a slave server?

# mysqladmin  -u root -ptmppassword stop-slave

Slave stopped

 

# mysqladmin  -u root -ptmppassword start-slave

mysqladmin: Error starting slave: The server is not configured as slave;

fix in config file or with CHANGE MASTER TO

 

15. How to combine multiple mysqladmin commands together?

In the example below, you can combine process-list, status and version command to get all the output together as shown below.

# mysqladmin  -u root -ptmppassword process status version

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

| Id | User | Host      | db | Command | Time | State | Info             |

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

| 43 | root | localhost |    | Query   | 0    |       | show processlist |

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

 

Uptime: 3135

Threads: 1  Questions: 80  Slow queries: 0  Opens: 15  Flush tables: 3

Open tables: 0  Queries per second avg: 0.25

 

mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686

Copyright (C) 2000-2006 MySQL AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

 

Server version          5.1.25-rc-community

Protocol version        10

Connection              Localhost via UNIX socket

UNIX socket             /var/lib/mysql/mysql.sock

Uptime:                 52 min 15 sec

You can also use the short form as shown below:

# mysqladmin  -u root -ptmppassword pro stat ver

Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# mysqladmin  -h 192.168.1.112 -u root -ptmppassword pro stat ver