Now ORACLE provides a free Media Management layer(MML) as part of its Oracle Secure Backup server software.
It offers a easy and convinent way to implement a tape backup statergy without using a third party MML where you must pay aditional licence fees.
If you have a single server, you can use Oracle Secure Backup Express.It is limited to a single host with one direct-attached tape drive.
Oracle Secure Backup Express is the replacement product for Legato Storage Manager (LSM) and Legato Single Server Version (LSSV).
One distinct advantage of OSB Express over LSSV is that OSB Express can do file system backup.
Oracle Secure Backup Express is bundled with the Oracle Database and its free.
Oracle's technical support organization will provide technical support, phone support, or updates to you for Oracle Secure Backup Express when it is used to back up another Oracle Product that has a valid Customer Support Identifier (CSI).
If you have a big environment you can use the full fledge Oracle Secure Backup.
Oracle Secure Backup is licensed by the number of tape drives utilized (clients are unlimited).
You can user OSB with Oracle Database 9i,10g and 11g.
Friday, December 14, 2007
Monday, December 10, 2007
RMAN and Online Redo logs
As you all know RMAN doesnt backup online redo logs
The online redo logs files are never backed up by RMAN or any hot backup method.
The online redo logs files contain the latest data or redo generated by the database.(The data comes from redo log buffer written by LGWR).
The contents of the current redo logs are not archived until a log switch occurs.
This means if all your members in a redo log group fails, you would lose the latest information.
To minimize these failures, here are some suggestions that you can implement to safeguard your online redo logs
1. Multiplex your groups to have redundancy members
2. Do not put same members of the same group in one disk controller
3. Use RAID for your online redo logs
4. Make sure switching happens at regular intervals (use archive_lag_target parameter)
Only Oracle Dataguard Implementation with Maximum Protection Mode can save all committed data in the event you lose all members of the current online redolog group
The online redo logs files are never backed up by RMAN or any hot backup method.
The online redo logs files contain the latest data or redo generated by the database.(The data comes from redo log buffer written by LGWR).
The contents of the current redo logs are not archived until a log switch occurs.
This means if all your members in a redo log group fails, you would lose the latest information.
To minimize these failures, here are some suggestions that you can implement to safeguard your online redo logs
1. Multiplex your groups to have redundancy members
2. Do not put same members of the same group in one disk controller
3. Use RAID for your online redo logs
4. Make sure switching happens at regular intervals (use archive_lag_target parameter)
Only Oracle Dataguard Implementation with Maximum Protection Mode can save all committed data in the event you lose all members of the current online redolog group
Thursday, November 29, 2007
Oracle Background Processes
Hi,Sorry for not posting any info for some time because I have been really busy.
Here is an article on Background processes.
Background processes for Orcacle9i
Database writer (DBWn)
· The database writer writes modified blocks from the database buffer cache to the datafiles.
· Oracle allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj).
· The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.
· Oracle selects an appropriate default setting for this initialization parameter (or might adjust a user specified setting) based upon the number of CPUs and the number of processor groups.
Log writer (LGWR)
· The log writer process writes redo log entries to disk.
· Redo log entries are generated in the redo log buffer of the system global area (SGA), and LGWR writes the redo log entries sequentially into an online redo log file.
· If the database has a multiplexed redo log, LGWR writes the redo log entries to a group of online redo log files.
Checkpoint (CKPT)
· At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint.
· The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.
System monitor (SMON)
· The system monitor performs crash recovery when a failed instance starts up again.
· In a cluster database (Oracle9i Real Application Clusters), the SMON process of one instance can perform instance recovery for other instances that have failed.
· SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.
· SMON also coalesces free extents within the database's dictionary-managed tablespaces to make free space contiguous and easier to allocate.
Process monitor (PMON)
· The process monitor performs process recovery when a user process fails.
· PMON is responsible for cleaning up the cache and freeing resources that the process was using.
· PMON also checks on the dispatcher processes and server processes and restarts them if they have failed.
Archiver (ARCn)
· One or more archiver processes copy the online redo log files to archival storage when they are full or a log switch occurs.
Recoverer (RECO)
· The recoverer process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.
· At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
New Background process for Oracle10g
Memory Monitor (MMON)
MMON performs various manageability-related background tasks, for example:
· Issuing alerts whenever a given metrics violates its threshold value.
· Taking snapshots by spawning additional process (MMON slaves)
· Capturing statistics value for SQL objects which have been recently modified
Recovery Writer (RVWR)
Enabling Flashback Database starts a new RVWR background process.
This process is similar to the LGWR (log writer) process.
The new process writes Flashback Database data to the Flashback Database logs.
Memory Manager (MMAN)
Memory Manager (MMAN) coordinates the sizing of different memory components within SGA.
MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage.
This process is used only when you enable Automatic Shared Memory Management.
Memory Monitor Light (MMNL)
· The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed.
· Performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation.
· It works with Automatic Workload Repository (AWR)
Please post if you have any questions.Thanks
Here is an article on Background processes.
Background processes for Orcacle9i
Database writer (DBWn)
· The database writer writes modified blocks from the database buffer cache to the datafiles.
· Oracle allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj).
· The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.
· Oracle selects an appropriate default setting for this initialization parameter (or might adjust a user specified setting) based upon the number of CPUs and the number of processor groups.
Log writer (LGWR)
· The log writer process writes redo log entries to disk.
· Redo log entries are generated in the redo log buffer of the system global area (SGA), and LGWR writes the redo log entries sequentially into an online redo log file.
· If the database has a multiplexed redo log, LGWR writes the redo log entries to a group of online redo log files.
Checkpoint (CKPT)
· At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint.
· The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.
System monitor (SMON)
· The system monitor performs crash recovery when a failed instance starts up again.
· In a cluster database (Oracle9i Real Application Clusters), the SMON process of one instance can perform instance recovery for other instances that have failed.
· SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.
· SMON also coalesces free extents within the database's dictionary-managed tablespaces to make free space contiguous and easier to allocate.
Process monitor (PMON)
· The process monitor performs process recovery when a user process fails.
· PMON is responsible for cleaning up the cache and freeing resources that the process was using.
· PMON also checks on the dispatcher processes and server processes and restarts them if they have failed.
Archiver (ARCn)
· One or more archiver processes copy the online redo log files to archival storage when they are full or a log switch occurs.
Recoverer (RECO)
· The recoverer process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.
· At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
New Background process for Oracle10g
Memory Monitor (MMON)
MMON performs various manageability-related background tasks, for example:
· Issuing alerts whenever a given metrics violates its threshold value.
· Taking snapshots by spawning additional process (MMON slaves)
· Capturing statistics value for SQL objects which have been recently modified
Recovery Writer (RVWR)
Enabling Flashback Database starts a new RVWR background process.
This process is similar to the LGWR (log writer) process.
The new process writes Flashback Database data to the Flashback Database logs.
Memory Manager (MMAN)
Memory Manager (MMAN) coordinates the sizing of different memory components within SGA.
MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage.
This process is used only when you enable Automatic Shared Memory Management.
Memory Monitor Light (MMNL)
· The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed.
· Performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation.
· It works with Automatic Workload Repository (AWR)
Please post if you have any questions.Thanks
Thursday, August 30, 2007
Flashback Recovery
A new feature in Oracle 10g. With Oracle 10g, flashback database enables to rollback the database either in full restore or partially to a specific point-in-time. Flashback feature is an easy and fast way to restore the database to a previous state without hassle.
This feature in Oracle 9i was called a flashback query. Restore is dependant on the size of the undo tablespace retention period. The bigger the undo area the longer Oracle keeps data.
Basic implementation of flashback recovery.
1) Enable archivelog:
SQL> startup mount;
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
2) Setting required parameters:
* mounted mode
SQL> alter system set db_flashback_retention_target=1200;
System altered.
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0\flash
_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
- db_flashback_retention_target = seconds the flashback data is kept.
- db_recovery_file_dest_size = size of the flashback data.
- db_recovery_file_dest = destination of the flashback data.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
3) Verifying the flashback statistics:
SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0
4) Testing the flashback recovery:
creating a tablespace:
SQL> create tablespace test datafile
2 'd:\oracle\product\10.2.0\oradata\TIME\t1.dbf' size 1m
3 extent management local uniform size 100k;
Tablespace created
creating user:
SQL> create user flash identified by back
2 default tablespace test
3 quota 200k on test
4 temporary tablespace temp;
User created
SQL> grant connect, resource to flash;
Grant succeeded.
5) Creating a table:
SQL> conn flash/back
Connected.
SQL> create table time (start_tag NUMBER(5), end_tag NUMBER(5));
Table created.
SQL> insert into time VALUES ('1.001','1.010');
1 row created.
SQL> insert into time VALUES ('1.003','1.010');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table time;
Table dropped.
SQL> desc time;
ERROR:
ORA-04043: object time does not exist
6) Running flashback on table level:
SQL> flashback table "TIME" to before drop;
Flashback complete.
SQL> desc time;
Name Null? Type
----------------------------------------- -------- ----------------------------
START_TAG NUMBER(5)
END_TAG NUMBER(5)
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
144445440
7) Running flashback database recovery:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('23-08-2007 01:59', 'DD-MM-YYYY HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select username from user_users where username='FLASH';
no rows selected
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0
This feature in Oracle 9i was called a flashback query. Restore is dependant on the size of the undo tablespace retention period. The bigger the undo area the longer Oracle keeps data.
Basic implementation of flashback recovery.
1) Enable archivelog:
SQL> startup mount;
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
2) Setting required parameters:
* mounted mode
SQL> alter system set db_flashback_retention_target=1200;
System altered.
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0\flash
_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
- db_flashback_retention_target = seconds the flashback data is kept.
- db_recovery_file_dest_size = size of the flashback data.
- db_recovery_file_dest = destination of the flashback data.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
3) Verifying the flashback statistics:
SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0
4) Testing the flashback recovery:
creating a tablespace:
SQL> create tablespace test datafile
2 'd:\oracle\product\10.2.0\oradata\TIME\t1.dbf' size 1m
3 extent management local uniform size 100k;
Tablespace created
creating user:
SQL> create user flash identified by back
2 default tablespace test
3 quota 200k on test
4 temporary tablespace temp;
User created
SQL> grant connect, resource to flash;
Grant succeeded.
5) Creating a table:
SQL> conn flash/back
Connected.
SQL> create table time (start_tag NUMBER(5), end_tag NUMBER(5));
Table created.
SQL> insert into time VALUES ('1.001','1.010');
1 row created.
SQL> insert into time VALUES ('1.003','1.010');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table time;
Table dropped.
SQL> desc time;
ERROR:
ORA-04043: object time does not exist
6) Running flashback on table level:
SQL> flashback table "TIME" to before drop;
Flashback complete.
SQL> desc time;
Name Null? Type
----------------------------------------- -------- ----------------------------
START_TAG NUMBER(5)
END_TAG NUMBER(5)
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
144445440
7) Running flashback database recovery:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('23-08-2007 01:59', 'DD-MM-YYYY HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select username from user_users where username='FLASH';
no rows selected
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0
Tuesday, July 24, 2007
FLASH RECOVERY AREA 10g
The Flash Recovery Area, a new feature in 10g, is a specific area of disk storage that is set aside for retention of backup components such as :-
datafiles
image copies
archived redo logs
control file autobackup copies and etc
Features include:
Centralized Backup Files location - All backup components can be stored in one consolidated disk. The Flash Recovery Area can be configured for use by multiple database instances if so desired.
Automated Disk-Based Backup and Recovery -Once the Flash Recovery Area is configured, all backup components are managed automatically by the Oracle server.
Flashback Logs. The Flash Recovery Area is also used to store and manage flashback logs, which are used during Flashback Backup operations to quickly restore a database to a prior desired state.( I will discuss this in another article)
Oracle recommends that the Flash Recovery Area should be sized large enough to include all files required for backup and recovery. Just make sure you have enough continuous disk space.
When sizing, you need to take into account the RMAN backup retention policy, storage device type that you are using and number of data block changes.
A warning server alert is issued when the flash recovery area is 85% full.
A critical server alert is issued when the flash recovery area is 97% full.
(These percentages can be changed )
You can see the alerts in the alert.log and in DBA_OUTSTANDING_ALERTS.
If the flash recovery area becomes full, an error will be issued.
Setting Up the Flash Recovery Area.
These involve setting up 2 main initialization parameter.
DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area.( when reached maximum, it can be changed by alter system)
This parameter has to be set first
DB_RECOVERY_FILE_DEST specifies the physical disk location where the Flashback Recovery Area will be stored. Oracle recommends that this be a separate location from the database's datafiles, control files, and redo logs
ALTER SYSTEM SET db_file_recovery_dest_size = '5G' SCOPE=BOTH;
and then set DB_FILE_RECOVERY_DEST and DB_FLASHBACK_RETENTION_TARGET
ALTER SYSTEM SET db_file_recovery_dest = 'c:\oracle\innotiive\flash' SCOPE=BOTH;
ALTER SYSTEM SET db_flashback_retention_target = 2880;( in minutes)
(It is for retention of flasback logs ).
datafiles
image copies
archived redo logs
control file autobackup copies and etc
Features include:
Centralized Backup Files location - All backup components can be stored in one consolidated disk. The Flash Recovery Area can be configured for use by multiple database instances if so desired.
Automated Disk-Based Backup and Recovery -Once the Flash Recovery Area is configured, all backup components are managed automatically by the Oracle server.
Flashback Logs. The Flash Recovery Area is also used to store and manage flashback logs, which are used during Flashback Backup operations to quickly restore a database to a prior desired state.( I will discuss this in another article)
Oracle recommends that the Flash Recovery Area should be sized large enough to include all files required for backup and recovery. Just make sure you have enough continuous disk space.
When sizing, you need to take into account the RMAN backup retention policy, storage device type that you are using and number of data block changes.
A warning server alert is issued when the flash recovery area is 85% full.
A critical server alert is issued when the flash recovery area is 97% full.
(These percentages can be changed )
You can see the alerts in the alert.log and in DBA_OUTSTANDING_ALERTS.
If the flash recovery area becomes full, an error will be issued.
Setting Up the Flash Recovery Area.
These involve setting up 2 main initialization parameter.
DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area.( when reached maximum, it can be changed by alter system)
This parameter has to be set first
DB_RECOVERY_FILE_DEST specifies the physical disk location where the Flashback Recovery Area will be stored. Oracle recommends that this be a separate location from the database's datafiles, control files, and redo logs
ALTER SYSTEM SET db_file_recovery_dest_size = '5G' SCOPE=BOTH;
and then set DB_FILE_RECOVERY_DEST and DB_FLASHBACK_RETENTION_TARGET
ALTER SYSTEM SET db_file_recovery_dest = 'c:\oracle\innotiive\flash' SCOPE=BOTH;
ALTER SYSTEM SET db_flashback_retention_target = 2880;( in minutes)
(It is for retention of flasback logs ).
Saturday, June 16, 2007
Renaming the existing database
Here is a step by step guide on how to change an existing database name. Ths guide is done by one of the Innotiiveasia's consultant.I hope it helps you all and if you have any comments, feel free to do it in this blog.Hapy reading.
Renaming the existing database
Connect to the database we wanted to change.
SQL> select name from v$database;
NAME
---------
PROD
The existing database name is prod, changing the database name to test. First force a log switch.
SQL> alter system switch logfile;
System altered.
Then backup the controlfile to trace
SQL> alter database backup controlfile to trace resetlogs;
Database altered.
Note: This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form.
4. shutdown the database using shutdown immediate command.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
5. Find the trace file in udump and modify it as below
cd $ORACLE_HOME/admin/prod/udump
[oracle@localhost udump]$ ls -lrt
total 1024
-rw-r----- 1 oracle dba 695 Jun 12 13:41 prod_ora_6567.trc
-rw-r----- 1 oracle dba 2094 Jun 12 13:41 prod_ora_6569.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:42 prod_ora_6604.trc
-rw-r----- 1 oracle dba 666 Jun 12 13:42 prod_ora_6580.trc
-rw-r----- 1 oracle dba 795 Jun 12 13:42 prod_ora_6605.trc
-rw-r----- 1 oracle dba 638 Jun 12 13:43 prod_ora_6619.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:43 prod_ora_6642.trc
-rw-r----- 1 oracle dba 6161 Jun 12 15:29 prod_ora_6644.trc
-rw-r----- 1 oracle dba 695 Jun 12 16:25 prod_ora_7693.trc
-rw-r----- 1 oracle dba 666 Jun 12 16:25 prod_ora_7668.trc
-rw-r----- 1 oracle dba 10260 Jun 12 16:40 prod_ora_7694.trc
6. Check the latest trace file and use vi to edit the file. Delete all the statement in trace file until the file set 2 STARTUP NOMOUNT.
Below is the trace file, which i deleted the unnecessary statement until the startup nomount. STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top
7. Edit the trace file. Change the word 'REUSE' to 'set' and the 'olddbname' to ‘newdbname’.
FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS
This is how the file will look after modification
STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M
-- STANDBY LOG FILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top
Note: SAVE the modified trace file to a new name.sql such as “rename.sql”
8. Connect to sqlplus and startup the database in nomount stage and check whether the database using spfile or pfile.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218944 bytes
Variable Size 79693440 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
----------- ------------------------------
spfile string
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string prod
9. Change the db_name in spfile to the new database name
SQL> alter system set db_name=test scope=spfile;
System altered.
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string test
10. Shutdown the database, rename the existing controlfile and run the script
[oracle@localhost udump]$ cd /u02
[oracle@localhost u02]$ cd oradata/prod
[oracle@localhost prod]$ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
control02.ctl redo02.log system01.dbf users01.dbf
control03.ctl redo03.log temp01.dbf
[oracle@localhost prod]$ mv control01.ctl con1.ctl
[oracle@localhost prod]$ mv control02.ctl con2.ctl
oracle@localhost prod]$ mv control03.ctl con3.ctl
[oracle@localhost prod]$ lscon1.ctl con3.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf control2.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
11. Run The Script
SQL>@$ORACLE_HOME/admin/prod/udump/rename
ORA-00308: cannot open archived log 'ARCH:
'ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested filename AUTO CANCEL}
11. When we encounter the statement above apply the logfiles path
Specify log: {=suggested filename AUTO CANCEL}
/u02/oradata/prod/redo02.log
Log applied.
Media recovery complete.
12. Once the media recovery complete, open the database with resetlogs
SQL> alter database open resetlogs;
Database altered.
13. The global database name may also need to be changed
SQL> alter database rename global_name to test;
Database altered.
14. To confirm the database name already been changed,
select the database name from v$database.
SQL> select name from v$database;
NAME
---------
TEST
Renaming the existing database
Connect to the database we wanted to change.
SQL> select name from v$database;
NAME
---------
PROD
The existing database name is prod, changing the database name to test. First force a log switch.
SQL> alter system switch logfile;
System altered.
Then backup the controlfile to trace
SQL> alter database backup controlfile to trace resetlogs;
Database altered.
Note: This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form.
4. shutdown the database using shutdown immediate command.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
5. Find the trace file in udump and modify it as below
cd $ORACLE_HOME/admin/prod/udump
[oracle@localhost udump]$ ls -lrt
total 1024
-rw-r----- 1 oracle dba 695 Jun 12 13:41 prod_ora_6567.trc
-rw-r----- 1 oracle dba 2094 Jun 12 13:41 prod_ora_6569.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:42 prod_ora_6604.trc
-rw-r----- 1 oracle dba 666 Jun 12 13:42 prod_ora_6580.trc
-rw-r----- 1 oracle dba 795 Jun 12 13:42 prod_ora_6605.trc
-rw-r----- 1 oracle dba 638 Jun 12 13:43 prod_ora_6619.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:43 prod_ora_6642.trc
-rw-r----- 1 oracle dba 6161 Jun 12 15:29 prod_ora_6644.trc
-rw-r----- 1 oracle dba 695 Jun 12 16:25 prod_ora_7693.trc
-rw-r----- 1 oracle dba 666 Jun 12 16:25 prod_ora_7668.trc
-rw-r----- 1 oracle dba 10260 Jun 12 16:40 prod_ora_7694.trc
6. Check the latest trace file and use vi to edit the file. Delete all the statement in trace file until the file set 2 STARTUP NOMOUNT.
Below is the trace file, which i deleted the unnecessary statement until the startup nomount. STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top
7. Edit the trace file. Change the word 'REUSE' to 'set' and the 'olddbname' to ‘newdbname’.
FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS
This is how the file will look after modification
STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M
-- STANDBY LOG FILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top
Note: SAVE the modified trace file to a new name.sql such as “rename.sql”
8. Connect to sqlplus and startup the database in nomount stage and check whether the database using spfile or pfile.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218944 bytes
Variable Size 79693440 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
----------- ------------------------------
spfile string
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string prod
9. Change the db_name in spfile to the new database name
SQL> alter system set db_name=test scope=spfile;
System altered.
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string test
10. Shutdown the database, rename the existing controlfile and run the script
[oracle@localhost udump]$ cd /u02
[oracle@localhost u02]$ cd oradata/prod
[oracle@localhost prod]$ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
control02.ctl redo02.log system01.dbf users01.dbf
control03.ctl redo03.log temp01.dbf
[oracle@localhost prod]$ mv control01.ctl con1.ctl
[oracle@localhost prod]$ mv control02.ctl con2.ctl
oracle@localhost prod]$ mv control03.ctl con3.ctl
[oracle@localhost prod]$ lscon1.ctl con3.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf control2.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
11. Run The Script
SQL>@$ORACLE_HOME/admin/prod/udump/rename
ORA-00308: cannot open archived log 'ARCH:
'ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {
11. When we encounter the statement above apply the logfiles path
Specify log: {
/u02/oradata/prod/redo02.log
Log applied.
Media recovery complete.
12. Once the media recovery complete, open the database with resetlogs
SQL> alter database open resetlogs;
Database altered.
13. The global database name may also need to be changed
SQL> alter database rename global_name to test;
Database altered.
14. To confirm the database name already been changed,
select the database name from v$database.
SQL> select name from v$database;
NAME
---------
TEST
Thursday, May 31, 2007
Segment Shrink in 10g
DML activities like updates and deletes will make segements have chunks of free space within their data blocks.This will make the particular object fragmented
This can lead to very poor performance because select statements that does full table scans will have to scan more blocks to retrieve data.
In oracle 9i, to reclaim free space we can do the following
1.Drop the table,recreate it and then load back the data or
2.Use the 'ALTER TABLE MOVE' command to move the table to a new tablespace
3.Online table reorganization
In Oracle 10g, you are able to shrink segments with commands directly.
During this operation
-Segment data is compacted.
-High-water mark (HWM) is brought down.
-Unused space is released back to the tablespace containing the segment.
e.g
-- Enable row movement.
ALTER TABLE ABU.ORDERS ENABLE ROW MOVEMENT;
-- Recover space and amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE;
-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE COMPACT;
-- Recover space for the object and all dependant objects.
ALTER TABLE ABU.ORDERS SHRINK SPACE CASCADE;
The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place.
For these operations to work, the objects needs to be in a tablespace with automatic segment-space management enabled.
You need not make a tablespace read-only, offline, or autoextensible before shrinking any of the segments in that tablespace.
This can lead to very poor performance because select statements that does full table scans will have to scan more blocks to retrieve data.
In oracle 9i, to reclaim free space we can do the following
1.Drop the table,recreate it and then load back the data or
2.Use the 'ALTER TABLE MOVE' command to move the table to a new tablespace
3.Online table reorganization
In Oracle 10g, you are able to shrink segments with commands directly.
During this operation
-Segment data is compacted.
-High-water mark (HWM) is brought down.
-Unused space is released back to the tablespace containing the segment.
e.g
-- Enable row movement.
ALTER TABLE ABU.ORDERS ENABLE ROW MOVEMENT;
-- Recover space and amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE;
-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE COMPACT;
-- Recover space for the object and all dependant objects.
ALTER TABLE ABU.ORDERS SHRINK SPACE CASCADE;
The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place.
For these operations to work, the objects needs to be in a tablespace with automatic segment-space management enabled.
You need not make a tablespace read-only, offline, or autoextensible before shrinking any of the segments in that tablespace.
Monday, May 28, 2007
Temporary tablespace groups in 10g
The main idea behind temporary tablespace groups is to improve the scalability of operations that require more temporary space. This is a new feature introduced in 10g.
There must be a minimum of one tablespace in a temporary tablespace group and groups cannot exist without at least one temporary tablespace.
The group will not exist when the last tablespace member is removed from it. For a user to be able to use the group, assign the tablespace group name when assigning temporary tablespace to users.
There is no explicit limit on the maximum number of tablespaces that are contained in a group.
EXAMPLES
– create a temporary tablespace without a group
CREATE TEMPORARY TABLESPACE temp1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP ‘’; -- same as not specifying a group
– create a temporary tablespace group with one tablespace (TEMP1) in it:
CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP TEMPGRP1;
If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:
– user TEMPGRP1 as the default temporary tablespace for all new users in the database:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPGRP1;
- Make user ALI use the temporary tablespace group
ALTER USER ALI TEMPORARY TABLESPACE TEMPGRP1;
There must be a minimum of one tablespace in a temporary tablespace group and groups cannot exist without at least one temporary tablespace.
The group will not exist when the last tablespace member is removed from it. For a user to be able to use the group, assign the tablespace group name when assigning temporary tablespace to users.
There is no explicit limit on the maximum number of tablespaces that are contained in a group.
EXAMPLES
– create a temporary tablespace without a group
CREATE TEMPORARY TABLESPACE temp1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP ‘’; -- same as not specifying a group
– create a temporary tablespace group with one tablespace (TEMP1) in it:
CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP TEMPGRP1;
If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:
– user TEMPGRP1 as the default temporary tablespace for all new users in the database:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPGRP1;
- Make user ALI use the temporary tablespace group
ALTER USER ALI TEMPORARY TABLESPACE TEMPGRP1;
Saturday, April 28, 2007
Roles
Before I talk about roles, there is a few points that I want the readers to understand before I begin.
All Oracle objects (e.g.tables, indexes, etc) are owned by users who created them.
- only the user who created a table will be able to see its data.To allow other to see rows, the owner/user must grant access privileges.
- Oracle has system privileges that allow global access rights.
- Oracle has object privileges that allow grants to specific tables.
Any big organization will face difficulties to manage their database users in the form of managing their system and objects privileges.
In these companies staffs come and go plus they may have selected privileges on many objects.
Roles will be most helpful to manage this problem.
A role is a set or group of privileges that can be granted to users or roles. This is a great way for DBA to save time and effort.
To be able to create a role, you must have at least the CREATE ROLE system privileges.
The syntax for creating a role is:
CREATE ROLE role_name[ NOT IDENTIFIED IDENTIFIED {BY password USING [schema.] package EXTERNALLY GLOBALLY } ;
If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.
The role_name phrase is the name of the new role that you are creating.
The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.
The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.
The BY password phrase means that a user must supply a password to enable the role.
The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.
The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.
The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.
Example:
CREATE ROLE abc;
This 1st example creates a role called abc.
CREATE ROLE abcIDENTIFIED BY abc123;
This 2nd example creates the same role called abc, but now it is password protected with the password of abc123.
The syntax for granting privileges on a table is:
grant privileges on object to role_name
For example, if you wanted to grant select, insert, update, and delete privileges on a table called emp to a role named abc, you would execute the following statement:
grant select, insert, update, delete on emp to abc;
You can also use the all keyword to indicate that you wish all permissions to be granted.
For example:
grant all on emp to abc;
Revoke Privileges (on Tables) to Roles.Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from role_name;
For example, if you wanted to revoke delete privileges on a table called emp from a role named abc, you would execute the following statement:
revoke delete on emp from abc;
If you wanted to revoke all privileges on a table, you could use the all keyword.
For example:
revoke all on emp from abc;
Granting the Role to a User
GRANT role_name TO user_name;
For example:
GRANT abc to bakar;
This example would grant the role called abc to the user named bakar.
The user bakar will automatically gets all the privileges that was in the role abc
The SET ROLE statement allows you to enable or disable a role for a current session.
When a user logs into the database, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.
The syntax for the SET ROLE statement is:
SET ROLE( role_name [ IDENTIFIED BY password ] ALL [EXCEPT roleA, roleB, ... ] NONE );
The role_name phrase is the name of the role that you wish to enable.
The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.
The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles for the current session. (including all default roles)
Example
SET ROLE abc IDENTIFIED BY abc123;
This example would enable the role called abc with a password of abc123.
A default role means that the role is always enabled for the current session when the user logs on. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.
The syntax for setting a role as a DEFAULT role is:
ALTER USER user_nameDEFAULT ROLE( role_name ALL [EXCEPT roleA, roleB, ... ] NONE );
The user_name phrase is the name of the user whose role you are setting as DEFAULT.
The role_name phrase is the name of the role that you wish to set as DEFAULT.
The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles as DEFAULT.
Example:
ALTER USER bakarDEFAULT ROLE abc;
This example would set the role called abc as a DEFAULT role for the user called bakar.
ALTER USER bakar DEFAULT ROLE ALL;
This example would set all roles assigned to bakar as DEFAULT.
ALTER USER bakarDEFAULT ROLEALL EXCEPT abc;
This example would set all roles assigned to bakar as DEFAULT, except for the role called abc.
All Oracle objects (e.g.tables, indexes, etc) are owned by users who created them.
- only the user who created a table will be able to see its data.To allow other to see rows, the owner/user must grant access privileges.
- Oracle has system privileges that allow global access rights.
- Oracle has object privileges that allow grants to specific tables.
Any big organization will face difficulties to manage their database users in the form of managing their system and objects privileges.
In these companies staffs come and go plus they may have selected privileges on many objects.
Roles will be most helpful to manage this problem.
A role is a set or group of privileges that can be granted to users or roles. This is a great way for DBA to save time and effort.
To be able to create a role, you must have at least the CREATE ROLE system privileges.
The syntax for creating a role is:
CREATE ROLE role_name[ NOT IDENTIFIED IDENTIFIED {BY password USING [schema.] package EXTERNALLY GLOBALLY } ;
If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.
The role_name phrase is the name of the new role that you are creating.
The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.
The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.
The BY password phrase means that a user must supply a password to enable the role.
The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.
The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.
The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.
Example:
CREATE ROLE abc;
This 1st example creates a role called abc.
CREATE ROLE abcIDENTIFIED BY abc123;
This 2nd example creates the same role called abc, but now it is password protected with the password of abc123.
The syntax for granting privileges on a table is:
grant privileges on object to role_name
For example, if you wanted to grant select, insert, update, and delete privileges on a table called emp to a role named abc, you would execute the following statement:
grant select, insert, update, delete on emp to abc;
You can also use the all keyword to indicate that you wish all permissions to be granted.
For example:
grant all on emp to abc;
Revoke Privileges (on Tables) to Roles.Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from role_name;
For example, if you wanted to revoke delete privileges on a table called emp from a role named abc, you would execute the following statement:
revoke delete on emp from abc;
If you wanted to revoke all privileges on a table, you could use the all keyword.
For example:
revoke all on emp from abc;
Granting the Role to a User
GRANT role_name TO user_name;
For example:
GRANT abc to bakar;
This example would grant the role called abc to the user named bakar.
The user bakar will automatically gets all the privileges that was in the role abc
The SET ROLE statement allows you to enable or disable a role for a current session.
When a user logs into the database, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.
The syntax for the SET ROLE statement is:
SET ROLE( role_name [ IDENTIFIED BY password ] ALL [EXCEPT roleA, roleB, ... ] NONE );
The role_name phrase is the name of the role that you wish to enable.
The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.
The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles for the current session. (including all default roles)
Example
SET ROLE abc IDENTIFIED BY abc123;
This example would enable the role called abc with a password of abc123.
A default role means that the role is always enabled for the current session when the user logs on. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.
The syntax for setting a role as a DEFAULT role is:
ALTER USER user_nameDEFAULT ROLE( role_name ALL [EXCEPT roleA, roleB, ... ] NONE );
The user_name phrase is the name of the user whose role you are setting as DEFAULT.
The role_name phrase is the name of the role that you wish to set as DEFAULT.
The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles as DEFAULT.
Example:
ALTER USER bakarDEFAULT ROLE abc;
This example would set the role called abc as a DEFAULT role for the user called bakar.
ALTER USER bakar DEFAULT ROLE ALL;
This example would set all roles assigned to bakar as DEFAULT.
ALTER USER bakarDEFAULT ROLEALL EXCEPT abc;
This example would set all roles assigned to bakar as DEFAULT, except for the role called abc.
Thursday, April 12, 2007
How to be a successful Oracle Database Professional
I read this article in a blog from a very famous Oracle Guru, Tom Kyte (http://tkyte.blogspot.com)
I thought of sharing this with you all of what he thinks to become a successful Oracle Database Professional.
Ways to go to become a successful Oracle Database Professional
1. Be involved, participate. I cannot stress that one enough.
2. Understand. It is not good enough to think you know how something should work according to you. You have to understand how it actually does work. What you believe to be the way something is implemented could be far from the truth as to how it actually is implemented.
3.Ask why whenever you don't know why. We could just shorten that to "ask". If you don't ask, you'll never know. If you are answering questions and don't ask some yourself - you are doing it wrong.
4.Trust but Question Authority. If you see something written as a statement of fact without any supporting evidence either ignore it entirely or if you feel it could be beneficial to you if true develop a method to test the concept and see if it holds true.
5.If you want to be successful, you'll be communicating. Be precise in that communication. It really does make a difference. Clarity is necessary and preciseness is mandatory for clarity.
6.When you participate and start giving your own answers, your own input - do it right
I hope this sayings from Tom Kyte motivates you all.
I thought of sharing this with you all of what he thinks to become a successful Oracle Database Professional.
Ways to go to become a successful Oracle Database Professional
1. Be involved, participate. I cannot stress that one enough.
2. Understand. It is not good enough to think you know how something should work according to you. You have to understand how it actually does work. What you believe to be the way something is implemented could be far from the truth as to how it actually is implemented.
3.Ask why whenever you don't know why. We could just shorten that to "ask". If you don't ask, you'll never know. If you are answering questions and don't ask some yourself - you are doing it wrong.
4.Trust but Question Authority. If you see something written as a statement of fact without any supporting evidence either ignore it entirely or if you feel it could be beneficial to you if true develop a method to test the concept and see if it holds true.
5.If you want to be successful, you'll be communicating. Be precise in that communication. It really does make a difference. Clarity is necessary and preciseness is mandatory for clarity.
6.When you participate and start giving your own answers, your own input - do it right
I hope this sayings from Tom Kyte motivates you all.
Monday, April 2, 2007
orakill on windows
This utility is provided only with Oracle databases on WINDOWS platforms.Ths executable allows a DBA to kill sessions in Oracle withuot connecting to the database.That is you can do it on DOS command prompt.(orakill.exe)
Unix system are processes based while windows environment are thread based.
In Unix, every oracle sessions can be seen individually by using the ps comand but in windows
based system all sessions are in the oracle.exe executables.That means you won't be able to see
sessions individually.
The orakill will do the same thing in windows what kill -9 does in UNIX.
C:\oracle>orakill
Usage: orakill sid thread
where sid = the Oracle instance to target
thread = the thread id of the thread to kill
The thread id should be retrieved from the spid column of a query such as:
select spid, osuser, s.program
from v$process p, v$session s
where p.addr=s.paddr;
to get a better view, you can user the below query to replace the above.
select b.spid, a.osuser, a.username
from v$process b, v$session a
where b.addr=a.paddr
and a.username is not null;
e.g
SPID OSUSER USERNAME
------------ ------------------------------ -------------------
2508 MURALI\innotiive PRASANA
2644 MURALI\innotiive ALI
C:\oracle>orakill PROD 2644
Kill of thread id 2644 in instance PROD successfully signalled.
SQL>select b.spid, a.osuser, a.username from
v$process b, v$session a where b.addr=a.paddr
and a.username is not null;
SPID OSUSER USERNAME
------------ ------------------------------ --------------
2508 MURALI\innotiive PRASANA
You can also kill a user session from the Oracle databasee.g.alter system kil session (sid,serial#);
You can get the value of sid and serial# from the v$session view.
One of the reason to use orakill instead of alter system kill session is orakill will clear
any locks that exist but alter system kill session will remain connected until it times out and
then it release the locks.
The orakill utility should be used as a last resort only. If the session cannot be killed more
gracefully (via alter system kill session), or the instance is inaccessible via SQL, then
orakill should be used to terminate the offending session.
Be careful not to kill a background process as this may cause the database to be down.
Unix system are processes based while windows environment are thread based.
In Unix, every oracle sessions can be seen individually by using the ps comand but in windows
based system all sessions are in the oracle.exe executables.That means you won't be able to see
sessions individually.
The orakill will do the same thing in windows what kill -9 does in UNIX.
C:\oracle>orakill
Usage: orakill sid thread
where sid = the Oracle instance to target
thread = the thread id of the thread to kill
The thread id should be retrieved from the spid column of a query such as:
select spid, osuser, s.program
from v$process p, v$session s
where p.addr=s.paddr;
to get a better view, you can user the below query to replace the above.
select b.spid, a.osuser, a.username
from v$process b, v$session a
where b.addr=a.paddr
and a.username is not null;
e.g
SPID OSUSER USERNAME
------------ ------------------------------ -------------------
2508 MURALI\innotiive PRASANA
2644 MURALI\innotiive ALI
C:\oracle>orakill PROD 2644
Kill of thread id 2644 in instance PROD successfully signalled.
SQL>select b.spid, a.osuser, a.username from
v$process b, v$session a where b.addr=a.paddr
and a.username is not null;
SPID OSUSER USERNAME
------------ ------------------------------ --------------
2508 MURALI\innotiive PRASANA
You can also kill a user session from the Oracle databasee.g.alter system kil session (sid,serial#);
You can get the value of sid and serial# from the v$session view.
One of the reason to use orakill instead of alter system kill session is orakill will clear
any locks that exist but alter system kill session will remain connected until it times out and
then it release the locks.
The orakill utility should be used as a last resort only. If the session cannot be killed more
gracefully (via alter system kill session), or the instance is inaccessible via SQL, then
orakill should be used to terminate the offending session.
Be careful not to kill a background process as this may cause the database to be down.
Saturday, March 24, 2007
Bigfile Tablespace in 10g
Database tables nowdays holds alot of data. This is of because data is important for companies to make decisions.As you are aware the concept of BI (Business Intelligence), it is becoming famous nowdays. BI needs alot of data to make better decisions.
To address this craving,Oracle has come up with th Bigfile Tablespace concept.
Bigfile tablespaces(BFT) are tablespaces with a single large datafile(this means you can only have 1 datafile for bigfile tablespace). In contrast to normal (smallfile) tablespaces can have several datafiles(max 1022 files), but each is limited in size (the size of the disk partiton).
This concept is introduced in 10g.
Bigfile tablespaces(BFT) must be locally managed with automatic segment-space management.
Temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces are exceptions to this rule.
e.g. of creating a bigfile tablespace
CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G;
OR
CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).
DBA_TABLESPACES AND V$TABLESPACE contain a new column called BIGFILE that allows administrators to determine if the tablespace is a Smallfile or Bigfile tablespace.
Before you even consider creating one of these things, make sure that your environment is able to support its expansion
For using BFT,the underlying operating system should support Large File.In other words the file system should have Large File Support(LFS).
Bigfile tablespaces will have a future in many Oracle environments but they must only be implemented with much forethought.
To address this craving,Oracle has come up with th Bigfile Tablespace concept.
Bigfile tablespaces(BFT) are tablespaces with a single large datafile(this means you can only have 1 datafile for bigfile tablespace). In contrast to normal (smallfile) tablespaces can have several datafiles(max 1022 files), but each is limited in size (the size of the disk partiton).
This concept is introduced in 10g.
Bigfile tablespaces(BFT) must be locally managed with automatic segment-space management.
Temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces are exceptions to this rule.
e.g. of creating a bigfile tablespace
CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G;
OR
CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).
DBA_TABLESPACES AND V$TABLESPACE contain a new column called BIGFILE that allows administrators to determine if the tablespace is a Smallfile or Bigfile tablespace.
Before you even consider creating one of these things, make sure that your environment is able to support its expansion
For using BFT,the underlying operating system should support Large File.In other words the file system should have Large File Support(LFS).
Bigfile tablespaces will have a future in many Oracle environments but they must only be implemented with much forethought.
Friday, March 16, 2007
Oracle Recycle Bin
This is a new feature in Oracle10g.Prior to 10g,when you issue the DROP command
e.g. DROP table EMP;
The particular table is dropped and also its dependent objects (indexes,costraints and triggers,etc).
In Oracle 10g it will be renamed to a system generated string and logically placed in the recycle bin.Each dropped object will be placed in the recycle bin which is in the same tablespace the schema from which it was dropped from.
The recycle bin is actually a new data dictionary table(SYS.RECYCLEBIN$) that records information about dropped tables.When an object is dropped,Oracle just renames the table and all its associated objects (indexes, triggers,etc) to a system-generated name that begins with BIN$.
There are two recyclebin views, USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.
Users will retain all writes and privileges to the recyclebin object just as before the object was dropped.
There is no special storage for these dropped objects because it will still remain in the tablespace they were dropped from.
Dropped objects is still counted in the users's quota.
Here are some examples.Have fun!!!
SQL> create table t (name varchar2(8));
Table created.
SQL> insert into t values ('ali');
1 row created.
SQL> insert into t values ('abu');
1 row created.
SQL> select * from t;
NAME
--------
ali
abu
SQL> drop table t;
Table dropped.
SQL> select * from t;select * from t
*ERROR at line 1:ORA-00942: table or view does not exist
SQL> select object_name,original_name,ts_name,droptime from recyclebin;
OBJECT_ NAME ORIGINAL_NAME TS_NAME DROPTIME
---------------------- ------------------- ------------ ---------------
BIN$DgYaFTOBTrScJ+2ba85IXA==$0 T USERS 2007-03-15:10:14:22
You can also type
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME-------------- --------------- --------------------- --------------- -------------
T BIN$DgYaFTOBTrScJ+2ba85IXA==$0 TABLE 2007-03-15:10:14:22
To query the content of a table in recyclebin
SQL> select * from "BIN$DgYaFTOBTrScJ+2ba85IXA==$0";
NAME
--------
ali
abu
to recover the dropped table
SQL> flashback table t to before drop;
Flashback complete.
SQL> select * from t;
NAME
--------
ali
abu
SQL> select * from recyclebin;
no rows selected
Some other commands
1.to recover the table to a different name
SQL> flashback table t to before drop rename to t1;
2. To drop permanently(do not go to recyclebin)
SQL> DROP table t purge;
When you issue the DROP TABLESPACE....INCLUDING CONTENTS,all objects will be drop and won't go to the recyclebin and all objects in the recyclebin will be removed.
Usually indexes,constraints and triggers should retain the original name after the dropped table is recovered but now the indexes has system generated names not original names.This is a bug in Oracle.
e.g. DROP table EMP;
The particular table is dropped and also its dependent objects (indexes,costraints and triggers,etc).
In Oracle 10g it will be renamed to a system generated string and logically placed in the recycle bin.Each dropped object will be placed in the recycle bin which is in the same tablespace the schema from which it was dropped from.
The recycle bin is actually a new data dictionary table(SYS.RECYCLEBIN$) that records information about dropped tables.When an object is dropped,Oracle just renames the table and all its associated objects (indexes, triggers,etc) to a system-generated name that begins with BIN$.
There are two recyclebin views, USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.
Users will retain all writes and privileges to the recyclebin object just as before the object was dropped.
There is no special storage for these dropped objects because it will still remain in the tablespace they were dropped from.
Dropped objects is still counted in the users's quota.
Here are some examples.Have fun!!!
SQL> create table t (name varchar2(8));
Table created.
SQL> insert into t values ('ali');
1 row created.
SQL> insert into t values ('abu');
1 row created.
SQL> select * from t;
NAME
--------
ali
abu
SQL> drop table t;
Table dropped.
SQL> select * from t;select * from t
*ERROR at line 1:ORA-00942: table or view does not exist
SQL> select object_name,original_name,ts_name,droptime from recyclebin;
OBJECT_ NAME ORIGINAL_NAME TS_NAME DROPTIME
---------------------- ------------------- ------------ ---------------
BIN$DgYaFTOBTrScJ+2ba85IXA==$0 T USERS 2007-03-15:10:14:22
You can also type
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME-------------- --------------- --------------------- --------------- -------------
T BIN$DgYaFTOBTrScJ+2ba85IXA==$0 TABLE 2007-03-15:10:14:22
To query the content of a table in recyclebin
SQL> select * from "BIN$DgYaFTOBTrScJ+2ba85IXA==$0";
NAME
--------
ali
abu
to recover the dropped table
SQL> flashback table t to before drop;
Flashback complete.
SQL> select * from t;
NAME
--------
ali
abu
SQL> select * from recyclebin;
no rows selected
Some other commands
1.to recover the table to a different name
SQL> flashback table t to before drop rename to t1;
2. To drop permanently(do not go to recyclebin)
SQL> DROP table t purge;
When you issue the DROP TABLESPACE....INCLUDING CONTENTS,all objects will be drop and won't go to the recyclebin and all objects in the recyclebin will be removed.
Usually indexes,constraints and triggers should retain the original name after the dropped table is recovered but now the indexes has system generated names not original names.This is a bug in Oracle.
Friday, March 9, 2007
SPFILE or PFILE
Someone ask me the other day the differences between spfile and pfile
Here are my input:
PFILE or SPFILE are file that is used to store database intialization parameters.
The pfile or spfile is read during instance startup to get instance specific settings.
SPFILE was implemented or introduced from 9i only.In 10g pfile is still supported
You only can use one at a time but both files can exist in the default location.
By default, if you do not specify PFILE in your STARTUP command, Oracle will use the spfile.
Changes to pfile can be made by editing the file and do a shutdown startup but for spfile, changes are made using the alter system command.
To create a server parameter file from a parameter file
login as sys
create spfile from pfile;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)
By using spfile changes can be made in 3 ways as specified below
SCOPE = SPFILE
e.g. alter system set sql_trace=true scope =spfile;
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)
SCOPE = MEMORY
e.g alter system set undo_tablespace=undotbs1 scope=memory;
(For dynamic parameters, changes are applied in memory only. No static parameter changes is allowed.)
SCOPE = BOTH
e.g alter system set log_archive_dest_1 =/u01/arch2 scope=both;
For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameters changes is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
We can also use the server parameter file to create a pfile
This would be useful for:
1) Creating backups of the spfile.
2) For diagnostic purposes
3) Modifying the spfile by first exporting it, editing the output file, and then recreating it.
The following example creates a pfile from spfile
login as sys
CREATE PFILE FROM SPFILE;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)
Fell free to comment or ask questions....Thanks
Here are my input:
PFILE or SPFILE are file that is used to store database intialization parameters.
The pfile or spfile is read during instance startup to get instance specific settings.
SPFILE was implemented or introduced from 9i only.In 10g pfile is still supported
You only can use one at a time but both files can exist in the default location.
By default, if you do not specify PFILE in your STARTUP command, Oracle will use the spfile.
Changes to pfile can be made by editing the file and do a shutdown startup but for spfile, changes are made using the alter system command.
To create a server parameter file from a parameter file
login as sys
create spfile from pfile;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)
By using spfile changes can be made in 3 ways as specified below
SCOPE = SPFILE
e.g. alter system set sql_trace=true scope =spfile;
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)
SCOPE = MEMORY
e.g alter system set undo_tablespace=undotbs1 scope=memory;
(For dynamic parameters, changes are applied in memory only. No static parameter changes is allowed.)
SCOPE = BOTH
e.g alter system set log_archive_dest_1 =/u01/arch2 scope=both;
For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameters changes is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
We can also use the server parameter file to create a pfile
This would be useful for:
1) Creating backups of the spfile.
2) For diagnostic purposes
3) Modifying the spfile by first exporting it, editing the output file, and then recreating it.
The following example creates a pfile from spfile
login as sys
CREATE PFILE FROM SPFILE;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)
Fell free to comment or ask questions....Thanks
Monday, March 5, 2007
Welcome all Malaysian Oracle Users
I am starting this blog to help Malaysian Oracle Users, so that they can share their experiences on Oracle Databases (7,8,8i,9i,10g)
I will try my best to post new/fresh/old experiences that I have encountered as a Database consultant.
Please give your comments if there is any mistakes or how to make this blog better.
Hope to do it weekly. WATCH OUT!!!
I will try my best to post new/fresh/old experiences that I have encountered as a Database consultant.
Please give your comments if there is any mistakes or how to make this blog better.
Hope to do it weekly. WATCH OUT!!!
Subscribe to:
Posts (Atom)
