SAPJSF User Problem in IDES ECC during installation...

Workaround for the Installation Problem when Installing the JAVA-AddIn

Step1:
Log on to the IDES ECC Abap system with user “idadmin”/Password “ides123” (client 001)

- Enter transaction code SNRO
-> Enter Object Name: AENDBELEG
-> Select Button: Number Ranges

-> Edit Intervals

-> Enter the following values as shown:

No. - 01
From number - 1
To Number - 9999999999
Current Number - 0

-> Save


Step 2
Maintain User Data for User SAPJSF

In the IDES ECC Abap System, enter transaction code SU01
-> change User Type to “System”
-> Enter the Master Password (that you defined during the Installation Process)
-> Save


enter Transaction code PFCG
-> Enter Role Name: SAP_BC_JSF_COMMUNICATION_RO
-> Choose TabPage User
-> Assign User SAPJSF to this role
-> Save

!Ignore message: No active plan version exists!


-> Repeat Installation Process on the Application Server

Step 3
Maintain User Data for User J2EE_ADMIN (as done before for user SAPJSF)

enter Transaction code PFCG
-> Enter Role Name: SAP_J2EE_ADMIN
-> Choose TabPage User
-> Assign User J2EE_ADMIN to this role
-> Save



-> Repeat Installation Process on the Application Server

Step 4
Maintain User Data for User J2EE_GUEST (as done before for user SAPJSF)

enter Transaction code PFCG
-> Enter Role Name: SAP_J2EE_GUEST
-> Choose TabPage User
-> Assign User J2EE_GUEST to this role
-> Save

-> Repeat Installation Process on the Application Server

Exchange Infrastructure (XI) in a Nutshell (Getting Started).....

Getting Started with Exchange Infrastructure now becomes easy.....

Here is a blog with everything you need to start working on XI.....

Check out the transactions which are frequently used:

SXMB_IFR - Start Integration Builder

SXMB_MONI - Integration Engine (Monitoring)

SXI_MONITOR - XI Message Monitoring

SLDCHECK - test SLD Connection

Some more...

SLDAPICUST - SLD API Customizing

SXMB_ADM - Administration of Integration Engine

SXI_CACHE - Directory Cache of XI

SXMB_MONI_BPE - Process Engine - Monitoring

Some very useful URLs....

hostname - is the host name of the server on which XI is running

inst number - is the instance number (example: 50000,50100 for port 00,01)

http://hostname:5inst number00/rep --- Exchange Infrastructure Tools

http://hostname:5inst number00/sld --- System Landscape Directory

http://hostname:5inst number00/rwb --- Runtime Workbench

Some more URLs which can be of use (not in the initial stage)...

http://hostname:5inst number00/MessagingSystem --- Message Display Tool

http://hostname:5inst number00/mdt/amtServlet --- Adapter Monitor

http://hostname:5inst number00/exchangeProfile --- Exchange Infrastructure Profile

http://hostname:5inst number00/CPACache --- CPA Cache Monitoring

http://hostname:5inst number00/CPACache/refresh?mode=delta --- Delta CPA cache refresh

http://hostname:5inst number00/CPACache/refresh?mode=full --- Full CPA cache refresh

Now some transaction related to IDOC's as IDOC play a very important role in XI...

WE60 - Documentation for IDoc types

BD87 - Status Monitor for ALE Messages

IDX1 - Maintenance of Port in IDoc Adapter

IDX2 - Meta Data Overview in IDoc Adapter

WE05 - Lists the IDocs

WE02 - Displays the IDoc

WE19 - Test Tool

WE09 - Search for IDocs by Content

Backup - Restore MS SQL using Enterprise Manager

Backup - Restore MS SQL using Enterprise Manager

--------------------------------------------------------------------------------

Backup:
*********

1) Open the SQL Server Enterprise Manager.
2) Click on "Microsoft SQL Servers" in left pane.
3) Expand your server group.
4) Click on "Databases".
5) Right click in the database for which you wish to take backup.
6) Click on "All Tasks" > "Backup Database". A new backup window will appear.
7) Select "Database-Complete" option.
8) Click on "Add" button in destination frame. A new window appears for selecting backup destination.
9) Select "Filename" option.
10) Enter Path where you wish to save backup along with the file name of the backup set. Please specify extension ".bak" for file name of the backup set.
11) Click "OK" twise.
12) A progress indicatior will show backup progress.


Restore:
*********
Login to the SQL server,
choose and run Enterprise Manager.
Select the database onto which you would like to restore the data,
right click on the database,
select 'Restore database' from under the 'All tasks' menu.
Under the 'General' tab, select 'From Device'.
From under the 'parameters' section, click 'select device'.
From the dialog box that appears, select 'add'. Browse to the .BAK file using the option under 'File'.
Select 'OK' across all sub options and finally the main dialog box to perform the restore operation.

Activating SAP* User in Visual Admin in SAP

Activating SAP* User in Visual Admin

Use

In emergency situations where you configured user

management incorrectly and can no longer log on to any

applications, or all administrator users are locked,

you can activate the emergency user SAP* to allow you

to log on to applications, in particular the

configuration tools, and change the configuration. The

SAP* user has full administrator authorizations. For

security reasons, the SAP* user does not have a

default password, so you must specify a password when

you activate the user.

Procedure
...
1. Activate the SAP* user:
a. Start the config tool for editing UME

properties as described in Editing UME Properties.
Editing UME Setting:
1. Start the Config Tool by executing

\j2

ee\configtool\configtool.bat.
2. In the tree, navigate to Global server

configuration services com.sap.security.core.ume.

service.
The list of UME properties appears.
¡ To change the value of a

property:
i. Select the property in the list.
ii. In Value at the bottom of the screen, enter

the new value that you want to assign to the property.
iii. Choose Set.
iv. Choose (Apply changes).
¡ To restore a property to its

default value:
...
i. Select the property in the list.
ii. Choose Restore to default.
iii. Choose (Apply changes).
¡ To restore all properties to their

default values:
...
i. Choose Restore all to default.
3. Restart the nodes in the cluster for

