Oracle

Comments of a database system

- Users
- Database Application
- Database Management System(DBMS)
- Database

Objectives

PFILE vs SPFILE Go to lab01

In Oracle databases, PFILE (Parameter File) and SPFILE (Server Parameter File) are both used to store initialization parameters that define the configuration of the Oracle instance. Here are the key differences between them:

1. PFILE (Parameter File)

• Format: PFILE is a plain text file that contains parameter settings.
• Manual Editing: It can be manually edited using a text editor.
• Static Parameters: PFILE is read during instance startup, and any changes require a restart of the Oracle instance to take effect.
• Location: The default name is initSID.ora (e.g., initORCL.ora), and it's typically located in $ORACLE_HOME/dbs (Unix) or %ORACLE_HOME%\database (Windows) directories.
• Usage: When starting the database with a PFILE, Oracle reads the parameters only at startup and will not persist any parameter changes dynamically.

2. SPFILE (Server Parameter File)

• Format: SPFILE is a binary file that stores initialization parameters.
• Dynamic Parameters: SPFILE allows dynamic changes to parameters while the database is running, and those changes can persist across restarts.
• Automatic Changes: You can use SQL commands (ALTER SYSTEM SET) to modify parameters, and these changes are automatically saved in the SPFILE.
• Location: The default name is spfileSID.ora (e.g., spfileORCL.ora), typically located in $ORACLE_HOME/dbs (Unix) or %ORACLE_HOME%\database (Windows).
• Usage: When using an SPFILE, Oracle can make dynamic changes to many parameters without requiring a restart, and these changes will be retained even after restarting the instance.

3. Key Differences

Feature	PFILE	SPFILE
Format	Plain text file	Binary file
Editing	Manual editing	Modified through Oracle commands
Dynamic Changes	Not supported	Supported, using ALTER SYSTEM
Persistent Changes	Require a restart to take effect	No restart required, changes persist
Location	$ORACLE_HOME/dbs/initSID.ora	$ORACLE_HOME/dbs/spfileSID.ora

4. Conversion Commands

• Convert PFILE to SPFILE:

    CREATE SPFILE FROM PFILE;

Oracle Database Server

instance : Memoery ,

Database files

    desc dba_data_files;
    select file_name from dba_data_files;
    select name from v$datafile;

    select v1.name location from v$datafile v1 join v$tablespace v2 on v1.ts#=v2.ts#
    where v2.name='SYSTEM';

    select v1.name location from v$datafile v1 join v$tablespace v2 on v1.ts#=v2.ts#
    where v2.name='USERS';

Dynamic Views

Solve probmes:

1. ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode solution: Switch the Database to ARCHIVELOG Mode

   1. Start the database in mount mode:

   ```sql

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   

   ```

   1. Enable ARCHIVELOG mode:

   ```sql

   ALTER DATABASE ARCHIVELOG;
   

   ```

   1. Open the database:

   ```sql

   ALTER DATABASE OPEN;
   

   ```

   1. Verify the ARCHIVELOG mode is enabled:

   ```sql

   ARCHIVE LOG LIST;
   

   ```

   1. Now, you can perform online backups using RMAN:

   ```sql

   rman target /
   RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
   

   ```

---