Oracle Server parameter file maintenance skills

In Oracle databases, this initialization parameter file is divided into two types: Text parameter files and server parameter files. Their content is the same, but the representation is different. The server parameter files are saved in binary format. You can use database commands to convert them. Next, I will talk about some tips for maintaining server parameter files.

1. Specify the parameter file when starting the database.

When starting a database, you must provide an accurate initialization parameter file, whether it is a text parameter file or a server parameter file. When using the Startup command to start a database, you can specify the server parameter file that it uses. However, you need to pay attention to the details here, that is, different initialization parameter files use different keywords. If a server parameter file is used, use SPfile. If a text parameter file is used, use the Pfile keyword. The database system does not automatically judge based on the parameter file category, but based on this keyword. If the initialization parameter file is correct, a keyword error may mislead the database to start the process, resulting in a database startup error.

If the initialization parameter file is not specified when the command startup is used to start the database (for example, the database is automatically started with the operating system, the database system will automatically search for the database at the default location. Generally, it first checks whether there is a server parameter file (binary file). If yes, it is enabled. If no parameter file exists, the system continues searching for whether the text parameter file exists. If it is enabled, if it is not, the user will be prompted that the startup fails. Therefore, server parameter files are preferred by default. If the database administrator wants to enable the database to use a text parameter file when it starts automatically, delete the server parameter file after shutting down the database, or move it to another location. Then, the text parameter file is automatically used when the initialization parameter file is not specified at the next restart. For the database system, the use of server parameter files is still text parameter files, there is no big difference. You only need to ensure that their content is accurate. Therefore, it is better to choose which parameter file to use because of the work preferences of the database administrator.

Ii. Maintenance of server parameter files.

You can also use a text editor to open a server parameter file in binary format and view related content. However, you must note that this cannot be changed here. That is to say, you cannot manually change the server parameter file. Because binary files and text files are stored in different ways. If you make changes directly here, the initialization parameter file will be corrupted, and the database will not start properly. To change the server parameter file, it is best to convert it to a text parameter file and then change it. After the modification is complete, start the database directly using the text parameter file or convert it to the server parameter file and then start the database system.

In addition, you can directly make changes using commands. Note that the initialization parameters stored in the server parameter file are permanently retained. That is to say, any changes made to parameters through commands during database operation will be saved in the initialization file of the server after the database is closed. Therefore, the Database Administrator does not need to worry about the loss of parameters after the database is restarted.

3. Export the server parameter file.

After the database is deployed or the initialization parameter file is modified, the Administrator must back up the parameter file. Whether it is a server parameter file or a text parameter file, this backup is inevitable. Because no database administrator can guarantee that the parameter file will be damaged in the future. Backup of server parameter files or text parameter files is an important measure to improve database security. In addition, you need to export the database server parameter file when troubleshooting database faults. After exporting this file, you can use the text editor to conveniently query all initialization parameters to analyze the cause of the failure. Although you can also view these initialization parameters in Database SQL * PLUS tools, the operation is not very convenient. Therefore, many administrators prefer to import them into text files for analysis. Finally, modify the server parameter file. Because the binary server parameter file cannot be directly modified using a text editor, the database administrator usually exports the server parameter file, then, use the commands provided by the database to convert them into text files. Finally, convert it to the server parameter file and start it with it. Sometimes, the Administrator will export the server parameter file before re-deploying the database, so that the server parameter file can be reused to start the database.

When exporting Database Server parameter files, pay attention to the following points.

First, you must have relevant permissions. According to the requirements of the Oracle database, if you want to export the database server parameter file, you must have the database's SYSDBA or SYSOPER permissions. If you do not have the same permissions, an error message "insufficient Permissions" appears when exporting server parameter files using commands.

Second, you can directly use the create pfile from spfile command to export the server parameter file as a text file. During export, you do not need to disable the original routine. If the Directory and name are not specified, the database will be stored in different directories. Pay attention to this name. Because different operating system platforms use different names. That is, if the export file name is not specified, the database uses the platform-specific name and is created based on the platform-specific default server parameter file. For convenience, you can specify the storage path in the command. To specify a path, use = and single quotation marks. For example, pfile = 'pathname' is used.

Third, during the export process, the server will also export some line comments from the original binary file to the text file to facilitate the Administrator to skip reading. Similarly, when converting a text file to a binary server parameter file, the line comment is also stored in the binary file. However, it only saves row comments instead of other comments. In this case, when the binary server parameter file is converted to a text parameter file, other comments will be lost and only row comments will be saved. Therefore, it is best to use line comment when writing comments in the initialization parameter file. Only this line comment will be permanently retained during the conversion process.

4. Create a server parameter file.

When the server parameter file is damaged and data cannot be started, you may need to recreate the server parameter file. I have already stressed several times that server parameter files cannot be edited and created manually. Therefore, you must create a server parameter file using the text parameter file. In order to remedy the damage to the server parameter file, it is best to back up the server parameter file or export it as a text parameter file at ordinary times. Then, if necessary, create the server parameter file based on the text parameter file. Although you can manually create text parameter files, this work is too complicated and cannot be completed easily. If you want to make minor changes to it, but want to create it from scratch, but a huge project is basically an impossible task to complete.

To convert a text parameter file to a server parameter file, run the "Create Spfile ='' from pfile = ''" Command provided by the database. If the name and path of the server parameter file are not specified, the xiotng database uses the platform-specific name and stores it in the default location of the database. Then the database administrator can use the Startup command to start the instance. When it is saved in the default location, you can enable this server parameter file without adding a parameter. By default, this server parameter file has a higher priority than the text parameter file.

The last thing to note is that even in a text parameter file, the database administrator still needs some skills to understand the content in this file. For this reason, the Administrator is advised to add comments if the system's default parameters are changed in the future to improve the readability of the text parameter file. In this case, it is easier to use this file to troubleshoot and improve database performance, which is conducive to improving the readability of this text parameter file.

1. Effect of disk sorting on Oracle database performance (1)
2. Prospective role in Oracle Database Maintenance
3. How to check Oracle availability and tablespace capacity
4. Optimize Oracle tablespace design to improve database performance
5. Use Resource Manager to optimize Oracle Performance