the changes to take effect.

b. Set the following properties:
Property Value Comment
ume.superadmin.activated true This activates

the SAP* user.
ume.superadmin.password Enter

any password of your choice. This defines the password

for the SAP* user.
c. Restart the Java

application server.
The SAP* user is now activated. While it is activated,

all other users are deactivated. You can only log on

with the SAP* user.

2. Fix your configuration as required, logging on

with the user ID SAP*and the password you specified.
For example, to log on to the Visual Administrator to

unlock users or create a new administrator user,

proceed as follows:
i. Start the Visual Administrator by executing

\j2ee\admin\go.bat.
ii. If you have not already done so, you have to

create a new connection entry in which you specify

SAP* as the user name. For more information, see

Creating a New Connection Entry.
iii. In the Connect to SAP J2EE Engine dialog,

select the connection entry you created and choose

Connect.
iv. Enter the password you specified in the

UMEproperties and choose Connect.
v. Do whatever is necessary to fix your

configuration. For example, unlock all administrator

users.
Similarly, you can log on to the UME administration

console using the SAP* user.
3. When you have fixed your configuration,

deactivate the SAP* user again.
a. Start the config tool for

editing UME properties as described in Editing UME

Properties.
b. Set the property ume.superadmin

.activated to false.
b. Restart the Java application server.

SDM Password Change in SAP....

SDM Password Change

Just enter following lines in your COMMAND LINE:

1. enter SDM directory as: cd C:\usr\sap\J2E\JC00\SDM\program

2. Execute the following (just copy-paste)
sdm jstartup "sdmhome=C:\usr\sap\J2E\JC00\SDM\program" "mode=standalone"

3. Execute following line (replacing Password1 with your new password for SDM)
sdm changepassword "sdmhome=C:\usr\sap/J2E/JC00/SDM/program" "newpassword=Password1"

4. I suggest also revert SDM back to integrated mode
sdm jstartup "sdmhome=C:\usr\sap\J2E\JC00\SDM\program" "mode=integrated"

5. Start SDM - StartServer.bat

When administrator or j2ee_admin user is locked in SAP...

When administrator or j2ee_admin user locked in SAP...
If you forgot or lock "Administrator or J2EE_ADMIN" password just follow the steps below:


STEP-1: Enable "SAP*"

1.Start the Config Tool C:\usr\sap\\\j2ee\configtool\configtool.bat
Ex: D:\usr\sap\F02\JC00\j2ee\configtool --> configtool.bat

2.Goto cluster-data --> Global server configuration --> services --> com.sap.security.core.ume.service

3.Double-click on the property "ume.superadmin.activated = TRUE"

4.Double-click on the property "ume.superadmin.password="

5.Save.

6.Restart the engine.

STEP-2: Login with "SAP*" into portal

1. http://:/useradmin/index.jsp

2. Enter userid / password as" SAP* / "

3. Search for "Administrator" user

4. Reset or change password for "Administrtor"


STEP-3: Disable "SAP*"

1.Start the Config Tool C:\usr\sap\\\j2ee\configtool\configtool.bat
Ex: D:\usr\sap\F02\JC00\j2ee\configtool --> configtool.bat

2.Goto cluster-data --> Global server configuration --> services --> com.sap.security.core.ume.service

3.Double-click on the property "ume.superadmin.activated = FALSE"

4.Save.

5. Restart the engine.


STEP-4: Login with "Administrator"

1. http://:/useradmin/index.jsp

2. Enter userid / Password as "Administrator /

3. it will ask change password just change it.

Administering Databases and Datafiles

Administering Databases and Datafiles

Today you will begin to look at some of the basic tasks the Oracle DBA must perform. Specifically, you will look at the database-creation procedure as well as some of the tuning parameters that must be set.

The database-creation operation is split into the tasks of the DBA and the tasks of the end user or application developer. These tasks are split based on what level they access the DBMS.

The Oracle DBA is responsible for all tasks that relate to the DBMS at the low level. Operations that involve the management of datafiles, redo log files, control files, tablespaces, extents, and segments are the responsibility of the DBA, as are the tasks of creating the tables, indexes, clusters, and views (with certain installations, some of these tasks might be performed by or in conjunction with the application development team). In any case, these responsibilities are addressed separately.

Tasks Involved in Creating a Database

Creating a database involves one Oracle DDL statement, and perhaps weeks or months of preparation to be ready for that one step. To create a database, you must know a lot about the data that will be put into the database, the data-access patterns, and the database's volume of activity. All these factors are used to determine the layout of the datafiles and redo log files. These are the responsibility of the Oracle DBA.

Under Windows NT, you must create the instance before you create the database. Because Oracle runs as a service under NT, the instance is necessary for the database-creation phase. An instance can be created, modified, or deleted through the NT Instance Manager. This should not be confused with the Enterprise Manager instance-management tool. Procedures on how to create this bootstrap instance were covered yesterday.

Creating the database actually occurs in two separate--but related--steps. The first step involves the actual database-creation command. This command creates the redo log files, the control files, and the datafiles necessary to create the SYSTEM tablespace. The SYSTEM tablespace contains the SYSTEM rollback segment, the data dictionary, stored procedures, and other structures necessary to run the Oracle instance.

The second phase involves adding tablespaces, tables, indexes, and so on that are used to store your specific data. The first phase described here is covered today; the remaining tasks necessary to finish creating your database will be described tomorrow. It is only when these additional tablespaces are added and your tables are created that your database is complete.

It is important that the DBA and end user work together in defining the database, because the physical layout and the actual data should be configured in an optimal manner. If you underconfigure the hardware or create a poor database layout, you will see a severe degradation in performance.

Tasks of the DBA

The DBA is responsible for all the low-level formatting of the database. I refer to this as formatting because that is basically what these steps do. When you format a disk, it is checked and zeroed out; likewise, when you create a tablespace and datafile, Oracle essentially checks out the disk space and lays down its internal format on the physical disk.

