Control File
Every oracle database has a control file.A control file is a small binary file that records the physical structure of the database .control file read only in mount stage.
Min 1 or Max 8 control for single database.control file we use for recovery purpose.
Control File Contains:
- The Database name
- Name and locations of associated datafiles and online redo log files
- The timestamp of the database creation
- It maintain current log sequence number (lsn)
- Kept information
- Backup information
- Archive log and redolog information
There are two ways to backup control file
i. Multiplexing control file
ii. Trace
Multiplexing control file
1. Pfile(offline)
2. Spfile(online)
Multiplexing control files using spfile
Step 1: Check the spfile parameter (show parameter spfile)
Step 2: Check the control file parameter (show parameter control or select name from v$controlfile)
Step 3: Add control file by using spfile
alter system set control_files=’/u01/app/oracle/oradata/mac/control01.ctl, ‘/u01/app/oracle/fast_recovery_area/mac/control02.ctl’,’/u01/app/oracle/oradata/mac/control03.ctl’scope=spfile
Step 4: Shut down the database
Shut immediate
Step 5: Copy the control file to more locations using operating system command
cd /u01/app/oracle/oradata/mac
Cp control01.ctl control02.ctl
Step 6: Start the database and check the control file parameter
Startup
Show parameter control
Multiplexing control file using the pfile(initmac.ora)
Step 1: First copy pfile from spfile and shut down the database
Shut immediate
Step 2: Open the pfile(initmac.ora)
Step 3: Change the initialization parameter to include new file in the paramater
Vi initmac.ora
control_files=’/u01/app/oracle/oradata/mac/control01.ctl’,’/u01/app/oracle/fast_recovery_area/mac/control02.ctl’,’/u01/app/oracle/oradata/mac/control03.ctl’,’/u01/app/oracle/oradata/mac/control04.ctl’
Step 4: Copy the control file to more locations using operating system command
cd /u01/app/oracle/oradata/mac
Cp control01.ctl control02.ctl
Step 5: Start the database using pfile
Startup
pfile=’/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initmac.ora’
Archive log
Archive log mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you recover to any point in time.
Changing the database archive log to no archive log
Step 1: First the check the archive enable or not
“archive log list” or “select log_mode from v$database;”
Step 2: Shut down the database and must startup with mount stage
“Shut immediate”
“Startup mount”
Step 3: Change the database no archiving mode. Then open the database for normal operations.
“Alter database no archivelog;”
“Alter database open”
Step 4: Check the database if its no archive log change or not
Changing the database no archive log to archive log
Step 1: Shut down the database and must start up with mount stage
Step 2: Change the database archiving mode. Then open the database for normal operations.
“Alter database archivelog;”
“Alter database open”
Step 3: Check the database if its archive log enable or not
Temporary tablespace
Step 1: Create a temporary tablespace and specify the path
(create temporary tablespace sdbt1 tempfile ’/home/oracle/sdbt1.dbf’ size 20m)
Step 2: Check free space for the created temporary tablespace
(select tablespace_name,tablespace_size,free_space from dba_temp_free_space)
Step 3: Set the created temporary tablespace as the default temp tablespace of the database
(alter database default temporary tablepsace sdbt1)
Step 4: Resize the temporary tablespace
(Alter database tempfile ‘/home/oracle/sdbt1.dbf autoextend on)
Step 5: Check if the temporary tablespace has been resized
Step 6: Change the status of the temp tablespace to offline mode
Leave A Comment