| Agile Product Lifecycle Management Database Installation and Management Guide Release 9.3.6 E71162-01 |
|
 Previous |
 Next |
| Agile Product Lifecycle Management Database Installation and Management Guide Release 9.3.6 E71162-01 |
|
Previous |
Next |
It is important to protect your Agile data and system files from loss. This section describes basic backup and recovery strategies and gives specific information about applying them to your Agile system used with Oracle products.
The instructions in this section are for system and database administrators who need to manage the Agile database.
This section provides database maintenance procedures.
As part of monthly maintenance activities, Oracle recommends the following:
Rebuilding domain indexes to improve performance.
Running Averify to check database integrity and tablespace free space.
Checking the tablespace data files in your Oracle database on a monthly basis can help determine how close your database storage is to maximum capacity. If any or all of the tablespaces are at least 90 percent, you should increase the disk space allocation for the specific tablespaces. One method for accomplishing this is to increase the data file size for the corresponding tablespaces, as follows:
To increase the disk space for Oracle databases:
Check the disk space for the hard drive where the Oracle database is located.
If there is less than 500 MB available, it is recommended that you increase disk space by adding to or replacing the hard drive.
To check the tablespace data files, start the Enterprise Manager Console. The Oracle Enterprise Manager Login dialog box appears.
Select Launch standalone and click OK.
Double-click the Databases folder and the name of the database.
Log in with the following credentials, and then click OK:
Username:system
Password:manager
Service:host name (remote) or blank (local)
Connect As: normal
|
Note: Type the fully qualified host name of the computer in the Service field (or Host String in some cases) if you are not logging in on the same computer where you have installed Oracle or if you receive a TNS error message. |
Under Storage > Tablespaces, select System, Temporary, Agile_DATA1-5, and Agile_INDX1-5. Verify disk space that each tablespace is using exceeds 90 percent. Make sure Agile_INDX4 has enough space for file content index synchronization.
|
Note: If you need to increase the available disk space, double-click the value in the Size field. |
When the Edit Datafile dialog box appears, increase the amount of available disk space, and then click OK.
|
Note: If possible, double the current disk space. If additional disk space is not available on the hard drive, consider upgrading your hardware.This is a preventive and proactive measure, but is not required. All Agile tablespaces automatically extend by 10MB whenever additional disk space is needed and available. |
Database losses are costly and harmful, but they can and do occur. They can result from hardware failures, natural disasters, fire, power surges, and problems with administration and configuration. Whatever the cause, your best protection against business disruption and permanent data loss is an effective backup and recovery plan, applied automatically as much as possible. To this end, Oracle recommends instituting a scheduled backup of all file systems on all servers.
This chapter introduces several methods to back up and recover data. You will need additional information to adequately administer and protect your databases. It is recommended that you do a cost/benefits analysis to determine how often to back up critical data and to justify the labor, hardware, software, and storage costs involved. The following documents provide helpful information:
Oracle Database Backup and Recovery Basics (see Oracle Database Backup and Recovery Basics - http://www.oracle.com/pls/db111/portal.portal_db?selected=4&frame=#backup_and_recovery)
Oracle Database Recovery Manager User's Guide (see Oracle Database Backup and Recovery Basics - http://www.oracle.com/pls/db111/portal.portal_db?selected=4&frame=#backup_and_recovery)
Oracle Database Concepts (see Oracle Database Concepts - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/toc.htm)
|
Note: The documents are available on the Oracle Technology Network (OTN) Web siteat: http://www.oracle.com/technetwork/documentation/agile-085940.html. |
You must be a member of the Oracle Technology Network (OTN) to have access to the site (becoming a member requires that you register at the site to gain access).
When you are planning a backup and recovery strategy, you must consider the following factors:
Database availability
What is the database availability requirement for business operations? Is it required for 7X24X365 availability or only during standard business hours?
According to the availability requirement, different database backup methods can be adopted. If the database cannot be shut down, a hot online backup is the only choice.
ARCHIVELOG and NOARCHIVELOG mode
A database can run in ARCHIVELOG or NOARCHIVELOG mode. It is a best practice is to have your PRODUCTION database in archive log mode. DEVELOPMENT and TEST environments need not be in archive log mode since they can be refreshed using PRODUCTION.
When the database is in operation, all database changes are recorded in redo log files. If the database is running in ARCHIVELOG mode, these redo log files are archived in the database archive log destination and are referred to as archived redo log files. A database running in ARCHIVELOG mode provides better protection from data loss. It can be recovered up to the point of failure. To perform a hot backup, a database must be running in ARCHIVELOG mode.
The default configuration for the Agile database is NOARCHIVELOG mode. You can change the database to ARCHIVELOG mode following the instructions available in the initagile9.ora file. It is recommended that an Oracle DBA or Oracle support be available.
Data loss tolerance
How much data can you afford to lose due to a database malfunction?
Can you afford to lose one day or one week's worth of data if a database malfunctions? Can you reenter user data if there is a database failure?
If your database cannot tolerate data loss due to failure, then a good data protection backup method must be adopted, such as hot or cold backup using ARCHIVELOG mode.
Recovery time
How much time can you afford to spend recovering from a database malfunction?
Different backup methods have different recovery times. Physical methods for backup and recovery are much faster than logical backups, and backups to disk are much faster than to tape. Recovery is also much faster from disk than from tape.
Technical skills
What are the technical skills of your database or systems administrator?
Some backup methods require more database knowledge than others. Standby databases require more technical skill than cold or hot backup.
Hardware or software investment
How much hardware or software investment do you want to put into the system?
Some advanced features, such as high availability, require more of an investment in hardware and software.
You can determine the safest backup method for your environment based on database requirements, database running mode, and your recovery scenario (described in the following table). However, the final decisions about the backup and recovery strategy you use are beyond the scope of this chapter. For detailed information to help you make these decisions, see the books listed on the previous page.
| Scenario | Backup methods |
|---|---|
| The database requires 7X24 uptime and cannot be shut down | Hot backup Export Database must be running in ARCHIVELOG mode |
| The database is available during regular business hours and can be shutdown | Hot backup Cold backup Export Database can running in NOARCHIVELOG or ARCHIVELOG mode |
| To recover up to the point of failure | Hot backup Cold backup with ARCHIVELOG mode |
| To recover an individual user or table | Export |
| For fast recovery | Hot backup Cold backup |
For best backup results, follow these guidelines:
Schedule online backups when there is minimal database access.
Test your backup strategy to see if it is effective; make changes if any area is weak.
Plan to save at least one version back; choose to retain enough versions for your business needs.
Perform database consistency checks just before export or after import.
Back up the master database before and after it is altered; if you save the original database creation scripts, you can use the same scripts to recreate it.
For a distributed system, plan on coordinating backup procedures so each site can be backed up individually without destroying the integrity of the data at other sites.
This section describes the following types of backups:
Registry file backups
System backups
Standby database backups
Standard database backups
System administrators often perform the first two types of backups, and database administrators (DBAs) perform the last two types of backups.
In general, system backups are performed on a small system. To this end, you will bring down the entire system, including all programs, data and log files. Usually, a system backup is run on a nightly basis.
System backups are performed in two steps:
Step 1. Shut down and restart the system in the single-user or maintenance mod.
Step 2. Copy the system files as follows:
Shut down all active applications.
Shut down the relational database.
Stop the Agile Application Server process.
Back up all file systems to an alternate storage device.
Start the Agile Application Server process.
Start the system in multiuser mode.
Restart the database.
Restart applications as needed.
The standby database feature maintains a duplicate database of your primary online database at the same location or at a remote site. (Both the standby database and the primary database must be running on the same hardware platform, operating system, and Oracle patch release.) A standby database acts as a backup when it resides locally, and is implemented as part of a database disaster recovery strategy when it resides at a remote site. The standby database has the following features:
It is copied from the primary or current production database onto a system residing locally or remotely.
It is mounted, but not open, and is in constant recovery mode.
Redo log files generated from the primary database can be transported to the standby database, and the standby database can apply these logs to recover the database.
In the event of a disaster, a standby database can be activated and fully functional as a new production database.
A standby database takes time to set up and configure. For more information on standby databases, see Oracle Data Guard Overview http://www.oracle.com/technetwork/deploy/availability/htdocs/DataGuardOverview.html.
It is recommended that you run your standard database backups on a daily basis. Databases backups can be:
Cold or offline, where the database is shut down before copying database-related files: control files, data files, redo log files, initial parameter file (initagile9.ora), spfileagile9.ora, and password file (pwdagile9.ora). A database running in ARCHIVELOG or NOARCHIVELOG mode can be backed up by a cold backup (NOARCHIVELOG mode permits only cold backups).
Hot or online, where a backup is performed while the database is open and users are accessing it. To perform a hot backup, a database must be running in ARCHIVELOG mode. When performing a hot backup, the database tablespace must first be put in backup mode, then the data file can be copied by the operating system. Once the data file has been copied, the database tablespace can be placed online again. This allows the database to be backed up tablespace by tablespace. The archived log files must be backed up regularly as these are needed for database recovery.
A logical backup creates logical copies of database objects in a binary export file. Logical backups use the agile9 database utilities, agile9exp and agile9imp. When performing logical backups, a database must be open and running.
|
Note: Oracle EXP and IMP utilities do not export the ctxsys account. So, FTS objects will be recreated during an agile9 import. |
For best results, timestamp your backups and generate scripts to perform this automatically using the operating system task schedule command.
This involves backing up the archived log files. You must perform this on a regular basis because they are needed for database recovery
Cold backups are done on all the database-related files, including data files, control files, redo log files, the initial parameter file (initagile9.ora), the password file (pwdagile9.ora), and the server parameter file (spfileagile9.ora).
To perform a cold backup on all database-related files:
Shut down the database.
Use the operating system copy command to copy all of the database data files, the control file, the initial parameter file, the password file, and the archived redo log file (if the database is running in ARCHIVELOG mode) to the backup destination.
Restart the database.
Cold backups are done on all database-related files, including data files, control files, redo log files, the initial parameter file (initagile9.ora), the password file (pwdagile9.ora), and the server parameter file (spfileagile9.ora).
You can import and export a database using either of the following means, based upon your requirement:
Oracle Data Pump - Ideal for very large Agile PLM databases. Enables very fast bulk data and metadata movement between Oracle databases. Uses high-speed, parallel 'expdp' and 'impdp' utilities to move data. The following utilities are included in the Agile PLM installation folder for import/export using Oracle Data Pump:
agile9impdp
agile9expdp
Import / Export Utilities- Ideal for relatively small initial 'schema dumps' and for small databases. Uses the Oracle Database Server 'imp' and 'exp' utilities to move data. The following utilities are included in the installation folder for traditional import/export:
agile9imp
agile9exp
The import or export procedure for the Agile database remains the same in both cases. Only the utilities called are different, as listed above. These procedures are outlined in the following sections.
The import process includes the following actions:
Creating the Agile schema.
Organizing the schema.
Defining import parameters.
Running the import utility.
Before you import, ensure that you have taken a full backup of your Oracle database or of the Agile schema, described in "Exporting the Database".
|
Note: If you are prompted for the service name or host string, you must provide the fully qualified computer name. |
To create the Agile schema and import the database on Windows:
Confirm that the schema is valid. If you do not already have the maintenance scripts generated, run the database installer with the Generate database scripts only option selected, described in "Configuring the Agile Database on Windows". Follow on-screen prompts to complete script generation.
Next, to ensure that the schema is organized correctly, type the following in a Command Prompt window:
cd oracle\admin\<Oracle_SID>\create\<agile schema user>
recreateagile.bat
|
Note: Running this command will drop the existing Agile schema (if any) and any data that it contains. |
Check import parameters in the .par file:
For traditional import: agile9imp.par
For Oracle Data Pump import: agile9impdp.par and agile9impdp_seq_trig.par
For more information on these parameters, see "Import Parameters".
To import the database and recreate indexes and statistics, run the following batch file:
For traditional import: agile9imp.bat
For Oracle Data Pump import:agile9impdp.bat
After the batch file finishes running, type Exit to close the Command Prompt window.
|
Note: For file content index synchronization, call Oracle Support Services. |
To create the Agile schema and import the database on UNIX:
Log in as the user used to install the Oracle database software..
Confirm that the database schema is valid.
Confirm that the user account name is new.
Make a backup of the existing database schema.
Change to the oracle user directory:
$ cd
Copy agile9database from the agile9350db directory:
$ cp ./agile9350db/agile9database.sh
Edit the agile9database shell script, and find AGILE=agile.
Change agile to the new, unused account name.
Save and close the file.
Run agile9database:
$ chmod u+x agile9database.sh
$ ./agile9database.sh
You are prompted to choose a database size. Enter D for demo, S for small, M for medium, L for large, or X for extra large, based on how you created the database initially.
The script creates several SQL scripts and Bourne shell scripts in the following directory:
$ORACLE_BASE/admin/$ORACLE_SID/create/<agile schema user>.
When the script finishes running, type the following:
$ cd $ORACLE_BASE/admin/$ORACLE_SID/create/<agile schema user>
Run recreateagile:
$ chmod u+x recreateagile.sh
$ ./recreateagile.sh
|
Note: Running this command will drop the existing Agile schema (if any) and any data that it contains. |
Check import parameters in the .par file:
For traditional import: agile9imp.par
For Oracle Data Pump import:agile9impdp.par
For more information on these parameters, see "Import Parameters".
To import the database and recreate indexes and statistics, run the following batch file:
For traditional import: agile9imp.sh
For Oracle Data Pump import:agile9impdp.sh
To import the schema and recreate indexes and statistics, run the following commands:
For traditional import:
$ chmod u+x agile9imp.sh
$ ./agile9imp.sh
For Oracle Data Pump import:
$ chmod u+x agile9impdp.sh
$ ./agile9impdp.sh
For file content index synchronization, call Oracle Support Services.
Import parameters are specified within the files listed in the table below. These files are located in the database instance folder along with the import utilities.
| agile9imp.par |
| Parameter
Description |
| file
The file to import. The dump file schema version must match the latest agile schema version. |
| log
The import log file. |
| fromuser
The user account in the file that contains the data that will be imported. fromuser must exist in the dump file specified by the file. |
| touser
The user account where the data is being imported. touser must match the current value of %AGILE%; otherwise, importing the data may cause data corruption. |
|
Note: Other parameters such as indexes, rows, ignore, grants, constraints, and statistics specify other import settings. Do not modify these parameters, and these settings should only be used when using the agile9imp utility. If a standalone imp is used, do not use these settings. |
| agile9impdp.par |
| Parameter
Description |
| directory
The directory object that identifies the location of the import files. |
| dumpfile
The dump file to import. The dump file schema version must match the latest agile schema version. |
| logfile
The import log file. |
| content
The data to import. The default value is data_only. To import to a new schema, first generate maintenance scripts for that schema, run recreateagile to create the schema objects, and then add the parameter remap_schema=<fromuser>:<touser>. |
| parallel
The number of import processes that should be run in parallel. Note This parameter is supported only for Oracle Enterprise Edition. For other versions, this parameter must be removed from the .par file. |
| agile9impdp_seq_trig.par |
| Parameter
Description |
| directory
The directory object that identifies the location of the import files. |
| dumpfile
The dump file to import. The dump file schema version must match the latest agile schema version. |
| logfile
The import log file. |
| include
Specifies that sequences and triggers are to be imported. |
Before creating a new instance, you must delete existing instances (such as agile9).
Make sure the Oracle Listener and Agile9 services are running. (Agile9 is the Oracle service if your SID is Agile9.)
Start the Database Configuration Assistant.
Select Delete a Database, and click Next.
The instance you want to delete (Agile9) should appear in the Available Instances field.
Select the instance.
Type the username (sys) and password (oracle), if necessary.
Click Finish.
You are prompted to confirm the deletion, and then a message appears confirming that the instance has been removed.
Confirm that the agile9-related password file, spfile, and init files in the $ORACLE_HOME/dbs folder on UNIX were deleted.
Confirm that the agile9-related password file, spfile, and init files in the %ORACLE_HOME\database folder on Windows were deleted.
|
Note: After dropping a database instance using the Database Configuration Assistant, the TEMP tablespace must be removed manually. |
|
Important: Before running a script, make sure you have a current backup (export) of your Agile database. For instructions on exporting (creating a DMP backup of your Agile database), refer to your Oracle documentation or Help system. |
To run an SQL script against an Oracle database on Windows:
Create a new directory called "scripts" under the oracle\admin\agile9\create\<agile schema user> directory. Where agile9 is your Oracle SID.
Copy the SQL script to the scripts folder.
On the computer where Oracle is installed, start SQL Plus in a command prompt window.
Type the login ID and password (the defaults are agile and tartan).
Before running the script, create a spool file to record and contain the results from issuing the SQL script. At the SQL prompt type:
spool d:\oracle\admin\agile9\create\<agile schema user>\scripts\<file name>.lstFor example: spool d:\oracle\admin\agile9\create\<agile schema user>\scripts\averifyresults.lst
|
Note: The file with the LST extension is any file name that you want to use to identify the file that will contain these results. It is best and easiest to give the LST file the same name as the file name that is attached to the SQL file. |
For example, if the SQL file to be run is oracle_averify90.sql, then name the spool file oracle_averify90.lst. You can also specify a drive or location other than what is shown in the previous example.
The drive and location specified are where the spool file will be saved.
Issue the command to run the SQL script by typing the following at the SQL prompt:
@d:\oracle\admin\agile9\create\<agile schema user>\scripts\<file name>.sqlFor example: @d:\oracle\admin\agile9\create\<agile schema user>\scripts\oracle_averify90.sql
|
Note: The @ symbol must be typed directly in front of this command line. |
The file with the SQL extension is the name of the specific SQL file to be issued against the database. Depending on where the SQL file is located on the server, you will also specify the drive and location, which could be something other than the d:\oracle\admin\agile9\create\scripts shown in the example.
Note the process of the script as it is executed. When it is complete, there is the indication ”commit, complete.”
At the SQL prompt, type the following: spool off
Close the SQL Plus window and exit SQL Plus.
|
Note: In some cases where a change is being made to the database, you may need to stop and restart both Agile services for the change to take effect. If this is necessary, you will be advised at the time the script is provided. In the case of issuing scripts that do not make changes to the database scripts (for example, oracle_averify.sql scripts), restarting the Agile services is not necessary. |
Locate and open the spool file created in step 5, if necessary.
The file can be opened within an application such as Notepad so that results can be viewed and printed, if necessary.
For maximum data security, you should use a cold backup. You can import the Agile schema DMP file whenever you need to restore a database or replicate it on another computer except for file content index synchronization because of its dependence on the file system.
If you are copying or moving the Agile schema to another computer, you need to set up the computer before importing the Agile schema.
You can perform either of the following types of export:
Export the Agile schema alone - This is much faster than exporting the whole database.
Full export - Export all the schemas in the Oracle database.
|
Important: If you are prompted for the service name or host string during the export, you must provide the fully qualified computer name. |
Use the following procedures for Windows and UNIX respectively.
To export only the Agile schema on Windows:
Ensure that all users are logged off before shutting down the application server.
|
Note: The following commands use the D drive. If you have installed agile9350db or Oracle on another drive, specify that drive letter. |
Open a Command Prompt window, and type the following:
d:
cd \oracle\admin\<Oracle SID>\create\<agile schema user>
Check export parameters in the .par file.
For traditional export: agile9exp.par
For Oracle Data Pump export: agile9expdp.par
For more information on these parameters, see "Export Parameters".
To export the database, run the following batch file:
For traditional export: agile9exp.bat
For Oracle Data Pump export: agile9expdp.bat
To only export the Agie Schema on UNIX:
Make sure all users are logged off. The easiest way to this end is to disconnect the server from the network.
Change to the directory where the Agile scripts are located:
$ cd $ORACLE_BASE/admin/$ORACLE_SID/create/<agile schema user>
Check export parameters in the .par file.
For traditional export: agile9exp.par
For Oracle Data Pump export: agile9expdp.par
For more information on these parameters, see "Export Parameters".
To export the database, run the following commands:
For traditional import:
$ chmod u+x agile9exp.sh
$ ./agile9exp.sh
For Oracle Data Pump import:
$ chmod u+x agile9expdp.sh
$ ./agile9expdp.sh
The database export takes awhile. When it is complete, open the log file and see if the export was successful. If there were problems, call Oracle Support Services.
You can copy the successful export of expdat.dmp to another secure computer as a backup.
If you are unable to export empty tables, you have the following options to overcome this issue.
Apply a patch to upgrade the database to version 11.2.0.3.0 or later a later version.Run ALTER TABLE ALLOCATE EXTENT on each empty table that is not exporting.Set the deferred_segment_creation parameter to FALSE in the database instance and recreate the schema.
The following procedure describe exporting the full Oracle database on a Windows and a UNIX environment.
To export the full Oracle database on Windows:
Make sure all system users are logged off. The easiest way to this is end is to disconnect the server from the network.
Open a Command Prompt window.
Set the character set:
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
Type the following text with spaces and a triple set of quotes as indicated (do not press Enter until you have typed the entire text string):
expsystem/manager full=y file="""<drive>:\Agile9Tmp\<exp_filename>.dmp""" log="""drive:\Agile9Tmp\<exp_filename>.log"""
Agile recommends naming the export file expfull.dmp. For example:
exp system/manager full=y file="""d:\Agile9Tmp\expfull.dmp""" log="""d:\Agile9Tmp\expfull.log"""
To export the full Oracle database on UNIX:
Make sure all system users are logged off. The easiest way to do this is to disconnect the server from the network.
Type the following command:
$ exp system/manager full=y file/home/oracle/agile9350db/ <exp_filename>.dmp log=/home/oracle/agile9350db/<exp_filename>.log
Agile recommends naming the export file expfull.dmp. For example:
exp system/manager full=y file=/home/oracle/agile9350db/expfull.dmp log=/home/oracle/agile9350db/expfull.log
The database export takes a while. When it completes, open the log file and see if the export was successful. If there were problems, call Agile Technical Support.
You can copy the successful export of expfull.dmp to another secure computer as a backup.
| agile9exp.par |
| file
The file to export. |
| log
The export log file. |
| owner
The user account that contains the data to export. |
| agile9expdp.par |
| directory
The directory object that identifies the location of the import files. |
| dumpfile
The dump file to export. |
| logfile
The export log file. |
| schemas
The names of the schemas to export. |
In the case of failure, database recovery uses a previous database backup to recreate a database that is as complete, accurate, and up-to-date as possible. Database recovery depends on the database backup method. Two backup methods are standard backup and logical backup.
For standard backup, including cold and hot backup, database recovery requires the use of the operating system copy command to restore backed up data files.
If the database is running in NOARCHIVELOG mode, there are no backed up archive log files. Recovery is to restore a previously backed up data file, control file, initial parameter file, and password file. No redo log files are applied and no database roll forward is needed. In this scenario, a database can be recovered up to the last backup.
If the database is running ARCHIVELOG mode, database recovery is to restore previous backed up database files up to the last archived log files. When recovering a database, these archived log files are applied and the database is rolled forward. In this scenario, a database can be recovered up to the point of database failure.
For a logical backup, a database recovery involves importing the database or schema from a previous export DMP file. For a logical backup, there is no roll forward involved.
Database recovery can be performed by using Oracle Recovery Manager.
You can use the Oracle Recovery Manager to perform an automatic recovery, restore the full database, restore a data file, or restore a control file.
The major advantage for Oracle Recovery Manager (RMAN) is that it can perform incremental database backup and recovery. Incremental backup and recovery is much faster than a full database backup and recovery, especially for large database systems. RMAN is more complicated to setup compared with a standard backup.