The DBA is responsible for creating the database, adding datafiles, and managing the control files and redo log files necessary for the proper function of the Oracle RDBMS. The DBA is also responsible for allocating these resources to the end user so that he or she can properly use them. The DBA or developer must then build tables, indexes, and clusters on these tablespaces. After the tables have been built and loaded, the user can then access this data.

Tasks of the User or Developer

It is the responsibility of the developer to relay to the DBA what the structure of the data should be and how it will be accessed. In this way, the DBA can have all of the information necessary to properly lay out the database. It is the responsibility of both the DBA and the application developer to work together to provide a stable and usable environment for the end user.

Designing the Database

Designing the database can be quite complex and time consuming, but well worth the effort. Any mistakes at this point can be very costly in terms of performance and stability of the system in the long run. A well-designed system takes into account the following factors:
Performance--The database that has been designed for performance from the very beginning will outperform any system that has not. Many critical performance items can only be configured in the design stage, as you will soon see.
Backup--Often, the DBA is given only a short time to accomplish the required backup operations. By planning the data layout at the very beginning with this criterion in mind, these operations can more easily be accomplished.
Recovery--Nobody plans for his system to crash, but it is an unfortunate fact of life that hardware and software components sometimes fail. Planning can facilitate the recovery process and can sometimes be the difference between recovering and not recovering.
Function--The database layout has to take into account its ultimate function. Depending on what type of applications are being run and what the data looks like, there might be different design considerations.

Physical Database Layout

As part of the design considerations mentioned previously, the physical layout of the database is very important. You should consider several factors when designing the physical layout, including
Database size--You must be able to support the amount of data you will be loading into the database.
Performance--A physical disk drive can support only a certain number of I/Os before performance begins to suffer.
Function--You might decide to lay out tablespaces based on their function. This allows different departments to have different backup schedules, and so on.
Data protection--It is very important that some types of files be protected against media failure. Primarily, the redo log files and the archive log files need to be protected.
Partitioning--Depending on what type and amount of partitioning you will be doing, the physical layout might vary.

So you gain a complete understanding of how and why the physical database design might vary based on function, let's review a few basic factors.

Database Size

The size of the database is a key factor in how the physical layout is designed. For very small databases, this might not be much of an issue, but for very large databases it can be a major issue. You must make sure that you have not only enough space for the datafiles themselves, but also for associated indexes. In some cases, you might need to have a large temporary area to copy input files to before they are loaded into the database. Oracle has a few restrictions on the size of the components of the database:
The maximum size of a datafile is 32GB (gigabytes).
The maximum number of datafiles per tablespace is 1,022.
The maximum size of a tablespace is 32TB (terabytes).

As you can see, Oracle allows you to create and maintain very large databases. You might think this is an incredible size for a database and no system will ever achieve this size. Well, I can remember when a 10MB disk drive was huge for a PC. If industry trends continue the way they've been going, I would not be surprised to see systems with 32TB tablespaces in the near future.

As you saw on Day 4, "Properly Sizing Your Database and Planning for Growth," it is not only necessary to build your system with today's requirements in mind, but also to plan for the future. Systems can increase in size at incredible rates, and you must be ready for it.

Performance

An important factor to remember when designing the physical layout of your database is the performance of the various components in the system. The load on the system caused by numerous users requesting data will generate a certain amount of disk I/O.

The disk drives that comprise the system can service only so many I/Os per second before the service time (the time it takes for an I/O to complete) starts increasing. In fact, it is recommended that for a standard 7200 RPM SCSI disk drive, you run it at only the following rates:
Random I/O--60-70 I/Os per second per disk drive.
Sequential I/O--100 I/Os per second per disk drive.

NOTE: With a sequential I/O, the data that is requested is either on the same track as the last data accessed or on an adjacent track.
With a random I/O, the data that is requested is on another track on the disk drive, which requires the disk arm to move, thus causing a seek. This track seek takes much more time to complete than the actual reading of the data.

Taking these factors into account, you should isolate the sequentially accessed data and spread out the randomly accessed data as much as possible. A hardware or software disk array is a good way to spread out these randomly accessed I/Os. By determining the amount of I/O traffic that will be generated, you can decide how many disk drives are required. A lack of disk drives can cause severe performance problems. In many cases, you will find that you are required to use many more disk drives for performance reasons than you would for size requirements.

TIP: The redo log files are sequentially accessed, as are the archive log files. These files should be isolated from randomly accessed files in order to increase performance.

Function

You might also find that you want to separate your database into different tablespaces based on function. That way, maintenance operations and backups can be done on a per-department basis. For example, you can put accounting and sales on different tablespaces so they can be backed up separately.

You will also find that different types of operations have different characteristics. For example, an OLTP system that has a large number of updates is very sensitive to the placement of the redo logs due to performance considerations. This type of system might also be continuously creating archive log files that need to be protected and backed up. This requires some planning.

On the other hand, a decision support system (DSS) that primarily queries might not need a high-performance redo log volume, and archiving might occur only once per day. In that case, you might want to design your database layout to favor the datafiles.

Data Protection

The primary job of the DBA is to protect the data in the system. As part of this job, you the DBA must determine how to protect that data. As you saw on Day 2, "Exploring the Oracle Architecture," every change that Oracle makes to the database is written to the redo log files and, in turn, these redo log files are archived. These redo log files and archive log files can be used, in conjunction with a recent backup, to recover the database to the point of system failure. This is, of course, assuming that the redo log files and archive log files are intact.

It is therefore necessary to protect the redo log files and archive log files from media failure. This can be accomplished either via hardware or software fault tolerance. I prefer hardware fault tolerance in the form of a RAID (redundant array of inexpensive disks) subsystem, but software fault tolerance is also very good.

There are several options available with RAID controllers; the most popular are RAID-1 and RAID-5. Each has advantages and disadvantages, as shown here:
RAID-1--Also known as mirroring. The entire contents of a disk drive are duplicated on another disk drive. This is the fastest fault-tolerant method and offers the most protection. It is, however, the most costly because you must double your disk-space requirements.
RAID-5--Also known as data guarding. In this method of fault tolerance, a distributed parity is written across all the disk drives. The system can survive the failure of one disk drive. RAID-5 is very fast for reading, but write performance is degraded. RAID-5 is typically too slow for the redo log files, which need fast write access. RAID-5 can be acceptable for datafiles and possibly for the archive log files.

