Friday 23 May 2014
Difference between oracle SID and Oracle service name
This summary is not available. Please
click here to view the post.
Thursday 22 May 2014
Restricted mode of instance
What is Restricted Mode of Instance Startup?
We can start an instance and mount and open a database in restricted mode so that the database is available only to DBAs.
Below are some reasons why we prefer Restricting mode of instance:
While performing export or import of database data(this is optional)
While performing data load (with SQL*Loader)
To Temporarily prevent users from using data
During certain migration and upgrade operations
A normal cannot connect to the database which is in restricted mode ,but it can be made possible if RESTRICTED SESSION system privilege is provided to him.It is not recommended to provided RESTRICTED SESSION system privilege to normal user apart from DBAs.
Starting up an instance in restricted mode by using the command:
STARTUP RESTRICT
SQL> select logins from v$instance; --->to know the status of restricted mode.
Changing the restricted mode to normal mode by using the command:
ALTER SYSTEM DISABLE RESTRICTED SESSION;
cheers...!!!
We can start an instance and mount and open a database in restricted mode so that the database is available only to DBAs.
Below are some reasons why we prefer Restricting mode of instance:
While performing export or import of database data(this is optional)
While performing data load (with SQL*Loader)
To Temporarily prevent users from using data
During certain migration and upgrade operations
A normal cannot connect to the database which is in restricted mode ,but it can be made possible if RESTRICTED SESSION system privilege is provided to him.It is not recommended to provided RESTRICTED SESSION system privilege to normal user apart from DBAs.
Starting up an instance in restricted mode by using the command:
STARTUP RESTRICT
SQL> select logins from v$instance; --->to know the status of restricted mode.
Changing the restricted mode to normal mode by using the command:
ALTER SYSTEM DISABLE RESTRICTED SESSION;
cheers...!!!
Creating backup control file
How to create backup control file?
Control file is a binary file that consists of database structure, RMAN backup information, SCN number etc. But we can backup that file either into readable format or in to regular binary format.
Backing up control file to trace location in binary format:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE; -->the created backup file in this process will be stored in user_dump_dest location with random name
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp'; -->the created backup file in this process will be stored in /oracle/backup/ path with control.bkp name
Backing up control file to trace location in Readable format:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '\u01\dba\my_control.sql'; -->the created backup file in this process will be stored in /u01/dba/ path with my_control.sql file name
We can read the database structure (CRD files, sizes, locations, SCN and RMAN information) in my_control.sql file in text format. This helps us in recreating control file whenever we lost control file from it's location.
ARCHIVELOG mode vs NOARCHIVELOG mode
What is the difference between ARCHIVELOG mode and NOARCHIVELOG
mode?
ARCHIVELOG mode:
ARCHIVELOG mode is a mode where we put database to backup all transactions into logs(archivelogs) ,which help us in recovery scenarios.With the help of archived logs we can recover database till any point of time.
During archivelog mode redo information is written into archive logs using ARCn process.In order to utilize RMAN and HOT backup type of technologies we need our database to be in archivelog mode.
NOARCHIVELOG mode:
NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point of time we need.
NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
Related links: http://orcl11gdba.blogspot.in/2014/03/enabling-archive-log-mode.html
ARCHIVELOG mode:
ARCHIVELOG mode is a mode where we put database to backup all transactions into logs(archivelogs) ,which help us in recovery scenarios.With the help of archived logs we can recover database till any point of time.
During archivelog mode redo information is written into archive logs using ARCn process.In order to utilize RMAN and HOT backup type of technologies we need our database to be in archivelog mode.
NOARCHIVELOG mode:
NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point of time we need.
NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
Related links: http://orcl11gdba.blogspot.in/2014/03/enabling-archive-log-mode.html
Differences between COLD and HOT backups
What are the
differences between COLD and HOT backup? And the benefits associated with each?
S.NO
|
HOT backup
|
COLD backup
|
1
|
HOT backup is taken while database
is in up and running state
|
COLD backup is taken while the
database is in graceful shut down state
|
2
|
For HOT backup database must be in
archive log mode
|
For COLD backup database need not
be in archive log mode
|
3
|
Using HOT backup we can recover
the database till any point in case of disasters
|
Using COLD backup we can recover
the database till the point where we took backup only. Later changes will be
unavailable
|
4
|
Excessive redo is generated during
HOT backup
|
Since the database need not be in
archive log mode we can gain slight performance
|
5
|
It is online backup
|
It is offline backup
|
6
|
HOT backup is inconsistent backup
|
It is Consistent backup
|
7
|
Since HOT backup don’t need
downtime we can use this for a database that performs 24*7 operations
|
Since COLD backup needs downtime
we can’t afford to use it for a database that performs 24*7 operations
|
8
|
Using HOT backup we will take
Control, Redo (optional), Data files for backup.
|
During COLD backup we will take
Control, Redo (optional), Data files for backup same as hot backup.
|
Related links:
Taking COLD backup: http://orcl11gdba.blogspot.in/2014/04/recovering-database-using-cold-backup.html
Tanking HOT backup: http://orcl11gdba.blogspot.in/2014/03/taking-hot-bakcup-database-in-begin.html
Tuesday 20 May 2014
Taking database backup using RMAN method
What is RMAN? How to take database backup using RMAN?
RMAN:
Recovery Manager is a tool that manages the process of creating backup and also manages the process of restoring and recovering them during disaster situations. In addition to RMAN repository records, the recovery catalog can also hold RMAN stored scripts, sequences of RMAN commands for common backup tasks. With RMAN we can be 100% sure that our database has been backed up. Moreover we can take check whether the backup files are valid or not. Further we can take incremental backups which results in improvement of performance during backup and recovery time. Backup process can be made faster using Parallel operation. In RMAN backup method no Extra redo generated when backup is taken compared to online backup. With the compression algorithm facility in RMAN we can save space in hard disk. RMAN Maintains repository of backup metadata. RMAN related views store backup locations.RMAN helps us in identifying the files which need to be backed up.
Before taking RMAN backup it is mandatory that the database should be in Archive log mode. Check whether it is enabled or not
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/app/oradata/pavan/ttdtest/flash_recovery_area/TTDTEST/archivelog
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
Let’s see start taking backup with RMAN
rmantarget /
RMAN >BACKUP DATABASE;
RMAN >exit;
The above process is a simple way of taking database backup using RMAN. We are not sure where the backup is taken/what is it's size/what is the format of it? Generally the backup location is either flash recovery area or $ORACLE_HOME/dbs location by default.
-----------------------------------------------------------------------------------------------------------------------
RMAN BACKUP SCRIPT:
The below script is a simple, efficient, compressed algorithm used one. We will learn all RMAN related tags and their purpose with in short time.
RMAN>
v$rman_status is the view that maintains all RMAN backup related information.
RMAN Commands:
We can return any setting to its default value by using CONFIGURE command or we can change the settings .
RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS -----> indicates that we are will be keeping our database backup file for next 14 days. After that it will become obsolete/invalid which is no longer useful for restore and recovery.
CROSSCHECK ARCHIVELOG ALL -----> indicates that it is checking the archive logs whether they are valid or not
CROSSCHECK BACKUP -----> tells us whether the previously taken database backups are valid or not/Check whether backup items still exist
allocate channel -----> this command is to manually assign a channel to back up specified files. An RMAN channel represents one stream of data to a device type.
Type disk ----->indicates backup files are going to take in disk
Format ----->indicates the naming convention of my backup files and their extention with .bck format. it can be any
maxpiecesize 5120m----->indicates that my each backup file should be atmost 5GB of size.
Backup incremental level = 0 ----->indicates that i am taking full backup of database. other than this i can iake incremental,cumulative database backups as per my wish just by replacing 0 with either 1 or 2.
COMPRESSED ----->indicates that the backup should be compressed one.the purpose of using this is it will reduce the backup size.
Tag = full_open_backup_disk ----->A tag is a symbolic name for a backup set. it tells us that it is full backup.
release channel t1 ----->RELEASE CHANNEL command to release a normal or maintenance channel while maintaining a connection to a target database instance.
Allocate channel t3 & release channel t3 ----->channel t3 is initiated and closed for backing up archive logs
backup archivelog until time 'sysdate -1' delete input ----->indicates that it is taking backup of archive logs till before 24 hours and dekleting the files from disk
sql 'alter system archive log current' ----->this indicates a normal sql to take a log switch. the purpose of it is to archive all transactions
backup archivelog all ----->it backs up all the archivelogs till now
backup current controlfile ----->indicates taking backup of control file
delete noprompt obsolete ----->indicates deletion of previous backups(greater than 14 days) without prompting/asking
delete noprompt expired backup ----->indicates deletion of expired(manually deleted in OS level) backups without prompting/asking
delete noprompt expired archivelog all ----->indicates deletion of expired archivelogs without prompting/asking.
in RMAN prompt type show all , then it will display all the settings defined for rman.
The backup we took from above steps are useful for recovery operations in future(with in 14 days).
Happy learning....!!!
RMAN:
Recovery Manager is a tool that manages the process of creating backup and also manages the process of restoring and recovering them during disaster situations. In addition to RMAN repository records, the recovery catalog can also hold RMAN stored scripts, sequences of RMAN commands for common backup tasks. With RMAN we can be 100% sure that our database has been backed up. Moreover we can take check whether the backup files are valid or not. Further we can take incremental backups which results in improvement of performance during backup and recovery time. Backup process can be made faster using Parallel operation. In RMAN backup method no Extra redo generated when backup is taken compared to online backup. With the compression algorithm facility in RMAN we can save space in hard disk. RMAN Maintains repository of backup metadata. RMAN related views store backup locations.RMAN helps us in identifying the files which need to be backed up.
Before taking RMAN backup it is mandatory that the database should be in Archive log mode. Check whether it is enabled or not
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/app/oradata/pavan/ttdtest/flash_recovery_area/TTDTEST/archivelog
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
Let’s see start taking backup with RMAN
rmantarget /
RMAN >BACKUP DATABASE;
RMAN >exit;
The above process is a simple way of taking database backup using RMAN. We are not sure where the backup is taken/what is it's size/what is the format of it? Generally the backup location is either flash recovery area or $ORACLE_HOME/dbs location by default.
-----------------------------------------------------------------------------------------------------------------------
RMAN BACKUP SCRIPT:
The below script is a simple, efficient, compressed algorithm used one. We will learn all RMAN related tags and their purpose with in short time.
RMAN>
run{
CONFIGURE RETENTION POLICY TO
RECOVERY WINDOW OF 14 DAYS;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
allocate channel t1 type disk format
'/u01/ttdtest/flash_recovery_area/TTDTEST/backupset/ORA_rman_full_backup_ttdtest_%U.bck'
maxpiecesize 5120m;
backup incremental level = 0 AS
COMPRESSED BACKUPSET database tag = full_open_backup_disk;
release channel t1;
allocate channel t3 type disk format
'/u01/ttdtest/flash_recovery_area/TTDTEST/backupset/ORA_rman_arch_backup_ttdtest_%U.bck';
backup archivelog until time
'sysdate -1' delete input;
sql
'alter system archive log current';
backup archivelog all;
backup current controlfile format
'/u01/ttdtest/flash_recovery_area/TTDTEST/backupset/ORA_rman_ctrl_backup_ttdtest_%U.bck';
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog
all;
release channel t3;
}
Exit
Now i can see all my files in
u01/ttdtest/flash_recovery_area/TTDTEST/backupset/
location.v$rman_status is the view that maintains all RMAN backup related information.
RMAN Commands:
We can return any setting to its default value by using CONFIGURE command or we can change the settings .
RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS -----> indicates that we are will be keeping our database backup file for next 14 days. After that it will become obsolete/invalid which is no longer useful for restore and recovery.
CROSSCHECK ARCHIVELOG ALL -----> indicates that it is checking the archive logs whether they are valid or not
CROSSCHECK BACKUP -----> tells us whether the previously taken database backups are valid or not/Check whether backup items still exist
allocate channel -----> this command is to manually assign a channel to back up specified files. An RMAN channel represents one stream of data to a device type.
Type disk ----->indicates backup files are going to take in disk
Format ----->indicates the naming convention of my backup files and their extention with .bck format. it can be any
maxpiecesize 5120m----->indicates that my each backup file should be atmost 5GB of size.
Backup incremental level = 0 ----->indicates that i am taking full backup of database. other than this i can iake incremental,cumulative database backups as per my wish just by replacing 0 with either 1 or 2.
COMPRESSED ----->indicates that the backup should be compressed one.the purpose of using this is it will reduce the backup size.
Tag = full_open_backup_disk ----->A tag is a symbolic name for a backup set. it tells us that it is full backup.
release channel t1 ----->RELEASE CHANNEL command to release a normal or maintenance channel while maintaining a connection to a target database instance.
Allocate channel t3 & release channel t3 ----->channel t3 is initiated and closed for backing up archive logs
backup archivelog until time 'sysdate -1' delete input ----->indicates that it is taking backup of archive logs till before 24 hours and dekleting the files from disk
sql 'alter system archive log current' ----->this indicates a normal sql to take a log switch. the purpose of it is to archive all transactions
backup archivelog all ----->it backs up all the archivelogs till now
backup current controlfile ----->indicates taking backup of control file
delete noprompt obsolete ----->indicates deletion of previous backups(greater than 14 days) without prompting/asking
delete noprompt expired backup ----->indicates deletion of expired(manually deleted in OS level) backups without prompting/asking
delete noprompt expired archivelog all ----->indicates deletion of expired archivelogs without prompting/asking.
in RMAN prompt type show all , then it will display all the settings defined for rman.
The backup we took from above steps are useful for recovery operations in future(with in 14 days).
Happy learning....!!!
Subscribe to:
Posts (Atom)