How to create server parameter file (spfile)
Oracle has, with the release of Oracle9i Database server, introduced a new type of parameter file called spfile (server parameter file). The spfile differs from the text based pfile in that it is in binary format. It is loaded into memory during instance initialization and read from there during startup. It remains in memory for as long as the instance is started. Oracle writes to and reads from this binary file during Instance startup and at runtime. The advantages of using spfile is that it is globally persistent across database startups.
Although the spfile can be modified dynamically by using the ALTER SYSTEM statement with the SCOPE option, it is not possible to edit non-dynamic parameters such as the LOG_ARCHIVE_START parameters because these are applied during Instance startup and remain permanent.
To change the LOG_ARCHIVE_START parameter, the entire content of the spfile has to be exported to a pfile, edited in the pfile and imported back into the spfile.
Exporting the spfile to a text file is possible using the CREATE PFILE command. This command will create a pfile with all the initialization parameters from the current spfile of the database.
SQL> CREATE PFILE='C:\init.ora' FROM SPFILE;
File created.
Enable Archive mode by setting the string value LOG_ARCHIVE_START to TRUE. Save the file and restart the database using this pfile. Recreate the spfile, after which the database will have to be restarted using the new spfile.
SQL> SHUTDOWN;
Database closed.
Database dismounted.
ORACLE instance shut down.
The pfile is created in the path specified in the PFILE option.
SQL> STARTUP PFILE='C:\init.ora'
ORACLE instance started.
Total System Global Area 63729356 bytes
Fixed Size 282316 bytes
Variable Size 46137344 bytes
Database Buffers 16777216 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
Create a new spfile from the pfile (it is created in the default location).
SQL> CREATE SPFILE FROM PFILE='C:\init.ora';
File created.
Restart the database to start using the newly created spfile.
SQL> STARTUP FORCE
ORACLE instance started.
Total System Global Area 63729356 bytes
Fixed Size 282316 bytes
Variable Size 46137344 bytes
Database Buffers 16777216 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
============================================================================
How to put an Oracle 9i and 10g database in Automatic Archive Log Mode using Oracle Enterprise Manager Console & SQL Plus
--------------------------------------------------------------------------------
Details:
For putting an Oracle Database in Automatic Archival Mode, the parameter log_archive_start must be set to True in the initialization parameter file of the Oracle database in question. The following method can be useful for the users that prefer the GUI to the command line.
Please follow the following steps to enable the automatic archival option of an Oracle database:
1. After starting the Oracle Enterprise Manager Console, connect to the Oracle database in question as SYSDBA (Figure 1). If it is not displayed in the left pane in the Oracle Enterprise Manager Console, then the required database should be added.
Figure 1
2. In the left pane, expand the Oracle database in question till Network | Databases | | Instance | Configuration (Figure 2).
Figure 2
3. Now in the right pane, under General, click the All Initialization Parameters button (Figure 2). (This step is given assuming that the archive log mode and automatic archival options have already been selected and applied on the Recovery tab in the right pane in the above image.)
4. Now select SPFile under the All Parameters section in the Edit Database: Configuration dialog box (Figure 3). Then search for the parameter log_archive_start under the parameter name and set its value to TRUE. Then click on Apply.
Figure 3
5. Click Yes to restart the database which will apply the changes (Figure 4).
Figure 4
6. Then respond Yes again to continue with the restart of the instance (Figure 5).
Figure 5
7. Click OK to restart the database with the Automatic Archival option enabled (Figure 6).
Figure 6
To check if the Automatic Archival option has been enabled or not, either
- Use SQL Plus and run the command archive log list after connecting to the respective database as SYSDBA
Or
- Run the Database Configuration Report through the Oracle Enterprise Manager Console by doing the following:
1. In the left pane, browse down till Network | Databases | | Instance | Configuration
2. Click on the first icon of Create Report in the toolbar on the left pane (Figure 7)
Figure 7
3. In the Database Configuration Report dialog box, just select the checkbox for Instance and then click the View button to see the report
4. In the Database Configuration Report, browse down and check the value shown next to the parameter log_archive_start that should be shown TRUE
ORACLE 10G (10.1) :
Starting with Oracle Database 10g release 10.1, the LOG_ARCHIVE_START initialization parameter has been deprecated. Archiving is now automatically started when the database is placed in ARCHIVELOG mode.
To enable ARCHIVELOG mode in Oracle 10g (10.1):
1. Check the Archive Log Mode (Figure 1)
Figure 1:
2. To switch the database to Archive Log Mode .
Access the Enterprise Manager and Login as SYS as shown in Figure 2 .
Figure 2:
3. If its the first time logging in, then a prompt for the licensing information will appear; Click "I Agree" to continue (Figure 3)
Figure 3:
4. On the Homepage, various information about the database and instance can be accessed (Figure 4) Archiving is disabled .
Figure 4:
5. Click "Maintenance" to go to the Maintenance Page. Click on "Configure Recovery Settings" (Figure 5)
Figure 5:
6. In the Configure Recovery Settings Page "check" the box "ARCHIVELOG Mode", accept the other default values and Click "Apply" (Figure 6)
Figure 6:
7. When prompted to restart the database to reflect the changes Click "Yes" to continue (Figure 7)
Figure 7:
8. Fill in the required OS and DB Credentials to restart the database and click "OK" (Figure 8)
Figure 8:
9. Click "OK" to confirm the restart (Figure 9)
Figure 9:
10. Database restarting will now show. Wait a few minutes and then click "Refresh" (Figure 10)
Figure 10:
11. Database restarted Archiving has been enabled will appear now (Figure 11)
Figure 11:
12. Confirm the Archive Log Mode Setting (Figure 12)
Figure 12:
If the database is not in Archive Log Mode after performing the steps above, then the commands listed below can be used to enable Archive Log Mode:
Note: In order to perform these steps the database will have to be Shut Down. Make sure to first get approval prior to performing these actions as they may affect a Production Oracle Instance.
1) Log Into SQL Plus.
2) SELECT log_mode FROM gv$database;
*This shows the database is in Non Archive Log Mode.
3) SHUTDOWN;
*This shuts down the database.
4) STARTUP MOUNT EXCLUSIVE;
*This mounts the database.
5) ALTER DATABASE ARCHIVELOG;
*This puts the database in Archive Log mode.
6) ALTER DATABASE OPEN;
*This puts the database in an Open State.
7) Next, execute the following command:
Select log_mode FROM gv$database;
This will show that the database is in Archive Log Mode, in a Mounted (Open ) State, and can be backed up with Backup Exec for Windows Servers.
Related Documents:
247294: How to put an Oracle 8i database in Archive Log Mode using Oracle DBA Studio
http://support.veritas.com/docs/247294
Although the spfile can be modified dynamically by using the ALTER SYSTEM statement with the SCOPE option, it is not possible to edit non-dynamic parameters such as the LOG_ARCHIVE_START parameters because these are applied during Instance startup and remain permanent.
To change the LOG_ARCHIVE_START parameter, the entire content of the spfile has to be exported to a pfile, edited in the pfile and imported back into the spfile.
Exporting the spfile to a text file is possible using the CREATE PFILE command. This command will create a pfile with all the initialization parameters from the current spfile of the database.
SQL> CREATE PFILE='C:\init.ora' FROM SPFILE;
File created.
Enable Archive mode by setting the string value LOG_ARCHIVE_START to TRUE. Save the file and restart the database using this pfile. Recreate the spfile, after which the database will have to be restarted using the new spfile.
SQL> SHUTDOWN;
Database closed.
Database dismounted.
ORACLE instance shut down.
The pfile is created in the path specified in the PFILE option.
SQL> STARTUP PFILE='C:\init.ora'
ORACLE instance started.
Total System Global Area 63729356 bytes
Fixed Size 282316 bytes
Variable Size 46137344 bytes
Database Buffers 16777216 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
Create a new spfile from the pfile (it is created in the default location).
SQL> CREATE SPFILE FROM PFILE='C:\init.ora';
File created.
Restart the database to start using the newly created spfile.
SQL> STARTUP FORCE
ORACLE instance started.
Total System Global Area 63729356 bytes
Fixed Size 282316 bytes
Variable Size 46137344 bytes
Database Buffers 16777216 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
============================================================================
How to put an Oracle 9i and 10g database in Automatic Archive Log Mode using Oracle Enterprise Manager Console & SQL Plus
--------------------------------------------------------------------------------
Details:
For putting an Oracle Database in Automatic Archival Mode, the parameter log_archive_start must be set to True in the initialization parameter file of the Oracle database in question. The following method can be useful for the users that prefer the GUI to the command line.
Please follow the following steps to enable the automatic archival option of an Oracle database:
1. After starting the Oracle Enterprise Manager Console, connect to the Oracle database in question as SYSDBA (Figure 1). If it is not displayed in the left pane in the Oracle Enterprise Manager Console, then the required database should be added.
Figure 1
2. In the left pane, expand the Oracle database in question till Network | Databases |
Figure 2
3. Now in the right pane, under General, click the All Initialization Parameters button (Figure 2). (This step is given assuming that the archive log mode and automatic archival options have already been selected and applied on the Recovery tab in the right pane in the above image.)
4. Now select SPFile under the All Parameters section in the Edit Database: Configuration dialog box (Figure 3). Then search for the parameter log_archive_start under the parameter name and set its value to TRUE. Then click on Apply.
Figure 3
5. Click Yes to restart the database which will apply the changes (Figure 4).
Figure 4
6. Then respond Yes again to continue with the restart of the instance (Figure 5).
Figure 5
7. Click OK to restart the database with the Automatic Archival option enabled (Figure 6).
Figure 6
To check if the Automatic Archival option has been enabled or not, either
- Use SQL Plus and run the command archive log list after connecting to the respective database as SYSDBA
Or
- Run the Database Configuration Report through the Oracle Enterprise Manager Console by doing the following:
1. In the left pane, browse down till Network | Databases |
2. Click on the first icon of Create Report in the toolbar on the left pane (Figure 7)
Figure 7
3. In the Database Configuration Report dialog box, just select the checkbox for Instance and then click the View button to see the report
4. In the Database Configuration Report, browse down and check the value shown next to the parameter log_archive_start that should be shown TRUE
ORACLE 10G (10.1) :
Starting with Oracle Database 10g release 10.1, the LOG_ARCHIVE_START initialization parameter has been deprecated. Archiving is now automatically started when the database is placed in ARCHIVELOG mode.
To enable ARCHIVELOG mode in Oracle 10g (10.1):
1. Check the Archive Log Mode (Figure 1)
Figure 1:
2. To switch the database to Archive Log Mode .
Access the Enterprise Manager and Login as SYS as shown in Figure 2 .
Figure 2:
3. If its the first time logging in, then a prompt for the licensing information will appear; Click "I Agree" to continue (Figure 3)
Figure 3:
4. On the Homepage, various information about the database and instance can be accessed (Figure 4) Archiving is disabled .
Figure 4:
5. Click "Maintenance" to go to the Maintenance Page. Click on "Configure Recovery Settings" (Figure 5)
Figure 5:
6. In the Configure Recovery Settings Page "check" the box "ARCHIVELOG Mode", accept the other default values and Click "Apply" (Figure 6)
Figure 6:
7. When prompted to restart the database to reflect the changes Click "Yes" to continue (Figure 7)
Figure 7:
8. Fill in the required OS and DB Credentials to restart the database and click "OK" (Figure 8)
Figure 8:
9. Click "OK" to confirm the restart (Figure 9)
Figure 9:
10. Database restarting will now show. Wait a few minutes and then click "Refresh" (Figure 10)
Figure 10:
11. Database restarted Archiving has been enabled will appear now (Figure 11)
Figure 11:
12. Confirm the Archive Log Mode Setting (Figure 12)
Figure 12:
If the database is not in Archive Log Mode after performing the steps above, then the commands listed below can be used to enable Archive Log Mode:
Note: In order to perform these steps the database will have to be Shut Down. Make sure to first get approval prior to performing these actions as they may affect a Production Oracle Instance.
1) Log Into SQL Plus.
2) SELECT log_mode FROM gv$database;
*This shows the database is in Non Archive Log Mode.
3) SHUTDOWN;
*This shuts down the database.
4) STARTUP MOUNT EXCLUSIVE;
*This mounts the database.
5) ALTER DATABASE ARCHIVELOG;
*This puts the database in Archive Log mode.
6) ALTER DATABASE OPEN;
*This puts the database in an Open State.
7) Next, execute the following command:
Select log_mode FROM gv$database;
This will show that the database is in Archive Log Mode, in a Mounted (Open ) State, and can be backed up with Backup Exec for Windows Servers.
Related Documents:
247294: How to put an Oracle 8i database in Archive Log Mode using Oracle DBA Studio
http://support.veritas.com/docs/247294
Comments