TIP: It is a good idea to put your operating system and redo log files on separate RAID-1 volumes. This provides the best level of protection and performance.

Typically, the archive log files can reside on a RAID-5 volume because performance is not critical. If you find that you are having trouble keeping up on the archive log writes, you might need to move them to RAID-1.

Your datafiles can reside on a non-fault-tolerant disk volume if you are limited on budget and can afford to have your system down in the event of a disk failure. As long as you have a good backup, you lose no data.
Partitioning

You might also decide to adjust the physical layout of your database based on the partitioning method you have chosen. Oracle has introduced a new partitioning method with Oracle8. Various partitions can be allocated to Oracle tables based on ranges of data. Because the partitioning is actually done at the tablespace level and the tablespaces are made up of datafiles, it is important to plan your partitioning before you build your datafiles.

Because Oracle supports only range partitioning, whether you partition your data is dependent on your application and data. If you can take advantage of partitioning, you will definitely see some advantages in terms of reduced downtime and increased performance.

Creating the Instance

Before you can create the Oracle database under Windows NT or even start up the Oracle instance, you must create an instance. Follow the steps in the previous chapter to create the Oracle instance; start up the instance, and then you can create the database. Because Oracle functions as a service under NT, you cannot create a database without creating the instance.

Creating the Database

When you create a database, you are primarily creating the redo log files, the control files, and the SYSTEM tablespace. This SYSTEM tablespace is where important structures such as the data dictionary are kept. The data dictionary keeps track of all of the datafiles, the database schema, and all other pertinent database information. After you create this initial database, you will create more tablespaces and assign your schema to those tablespaces. So let's continue creating the initial database.

After the instance has been created, you can create the database. Creating the database is done either through Enterprise Manager or with the CREATE DATABASE DDL command. Although Enterprise Manager is quite convenient and easy to use, I prefer to script the creation procedure into a SQL file. By doing this, you can easily run this creation procedure over and over again and modify it for other purposes. This also provides you with a record of how this procedure was done.

Setup

There are a few initial setup steps that should be completed before you begin the actual creation process. These steps are designed to help you create the right configuration as well as to protect yourself from potential future problems. These steps involve the following:

1. Backing up any existing databases on the system

2. Creating the init.ora file

3. Starting up the Oracle instance

If you follow these steps, you should be ready to successfully create an Oracle database.

Let's look at these steps.

Backing up existing Databases

This is purely a precautionary step. It is always a good idea to back up all your databases on a regular basis. It is also recommended that you back up your databases prior to any major system changes, such as the creation of a new database.

No matter how careful you are in preparing for the database creation, there is always some danger in making major changes to the system. Because it is possible that a mistake could affect existing control files, redo log files, or datafiles, this precaution might save you quite a bit of work.

If some unforeseen event causes data loss in an existing database, the recovery process will be facilitated by having a fresh backup. This is just a precaution, and one that is well worth the time and effort.

Creating the init.ora File

It is necessary to create a new parameter file for each new database. The parameter file, also known as the init.ora file, contains important information concerning the structure of your database. All the Oracle tuning parameters are described in Appendix B, "Oracle Tuning Parameters," but a few parameters are critical to the creation of the database:
DB_NAME--This parameter specifies the name of the database. The DB_NAME parameter is a string of eight or fewer characters. This name is typically the same as your Oracle SID (system identifier). The default database was built with DB_NAME = oracle.
DB_DOMAIN--This parameter specifies the network domain where your server resides. This parameter, in conjunction with the DB_NAME parameter, is used to identify your database over the network. The default database was built with DB_DOMAIN = WORLD.
CONTROL_FILE--This parameter specifies one or more control files to be used for this database. It is a very good idea to specify multiple control files, in case of disk or other failures.
DB_BLOCK_SIZE--This parameter specifies the size of the Oracle data block. The data block is the smallest unit of space within the datafiles, or in memory. The DB_BLOCK_SIZE can make a difference in performance, depending on your application. The default size is 2,048 bytes, or 2KB. After the database is built, the block size cannot change.
DB_BLOCK_BUFFER--This parameter specifies the number of blocks to be allocated in memory for database caching. This is very important for performance. Too few buffers causes a low cache-hit rate; too many buffers can take up too much memory and cause paging. This parameter can be changed after the database has been built.
PROCESSES--This parameter specifies the maximum number of OS processes or threads that can be connected to Oracle. Remember that this must include five extra processes to account for the background processes.
ROLLBACK_SEGMENT--This parameter specifies a list of rollback segments that is acquired at instance startup. These segments are in addition to the system rollback segment. This should be set after you create the rollback segments for your database.

The following parameters should also be set, based on your licensing agreement with Oracle:
LICENSE_MAX_SESSIONS--This parameter specifies the maximum number of concurrent sessions that can connect into the Oracle instance.
LICENSE_SESSION_WARNING--This is similar to LICENSE_MAX_SESSIONS in that it relates to the maximum number of sessions that can be connected into the instance. After LICENSE_SESSION_WARNING sessions have connected into the instance, you can continue to connect more sessions until LICENSE_MAX_SESSIONS has been reached, but you will receive a warning from Oracle that you are reaching your limit.
LICENSE_MAX_USERS--This parameter specifies the maximum number of unique users that can be created in the database.

After these parameters are set, you can move on to the next phase: starting up the Oracle instance.

Starting Up the Oracle Instance with NOMOUNT

Before you start up the Oracle instance, check your SID. This will indicate which database you will connect to. You should typically set your SID to the same name as in the DB_NAME parameter. When your application connects into Oracle, it uses the SID to determine which database (if there is more than one) to connect to. Depending on the application and your network, the SID might be used to connect you to a particular database on a particular system via SQL*Net.

This is similar to starting up the instance as shown yesterday, except that to create a database, the instance be must be started with the NOMOUNT option (this is because no database associated with that instance is available to mount). After the SID has been checked, you can then start the Oracle instance. This can be accomplished in two ways: by using the Oracle Instance Manager or by using Server Manager. Both methods are presented here.

