Physical Database files:
Password file
It’s a physical file. “ORAPWD” is a command utility to create the file.
To connect the oracle database as DBA, either by password file or OS group which has SYSDBA access.
It assists the DBA with granting SYSDBA and SYSOPER privileges to other users.
The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used.
Sql> show parameter password
SQL> select * from v$pwfile_users;
The only time the password file is accessed is if someone logs on ‘as sysdba’.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret
Parameter files
It’s a physical file.
Without parameter files, we cannot start the database.
Whenever, you are planning to change the parameter files. First check which one it’s using it and take a copy of it both physical as well as oracle.
Types 1. Pfile 2. SPfile
Difference:
| Pfile | Spfile (9i Onwards) |
| Text file | Binary file |
| We can view | Cannot view |
| Needs a DB reboot | No reboot required |
| Names: init.ora (or) init<SID>.ora | Names: spfile.ora (or) spfile<SID>.ora |
| Location: $ORACLE_HOME/dbs | Location: $ORACLE_HOME/dbs |
| %ORACLE_HOME%\database | |
| SPFILE can be backed-up with RMAN | |
| Reduce human errors. The SPFILE is maintained by the server. |
Any changes to either files will not reflect, we need to manually change to match this, a reboot required. Spile we can reboot later, but pfile needs an immediate shutdown.
The reason for the SPfile features, to minimize the changes and downtime, to make sure all are correct before we reflect with Pfile.
For some parameter does not need a reboot and we can specify the “SCOPE can be set to MEMORY, SPFILE, or BOTH”. Memory – no reboot required.
Memory – is a temporary one, next reboot it will use the old. This is the default behavior if a PFILE was used at STARTUP.
SPfile –SPFILE, the parameter will take effect with next database startup.
Both – affect the current instance and persist to the SPFILE. This is the default behavior if an SPFILE was used at STARTUP.
How to change the parameter values using SPfile.
Show parameter shared_pool_size;
Alter system set shared_pool_size = 700m scope = spfile;
How to take this changes into Pfile
Create pfile from spile; — It will create a file in the default location
You can also, create your desired location.
Create pfile =’/opt/init_test1_DB.ora’from spile; — To non-default location
How to read the file
$ cat OH/ init_test1_DB.ora
What are the main parameters are available here.
There are many parameters are available in the Pfile. You can get that from following command.
Show parameters;
The main parameters to start the database
COMPATIBLE =11.2.0.4.0
Db_name=’test1’
CONTROL_FILES
db_block_size
Undo_managment = ‘auto’
Undo_tablespace=undotbl1
How to change the parameter values using Pfile
$ vi OH/ init_test1_DB.ora
Create spfile from pfile =’ OH/ init_test1_DB.ora’; — This will sent an error, since it’s in use
From defalt location –Create spfile from pfile
In case, if it uses a spfile:
create pfile from spfile
edit pfile
shutdown instance
startup with pfile
create spfile from pfile
Shutdown & startup
By defaut oracle will start from SPfile.
How to start from pfile
Startup pfile =’/opt/init.ora’
How to know the server is using SPfile or Pfile
Show parameter spfile; – if the column is emty it’s using pfile.
SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type”
FROM sys.v_$parameter WHERE name = ‘spfile’;
Best practice , to have more pfiles.
How find the database parameters
Select * from v$parameters;
Control files
It’s a physical file. Also, a binary file. It defines the current state of the physical database..
It’s a heart of the database
A database needs a minimum 1 control file and maximum 8 control files. The information will be same as all the control files. By default two files will be created.
If the database has more than 1 control file called “multiplexing”
How to recover the control files
— Recover from multiplexing
(Even if you have multiple control files, any of the files corrupted, the database will be terminated)
— From backup
— You can write the script, but need to know of content i.e. data file location etc…
REUSE in CREATE CONTROLFILE is to specify that existing controlfiles are to be overwritten.
SET in CREATE CONTROLFILE is to specify that the DB_NAME is being changed from whatever is the existing name in datafile headers.
They are two different clauses for two different purposes. It is not an “either/or” decision.
From what I understand, alter database resetlogs is always required when:
- restoring from a backup controlfile (it does not have information about latest SCN)
-
incomplete recovery (you cannot use current redologs or redo logs are unavailable)
-
DB point in time recovery (relies on archivelogs to roll DB forward).
When opening database with resetlogs, you reset the SCN and start a new database incarnation.
Oracle uses noresetlogs when your database crashed and all files are available – this is automatic recover. You can also use noresetlogs when you restore a database using a backup controlfile from trace and all datafiles including online redologs are valid and the DB was shutdown properly (rare case).
In most cases when you restore and recover a databaes you use resetlogs, because the current onlline redo logfiles cannot be part of a backup.
A “SET DATABASE” clause in the CREATE CONTROLFILE statement is a clause used to rename a database. (Even if you have SET it back to the same name !). This requires that the Redo Logs be initialized. That is why the RESETLOGS is mandatory for a CREATE CONTROLFILE SET DATABASE.
(and it follows that the RESETLOGS will be required when doing the subsequent ALTER DATABASE OPEN).
Note : IF you are NOT planning to rename the database, you should not use the SET DATABASE clause in the CREATE CONTROLFILE statement.
How to control control file
Maxinstance 1
Maxdatafiles 1600
Maxlogfiles 5 – Redo log
Maxlogmember 3 – Member of redo log
Maxloghistory 65000 – Archive log
What is the content of the control file
The database name
Names and locations of associated datafiles and redo log files
The timestamp of the database creation
The current log sequence number
Checkpoint information
Archive log mode info
SCN info
RMAN backup info
Control file multiplexing using SPfile
Down time needed, You need to mention all existing control file with the new file.
Select name from v$controlfile;
Alter system set control_files = ‘ All existing files’, ‘new location ’ scope = spfile;
Shutdown immediate
$ cd /opt/controlfile
$ Ls
$cp old file new file
Startup
Select name from v$controlfile;
You need to manually copy this files.
Control file multiplexing using Pfile
Create pfile = ‘pfile Existing location’ from spfile;
$vi /opt/inittest.ora
Add the control file info manually and save it. (,/opt/03.ctl)
Shutdown immediate
$ cd /opt/controlfile
$cp old file new file
$ls
Startup pfile =’/opt/init.ora’
Select name from v$controlfile;
Create spfile from pfile ’/opt/init.ora’;
Shutdown immediate
Startup
Best practice, always database needs to run using SPfile.
How to view the content of the control file
Select * from v$controlfile_record_section;
Backup the Control files
ALTER DATABASE BACKUP CONTROLFILE TO ‘/tmp/control.bkp’; — binary backup
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/opt/create_control.sql’ — script backup
Redo log files
It’s an online backup of the database. All the changes are stored and written here.
A database needs minimum 2 redo log files. Maximum 5 redo log groups, we can create.
The reason for the two files, in case one full oracle will use next.
Once, the log 1 is full and its move to the next it called “log switch”.
The log switch can happen from log sequence number. Every redo log file has a low LSN and High LSN.
The first number is low LSN and end of the log file last number is high LSN. The log writer will write those info. When it moves to next redo log file it will take a previous high LSN and start the same number from the current redo logs. Like (1 to 10) (10 to 20)
Redo log status
Current
Active
Inactive
Unused
Current
The current redo log used by the LGWR. Its’s a current redo online log, cannont be droped and renamed/relocated.
In case if it gets corrupted, only incomplete recovery is possible. (With data loss).
Active
One, the LGWR completed the task and moved to the next log file , it will mark as active.
Redo log files that are required for instance recovery are called active redo log files.
Inactive
Once, the redo log file data moved to Archive file, it’s marked as inactive.
Redo log files that are no longer required for instance recovery are called inactive redo log files.
Unused
A newly created file and LGWR cannot started.
Redo logs multiplexing
A group can have 3 members. In a single group the members are having same data.
Example: G1 (M1 & M2) G2(M3,M4). It will be used for high availabilities.
How to know the member belongs to, which group and how many
Select * from v$logfile;
To find the redo log file size
Select * from v$log;
Create a new group with file & add a new log file
Alter database add logfile ‘/opt/redo03.log’ size 100m;
Alter database add logfile group 3 ‘/opt/redo03.log’ size 100m;
group 3 – Optional
Multiplexing redo log file
Alter database add logfile member ‘/opt/redo03_1.log’ to group 3;
5 groups maximum possible.
How to clear/ flush a member
Alter database clear logfile member ‘/opt/redo03.log’ ;
How to flush a group
Alter database clear logfile group 3;
The flush will be mostly used for shutdown and startup issues.
How to drop a member & log file
Alter database drop file member ‘/opt/redo03.log’;
Alter database drop logfile ‘/opt/redo03.log’;
How to drop a group
Alter database drop logfile group 3;
How to resize he redo log file
An existing redo log file cannot be resized.
An alternate solution: Create a new file with the size and drop the existing one in the inactive state.
How to change the redo log file into inactive
Alter system switch logfile;
How to find the number of LSN
Select * from v$log_history;
Archive log files
It’s an offline backup of redo log files.
For every log switch the archive log will be generated. You can choose automatic or manual archiving.
The used file size will come to archive log location (Example log size 100 g, but used 10g)
It’s an offline copy and you can do anything (copy, cut & delete), but before delete we need to back up the files. Since, it’s used for recovery purpose.
An archived redo log file is a copy of one of the filled members of a redo log group. Since, both has same data.
The default archive has two processes. You can add more LOG_ARCHIVE_MAX_PROCESSES. Oracle will increase it whenever needed, you can also limit this.
You can choose whether to archive redo logs to a single destination or multiplex them.
LOG_ARCHIVE_DEST_n – Local or remote
You can specify the location as local as a directory or service. Service is for standby database.
LOG_ARCHIVE_DEST & LOG_ARCHIVE_DUPLEX_DEST — Local only
To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. All locations must be local.
Mode 1. Archive 2. No Archive
| Archive | No Archive |
| Archive files will be generated | No files |
| We can recover data without any loss | There will be a data loss |
How to find the database archive mode
Select log_mode from v$database;
Archive log list;
Results
Archive mode : no archive
Automatice Archive : disable
Dest : online_recovery_area – Destination of log
OLN :72 –old LSN
CLN :72 – Current LSN
NLN :72 – Next LSN
We need to enable the archive process to place archive mode.
Archive start/stop;
How to make it permanent – From 10g onwards no need to enable the Archive process
Alter system set log_archive_start =true/false =scope = spfile;
Change the database into Archive mode:
Shutdown immediate
Startup mount
Alter database archivelog/ no archivelog;
Alter database open;
Where the Archive log will generate
9i Lunix – $ORACLE_HOME/dbs
9i Windows – $ORACLE_HOME%database
10g & 11g – Both Lunix and windows
$ORACLE_BASE/flash_recovry_area
Change the Archive log location from default to other FS
$ mkdir –p /Archive/testDB
alter system set log_archive_dest_1= ‘location=/opt/Archive/testdb1’ SCOPE=spfile;
scope – optional
Archive Full error: ORA-257. If the archive location is full DB will hang. We need to backup the logs and delete.
For emergency, We can point the archive log to a different location and can free up.
Alter system set log_archive_dest= ’Different location’;
We can change back to the original location. But, always need to backup the logs whatever location it has been running.
How to get the number of archive log files
select * from v$archived_log;
Understanding Archive Destination Status
Valid/Invalid – indicates whether the disk location or service name information is specified and valid
Enabled/Disabled – indicates the availability state of the location and whether the database can use the destination
Active/Inactive – indicates whether there was a problem accessing the destination
select * from V$ARCHIVE_DEST;
