| Prev | Chapter�6.�Maintenance | Next |
|---|
By Don Baccus with additions - by Joel Aufrecht
+
The purpose of backup is to enable recovery. Backup and
recovery are always risky; here are some steps that minimize the
chance recovery is necessary:
@@ -112,10 +112,10 @@
-rw-rw-r-x 1 service0 service0 4005995 Feb 21 18:28 /var/lib/aolserver/service0/database-backup/before_upgrade_to_4.6.dmp
[service0@localhost service0]$ exit
[root@localhost root]#
- su - service0
+ su - service0
pg_dump -f /var/lib/aolserver/service0/database-backup/before_upgrade_to_4.6.dmp openacs-dev
ls -al /var/lib/aolserver/service0/database-backup/before_upgrade_to_4.6.dmp
- exit
Back up the file system.�Back up all of the files in the service, including the + exit
Back up the file system.�Back up all of the files in the service, including the database backup file but excluding the auto-generated supervise directory, which is unneccesary and has complicated permissions.
In the tar command,
c create a @@ -156,7 +156,7 @@ [service0@yourserver aolserver]$ chown -R service0.web service0
Restore the database
Oracle.�
Set up a clean Oracle database user and - tablespace with the same names as the ones exported from (more information).
Invoke the import command
imp service0/service0 FILE=/var/lib/aolserver/service0/database-backup/nighty_backup.dmp FULL=YPostgres.�
Because of a bug in Postgres backup-recovery, database objects are not guaranteed to be created in the right order. To compensate, we pre-creating some critical items first, which leads to some harmless errors.
[root@yourserver root]# su - postgres + tablespace with the same names as the ones exported from (more information).
Invoke the import command
imp service0/service0 FILE=/var/lib/aolserver/service0/database-backup/nighty_backup.dmp FULL=YPostgres.�
Because of a bug in Postgres backup-recovery, database objects are not guaranteed to be created in the right order. To compensate, we pre-creating some critical items first, which leads to some harmless errors.
[root@yourserver root]# su - postgres
[postgres@yourserver pgsql]$ createuser service0
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
@@ -194,14 +194,14 @@
(many lines omitted)
[service0@localhost service0]$ exit
[root@localhost root]#
- su - service0
+ su - service0
cd /var/lib/aolserver/service0
cvs commit -m "last-minute commits before upgrade to 4.6"
cvs tag before_upgrade_to_4_6
- exitTo restore files from a cvs tag such as the one used above:
[root@localhost root]# su - service0 + exit
To restore files from a cvs tag such as the one used above:
[root@localhost root]# su - service0
[service0@localhost service0]$ cd /var/lib/aolserver/service0
[service0@localhost service0]$ cvs up -r current
[service0@localhost service0]$ exit
- su - service0
+ su - service0
cd /var/lib/aolserver/service0
- cvs up -r currentSome things to check first:
Did performance problems happen overnight, or did they sneak up on + you? Any clue what caused the performance problems (e.g. loading 20K + users into .LRN)
Is the file system out of space? Is the machine swapping to disk constantly?
Isolating and solving database problems.
Without daily internal maintenance, most databases slowly degrade in performance. the section called “Vacuum Postgres nightly”. For Oracle, "gather schema stats" (more info?).
You can track the exact amount of time each database query on a page takes:
Go to Main Site : Site-Wide Administration : Install Software
Click on "Install New Application" in "Install from OpenACS Repository"
Choose "ACS Developer Support">
After install is complete, restart the server.
Browse to Developer Support, which is automatically mounted at /ds. +
Turn on Database statistics
Browse directly to a slow page and click "Request Information" at the bottom of the page.
This should return a list of database queries on the page, including the exact query (so it can be cut-paste into psql or oracle) and the time each query took.
Identify a runaway Oracle query: first, use ps aux or top to get the UNIX process ID of a runaway Oracle process.
Log in to SQL*Plus as the admin:
[service0 ~]$ svrmgrl
+
+Oracle Server Manager Release 3.1.7.0.0 - Production
+
+Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
+
+Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
+With the Partitioning option
+JServer Release 8.1.7.3.0 - Production
+
+SVRMGR> connect internal
+Password:See all of the running queries, and match the UNIX PID:
select p.spid -- The UNIX PID + ,s.sid ,s.serial# + ,p.username as os_user + ,s.username ,s.status + ,p.terminal ,p.program + from v$session s ,v$process p + where p.addr = s.paddr + order by s.username ,p.spid ,s.sid ,s.serial# ;
See the SQL behind the oracle processes:
select s.username + ,s.sid ,s.serial# + ,sql.sql_text + from v$session s, v$sqltext sql + where sql.address = s.sql_address + and sql.hash_value = s.sql_hash_value + --and upper(s.username) like 'USERNAME%' + order by s.username ,s.sid ,s.serial# ,sql.piece ;
To kill a troubled process:
alter system kill session 'SID,SERIAL#'; --substitute values for SID and SERIAL#
Table of Contents