Starting the Instance with Server Manager

The way I prefer to build a database is by scripting it into a command file. That way, I will have a permanent record of what I have done to create the database. The first command in my script will be to start the Oracle instance in NOMOUNT mode as follows:

connect internal/oracle

startup [pfile=c:\orant\database\initORCL.ora] NOMOUNT;


NOTE: The brackets indicate an optional parameter. If the pfile parameter is not specified, c:\orant\database\initSID.ora will be used (where SID is the value of your SID environment variable).

By scripting, you can reuse this in the event you need to re-create the database or as a template for other database creations.

Creating the Database

After you have created the instance, you can move on to the next stage: creating the database itself. As with the instance, it is possible to create the database both from a graphical tool (in this case, the NT Instance Manager) or from the command line or script using the Oracle Server Manager. Here you will look at both methods. I prefer character-based creation because it can be scripted and thus re-used.
Creating the Database with Server Manager

To create the database with Server Manager, you must type it manually or, as I prefer, use a SQL script. The database is created with the CREATE DATABASE command.
The Syntax for CREATE DATABASE

The syntax for this command is as follows:

SYNTAX:

CREATE DATABASE [[database]
[CONTROLFILE REUSE]]
LOGFILE [GROUP group_number] logfile
[, [GROUP group_number] logfile] ...
[MAXLOGFILES number]
[MAXLOGMEMBERS number]
[MAXLOGHISTORY number]
[MAXDATAFILES number]
[MAXINSTANCES number]
[ARCHIVELOG or NOARCHIVELOG]
[EXCLUSIVE]
[CHARACTER SET charset]
[NATIONAL CHARACTER SET charset]
DATAFILE file_specification [AUTOEXTEND OFF ON
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[NEXT number K M] [MAXSIZE UNLIMITED number K M]
[, DATAFILE file_specification [AUTOEXTEND OFF ON
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[NEXT number K M] [MAXSIZE UNLIMITED number K M]]

The various parameters and variables are
database--The name of the database to be created. This is up to eight characters long.
CONTROLFILE REUSE--This optional parameter specifies that any existing control files be overwritten with this new information. Without this parameter, the CREATE DATABASE command would fail if the control files exist.
LOGFILE--This parameter is followed by the log-file name. This specifies the name of the redo log file. You can specify the log-file group with the optional GROUP parameter, or a log-file group number will be assigned automatically.
MAXLOGFILE--This parameter specifies a maximum number of log-file groups that can be created for this database.
MAXLOGMEMBER--This parameter specifies a maximum number of log-file members in a log-file group.
MAXLOGHISTORE--This is a parallel-server parameter that specifies a maximum number of archive log files to be used in recovery in a parallel-server environment.
MAXDATAFILE--This parameter specifies the maximum number of files that can be added to a database before the control file automatically expands.
MAXINSTANCES--This parameter specifies a maximum number of instances that the database can have open simultaneously.
ARCHIVELO--This parameter specifies that the database will be run in ARCHIVELOG mode. In ARCHIVELOG mode, a redo log group must be archived before it can be reused. ARCHIVELOG mode is necessary for recovery.
NOARCHIVELO--This parameter specifies that the database will be run in NOARCHIVELOG mode. In NOARCHIVELOG mode, the redo log groups are not archived. This is the default setting.
EXCLUSIVE--This parameter specifies that the database is mounted in EXCLUSIVE mode after it has been created. In EXCLUSIVE mode, only one instance can mount the database.
CHARACTER SET--This parameter specifies that the data in the database will be stored in the charset character set.
NATIONAL CHARACTER SET--This parameter specifies that the National Character Set used to store data in the NCHAR, NCLOB, and NVARCHAR2 columns will use the charset character set.
DATAFILE--This parameter specifies that the file identified by file_specification will be used as a datafile.

File specification is made up of the following:
`filename' SIZE number (K or M)--The file specification is used to define the name and the initial size in K (kilobytes) or M (megabytes) of the datafile.
[REUSE]--This parameter allows you to use the name of an existing file.

The following options are available to the DATAFILE parameter:
AUTOEXTEND OFF--Specifies that the autoextend feature is not enabled.
AUTOEXTEND ON--Specifies that the autoextend feature is enabled.

The following options are available to the AUTOEXTEND ON parameter:
NEXT--Specifies the number K (kilobytes) or M (megabytes) automatically added to the datafile each time it autoextends.
MAXSIZE UNLIMITED--Specifies that the maximum size of the extended datafile is unlimited. It continues to grow until it runs out of disk space or reaches the maximum file size.
MAXSIZEnumber (K or M)--Specifies that the maximum size that the datafile can autoextend to is number K (kilobytes) or M (megabytes).

The CREATE DATABASE command might seem to be quite complex, but it is not really that difficult. It is not necessary to use all the optional parameters, but as you gain experience, you might decide to use them. An example of creating a database is shown here:

CREATE DATABASE dogs CONTROLFILE REUSE
LOGFILE
GROUP 1 ( `d:\database\log1a.dbf', `e:\database\log1b.dbf') SIZE 100K,
GROUP 2 ( `d:\database\log2a.dbf', `e:\database\log2b.dbf') SIZE 100K
DATAFILE `d:\database\data1.dbf' SIZE 10M,
`d:\database\data2.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 50M;

It is not necessary to create all the datafiles at database-creation time. In fact, if you are creating a large number of datafiles, it is more efficient to create the datafiles in parallel using ALTER TABLESPACE ADD DATAFILE.

The CREATE DATABASE command serializes its operations. So if you specify two datafiles, the second will not be created and initialized until the first one has completed. The operation of adding datafiles can, however, be accomplished in parallel. This will reduce the time necessary to create the database.

Creating the Catalogs

After the database has been created, two scripts (CATALOG.SQL and CATPROC.SQL) should be run to create the data dictionary views. These views are important to the operation of the system as well as for the DBA. These catalog scripts can be run within the Server Manager by using the @ character to indicate that you are running a SQL script, as shown here:

@D:\ORANT\RDBMS80\ADMIN\CATALOG;
...
Much data returned
...
@D:\ORANT\RDBMS80\ADMIN\CATPROC;
...
Much data returned
...

You will see the SQL script text as it is running. This process is quite time consuming and will display a very large amount of data.


NOTE: Running the CATALOG.SQL and CATPROC.SQL scripts will take a significant amount of time; don't worry if it seems like it is taking forever.
CATALOG.SQL

The Oracle SQL script CATALOG.SQL creates many of the views used by the system and by the DBA. These include the V$ tables that are referenced throughout the book. Also created by this script are the DBA_, USER_, and SYS_ views. Synonyms are also created, and many grants are done by this script. All these views, synonyms, and permissions are very important to the operation of the system.
CATPROC.SQL

The CATPROC.SQL script is also extremely important to the function of the system. This script sets up the database for the procedural option. The CATPROC.SQL script runs many other SQL scripts, including ones that set up permissions, insert stored procedures into the system, and load a number of packages into the database.

If you run the CATALOG.SQL and CATPROC.SQL scripts, your system will be configured and ready to create tables and load the database. Nonetheless, there might be other options you want to set or parameters you want to alter. These can be accomplished through the use of the ALTER DATABASE command, as shown in the next section.

Modifying the Database

Many of the tasks involved in modifying the Oracle database, tablespaces, and datafiles can be done via the Oracle Enterprise Manager tools or through the use of DDL statements via the Oracle Server Manager. Both methods are described in this section. As you will see, the Oracle Enterprise Manager simplifies the task by providing you with choices, but is somewhat limited in functionality.

Modifying the Database with the ALTER DATABASE Command

Modifying the database from Server Manager is accomplished via the ALTER DATABASE command. This command is used to alter various parameters and specifications on the database itself, and can be typed into Server Manager or run as a SQL script. The syntax of the ALTER DATABASE command is as follows.

ALTER DATABASE [database]
[MOUNT [STANDBY DATABASE] [EXCLUSIVE PARALLEL]]
[CONVERT]
[OPEN [RESETLOGS NORESETLOGS]]
[ACTIVATE STANDBY DATABASE]
[ARCHIVELOG NOARCHIVELOG]
[RECOVER recover_parameters]
[ADD LOGFILE [THREAD number] [GROUP number] logfile
[, [GROUP number] logfile] ...]
[ADD LOGFILE MEMBER `filename' [REUSE]
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[, `filename' [REUSE] ...][TO GROUP number] or
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[`filename' [, `filename'] ...]
[, `filename' [REUSE] [, `filename' [REUSE] ...
[TO GROUP number] or [`filename' [, `filename'] ...]]
[DROP LOGFILE [GROUP number] or [`filename' [, `filename'] ...]
[, GROUP number] or [`filename' [, `filename'] ...]]
[DROP LOGFILE MEMBER `filename' [, `filename'] ...]
[CLEAR [UNARCHIVED] LOGFILE
[GROUP number] or [`filename' [, `filename'] ...]
[, GROUP number] or [`filename' [, `filename'] ...]
[UNRECOVERABLE DATAFILE]]
[RENAME FILE `filename' [, `filename'] ... TO `filename' [, `filename'] ...
[CREATE STANDBY CONTROLFILE AS `control_file_name' [REUSE]]
[BACKUP CONTROLFILE
[TO `filename' [REUSE]] or [TO TRACE [RESETLOGS or NORESETLOGS]]
[RENAME GLOBAL NAME TO database[.domain] ...]
[RESET COMPATABILITY]
[SET [DBLOW = value] or [DBHIGH = value] or [DBMAC ON or OFF]]
[ENABLE [PUBLIC] THREAD number]
[DISABLE THREAD number]
[CREATE DATAFILE `filename' [, `filename'] ...
AS filespec [, filespec] ...]
DATAFILE `filename' [, `filename'] ...
ONLINE or OFFLINE [DROP] or RESIZE number (K or M)
or AUTOEXTEND OFF or ON [NEXT number (K or M)] [MAXSIZE UNLIMITED or number
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">; (K or M)]
or END BACKUP]

The various parameters and variables for the ALTER DATABASE command are as follows:
database--This specifies the name of the database to be created and is a character string up to eight characters in length.
MOUNT--This parameter is used to mount an unmounted database.

The various options to the ALTER DATABASE database MOUNT command are as follows:
MOUNT STANDBY DATABASE--This is used to mount a standby database. The standby database will be described in detail on Days 16, "Understanding Effective Backup Techniques," and 17, "Recovering the Database."
MOUNT EXCLUSIVE--This is used to mount the database in EXCLUSIVE mode. EXCLUSIVE mode specifies that only one instance can mount the database. This is the default mode for the ALTER DATABASE MOUNT command.
MOUNT PARALLEL--This is used to mount the database in PARALLEL mode. PARALLEL mode allows other instances to mount the database in a parallel-server environment.

Other optional parameters to the ALTER DATABASE command are
CONVERT --This option is used to convert an Oracle7 data dictionary to the Oracle8 data dictionary.
OPEN--This parameter opens the database for normal use. Optionally, you can specify the additional parameter RESETLOGS or NORESETLOGS.

The options to the ALTER DATABASE database OPEN command are as follows:
OPEN RESETLOGS--With the RESETLOG parameter set, the redo logs are essentially reset to sequence number 1. This basically discards all information in the redo logs, thus starting over. The RESETLOGS command is required after an incomplete recovery done with the RECOVER UNTIL option of media recovery or after a backup control file. A backup should be taken immediately after an ALTER DATABASE RESETLOGS command. This is described in more detail on Days 16 and 17.
OPEN NORESETLOGS--This is the default operation of the ALTER DATABASE OPEN command, specifying not to reset the redo logs.

Other optional parameters to the ALTER DATABASE command are
ACTIVATE STANDBY DATABASE --This parameter is used to make a standby database into the current active database. The standby database is described in detail on Days 16 and 17.
ARCHIVELO --This specifies that this database is running in ARCHIVELOG mode. In ARCHIVELOG mode, each redo log group is archived to an archive log file before it can be reused. ARCHIVELOG mode is essential for data recovery in the event of media failure.
NOARCHIVELO--This specifies that the database is not running in ARCHIVELOG mode. Running in NOARCHIVELOG mode is very dangerous because media recovery might not be possible. See Days 16 and 17 for more details.
RECOVER--The recovery parameters are shown immediately after this section.
ADD LOGFILE logfile'--This parameter is used to specify the addition of log files named `logfile' to the database. By specifying the THREAD option, you can add this log file to a specific parallel server thread; omitting the THREAD parameter will cause the redo log group to be added to your current instance. You can also specify the value of the GROUP parameter. If you omit the GROUP value, one is assigned automatically. You can specify one or more log-file groups with this parameter.
ADD LOGFILE MEMBER filename'--This parameter adds members named `filename' to existing log-file groups. The optional parameter REUSE must be included if the file `filename' already exists. You specify the group that you are adding to in one of several different ways.

The various options to the ALTER DATABASE database ADD LOGFILE MEMBER command are as follows:
TO GROUP number--This can be used if you know the log-file group identification parameter.
TO GROUP `filename'--You can also add to the log-file group by specifying the name or names of all members of the existing log-file group.

Other optional parameters to the ALTER DATABASE command include
DROP LOGFILE --This parameter drops all members of a log-file group. You specify the group that you are dropping in one of two ways: by specifying the GROUP or by specifying members of the group as described here.

The various options to the ALTER DATABASE database DROP LOGFILE command are as follows:
GROUP number--If you know the group identifier, you can drop the log-file group by specifying it.
`filename'--You can add to the log-file group by specifying the name or names of all members of the existing log-file group.

Other optional parameters to the ALTER DATABASE command are
DROP LOGFILE MEMBER `filename'--This command is used to drop a member or members of a log-file group. The member to be dropped is specified by the log-file member's filename. One or more members can be specified.
CLEAR LOGFILE --This command is used to drop and re-create a log file. This can be used in the event of a problem with an existing log file. By using the optional UNARCHIVED qualifier, you can clear a log file that has logging information in it without having to first archive that logging information. If you use the UNARCHIVED qualifier, you will probably make your database unrecoverable in the event of media failure. You specify the log files that you are clearing in one of two ways: by specifying the GROUP or by specifying members of the group as described here.

The various options to the ALTER DATABASE database CLEAR LOGFILE command are as follows:
GROUP number--If you know the group identifier, you can drop the log-file group by specifying it.
filename'--You can add to the log-file group by specifying the name or names of all members of the existing log-file group.
UNRECOVERABLE DATAFILE--This option to CLEAR LOGFILES is used if the tablespace has a datafile that is offline. This requires that the tablespace and the datafile be dropped after the CLEAR LOGFILES operation has finished.

Other optional parameters to the ALTER DATABASE command are
RENAME FILE `filename' TO `filename'--This command is used to rename datafiles or log files. This only changes the name in the control file, not on disk.
CREATE STANDBY CONTROLFILE AS`control_file_name'--This command is used to create a standby control file called control_file_name. The optional REUSE qualifier allows you to specify the name of an existing file that will be reused.
BACKUP CONTROLFILE --This command is used to create a backup of the control file.

This can be accomplished in the following two ways.

The various options to the ALTER DATABASE database CLEAR LOGFILE command are as follows:
TO `filename'--By assigning the backup control file to a filename, the control file will be backed up to this file. If the file already exists, the optional REUSE qualifier must be used.
TO TRACE--This optional parameter writes SQL to a trace file that can be used to re-create the control files. You can specify the qualifiers RESETLOGS or NORESETLOGS, which will add SQL to open the database with these options. The SQL statements are complete enough to start up the database, re-create the control files, and recover and open the database appropriately.

Tip: By running the ALTER DATABASE database BACKUP CONTROLFILE TO TRACE command after your database has been altered in any way, you will have a method of re-creating the control files if necessary. This is part of a good recovery plan.

Other optional parameters to the ALTER DATABASE command are
RENAME GLOBAL NAME TO--This command allows you to rename the database name, domain name, or both.
RESET COMPATABILITY--This command resets the compatability level of the database to an earlier version of Oracle after the instance is restarted.

SET--The following trusted Oracle parameters are modified via the SET command SET DBLOW = value, SET DBHIGH = value, SET DBMAC ON or OFF. Trusted Oracle is not covered in this book. See the Trusted Oracle Administration Guide from Oracle for more information.
ENABLE [PUBLIC] THREAD number--This parallel-server command is used to enable a thread of redo log groups identified by number. The addition of the PUBLIC qualifier allows this log file thread to be used by any instance.
DISABLE THREADnumber--This command disables a log file thread group identified by number, making it unavailable to any instance.
CREATE DATAFILE filename'--This parameter is used to create a datafile that was lost due to media failure and was not backed up.
ASfilespec--This option of the CREATE DATAFILE command is used to specify the filespec specification parameters.
DATAFILE filename'--The ALTER DATABASE database DATAFILE command has several different functions that allow you to change the state of database datafiles.

The various options to the ALTER DATABASE database DATAFILE `filename' command are as follows:
ONLINE--Brings the datafile online.
OFFLINE [DROP--Takes the datafile offline. When the database is running in NOARCHIVELOG mode, the drop command takes it offline.
RESIZE number(K or M)--This is used to resize a datafile to number K (kilobytes) or M (megabytes).
AUTOEXTEND OFF or ON--This command is used to alter a datafile to have autoextend either on or off. With autoextend on, the file will increase in size based on the AUTOEXTEND parameters.

The various options to the ALTER DATABASE database DATAFILE `filename' AUTOEXTEND ON command are as follows:
NEXT number (K or M)--This option specifies that the database will grow in increments of number K (kilobytes) or M (megabytes) whenever space requirements force the datafile to grow.
MAXSIZE UNLIMITED--This parameter specifies that the maximum size of the datafile is governed only by disk space and OS datafile limitations. On NT, a datafile can grow to 32GB in size.
MAXSIZEnumber (K or M)--This option specifies that the maximum size a datafile will grow to is number K (kilobytes) or M (megabytes).

Another optional parameter to the ALTER DATABASE command is
END BACKUP --This option specifies that media recovery should not be done when an online backup was interrupted by an instance failure.

The parameters and options to the RECOVER clause are

RECOVER [AUTOMATIC] [FROM `path']
[[STANDBY] DATABASE]
[UNTIL CANCEL] or [UNTIL TIME `time']
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;or [UNTIL CHANGE number] or [USING BACKUP CONTROLFILE] ...]
[TABLESPACE tablespace [,tablespace] ....]
[DATAFILE `filename' [, `filename'] ....]
[LOGFILE `filename']
[CONTINUE [DEFAULT]]
[CANCEL]
[PARALLEL parallel_definition]

The various parameters and variables for the RECOVER option are
AUTOMATIC--This qualifier specifies that the recovery process automatically figures out the names of the redo log files that it needs to apply in order to perform media recovery.
FROM path'--This qualifier allows you to specify the location of archive log files. This is useful because you do not always keep the archive log files in the directory where they were originally generated.
STANDBY--This recovers the standby database.
DATABASE--This is the default option. It indicates that the database should be recovered.
UNTIL ?--The UNTIL parameters are very important to the recovery of the database if you are recovering from a software or operator problem. These parameters allow you to recover up until a specific point.

The various options to the ALTER DATABASE database RECOVER UNTIL ?? command are as follows:
UNTIL CANCEL--The database will be recovered until you submit an ALTER DATABASE database RECOVER CANCEL command.
UNTIL TIME time'--This command performs a time-based recovery. It recovers all transactions that have finished until `time'. The qualifier is given in the form `YYYY-MM-DD:HH24:MI:SS'. This can be quite useful if you know when the suspected SQL statement that caused the failure occurred.
UNTIL CHANGEnumber--This performs a recovery up until the last transaction before the system change number.

Other optional parameters to the ALTER DATABASE database RECOVER command are
USING BACKUP CONTROLFILE--This specifies that the recovery should be done using a backup control file.
TABLESPACEtablespace--This performs recovery only on the specified tablespace(s).
DATAFILE filename'--This performs recovery only on the specified datafile.
LOGFILE filename'--This performs recovery using the specified log file.
CONTINUE [DEFAULT--This continues recovery after it has been interrupted. CONTINUE DEFAULT is similar, but uses Oracle-generated default values.
CANCEL--This cancels the UNTIL CANCEL-based recovery.
PARALLEL (DEGREEnumber)--This specifies the degree of parallelism to use during the recovery process. The number of parallel processes is determined by the value of number.

The recovery process is key to the stability of Oracle and your database. This topic is covered in much more detail on Days 16 and 17.

Let's look at a few examples of using the ALTER DATABASE command to perform regular maintenance tasks.

Changing to Use ARCHIVELOG Mode

If you are not running in ARCHIVELOG mode, you are in danger of losing data in the event of a system failure. To alter the database to run in ARCHIVELOG mode, use the following syntax:

ALTER DATABASE dogs ARCHIVELOG;


Performing a Timed Recovery

It is sometimes necessary to perform a timed recovery. If a certain SQL statement caused a system failure, you should recover until just before that statement was issued. If a SQL statement that caused data loss was inadvertently run, you can recover until just before that statement was issued. Here is an example of how to perform a timed recovery:

ALTER DATABASE dogs RECOVER UNTIL TIME `1999-07-04:15:03:00';

This statement recovers the database until 3:03 p.m. on July 4, 1999.

Open a Closed Database

Databases are often brought up and mounted but not opened for maintenance. To open a closed database, use the following syntax:

ALTER DATABASE dogs OPEN;

Backing Up a Control File

Backing up control files is an important operation. Here is an example of how to use ALTER DATABASE to back up your control files:

ALTER DATABASE dogs BACKUP CONTROLFILE TO `C:\backup\cntrlDOGS.dbf;

Backing Up a Control File to Trace

Backing up your control file to trace generates a SQL script that can be used to re-create the control file in the event of an emergency recovery. Use this syntax:

ALTER DATABASE dogs BACKUP CONTROLFILE TO TRACE;

Followup

Even after the database and datafiles have been created, your job is not over. You must watch the system carefully to make sure that you don't run out of space or other resources. As you saw on Day 4, capacity planning and sizing are not easy jobs. By anticipating and solving problems before they become critical, you will avoid costly setbacks. You must periodically monitor the system from the OS and the Oracle perspectives to avoid these types of problems.

Monitoring the Datafiles

To make sure you are not running out of space, you can use Enterprise Manager's Storage Manager utility. If you click the Datafiles icon on the left, you will see a list of datafiles, the size of each file, and how much it is used on the right. This is a quick and easy way of determining whether you are running out of space in your datafiles. You can manually check this by looking at several different system views and by adding up individual free spaces. The Oracle Storage Manager simplifies this task.

Load Balancing

It is important that you not overdrive any of the disk drives or disk arrays in your system. This can severely hurt your performance. The I/O rates at which your system is running can be monitored with the NT Performance Monitor. I will not spend much time on the Performance Monitor, but I do want to mention a few points that you should watch out for:
Use diskperf--Turn on diskperf by using the NT command diskperf -y. By turning on diskperf, you will see much more information about your disk I/O rates when you run perfmon.
Monitor I/O--Use perfmon to look at PhysicalDisk. Of great importance is the reads and writes per second (throughput) and the seconds/read and seconds/write (latency).

If you see a disk drive or disk array (a disk array looks like a big disk drive to the OS) that has many more I/Os per second per disk than the others, you might have a balance problem.


TIP: The I/Os per disk drive per second should not exceed 60-79 on the data volumes. On an array, divide the number of I/Os by the number of drives to get the I/Os per drive.
A typical disk drive should take about 20-30 milliseconds to read or write to the drive. If your seconds/read or seconds/write is much higher, you are probably overloading your disks.