Are You A New Oracle DBA?

Are you New Oracle DBA?

Oracle has been pioneer in developing and marketing database management system(DBMS). Oracle started version 6.0 and has released its latest version 11g on July 11th, 2007. Latest version of Oracle that comes with multi-tenancy capacity is the Oracle database 12c.

Each version comes out as an enhanced version of its predecessor with enriched features than its predecessor. Lets consider 9i where features which support Internet computing has been added and later came the 10g which supports grid computing.
1) Where to get best oracle 10g certification?
Its always a better idea to start with oracle certifications if you are an oracle niche. Details on oracle certifications is available in the link given below:
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=143
2) Where to take up the certification course?
There are plenty of institutes offering OCP certification training. Oracle has its University training people in various disciplines. Details can be found here:
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=16&group_id=1136
3) What will be the salary of a DBA fresher?
It depends on the organization. There are people joining organizations as junior DBA’s, Oracle consultants,junior database consultants, system analysts, data analysts, database designers.
In case of mid-sized organizations a fresher can expect a salary around 1.5-2lac. People joining larger organizations can expect between 2-5lac.
What are all the job duties of a Oracle DBA?
As opposed to regular office hours oracle database administration is a 5 to 9 job. If you are involved in supporting highly critical production env you are expected 24×7. Things apart here are the typical job duties of an Oracle DBA
1) Be partof capacity planning meeting that does involve business analyst, project manager, infrastrcture manager, senior DBA member, sales professional on an as needed basis. DBA is expected to provide information on database capacity planning. This will help the project manager determine cost of storage, servers, software license and all the factors needed for a project implementation. The ccapacity palnning meeting can also be a project meeting in some oroganizations.
2) Once the project does kick off , DBA should start with preparing the hardware, install the oracle software, create databases. With lastest 12c multi-tenancy architecture consolidation projects are underway. Hence, DBA may have to create both pluggable database form seed template as well as migrate stand-alone instances onto container database
3) Once the database is up and running, the development will proceed with product creation. At the end the entire bundle is released as an installer in case of product development firms. IF this is a service provider, this may not be needed
4) Upgrading current version of database to latest version is a typical job of DBA. This starts with analysing new features to determine the advantages of upgrade onto top management , testing upgrades, performing upgrades etc
5) Maintain consistent copy of database by properly planning full and incremental database backups. This is the first need in case of disaster recovery
6) Assist with developers to tune the queries utilizing oracle supplied tools
7) Monitor the database for alerts, space growth and add additional disk space on an as needed basis
8) Automate the routine job of DBA. This demands shell scripting knowledge these days
9) Latest technologies like RAC, ASM, Dataguard implementation to provide high availability, disaster recovery are some projects that has become a mandate
10) Work with cloud support engineer to migrate the existing env onto cloud like rackspace, AWS etc
11) Conduct business continuity testing in the form of disaster recovery meetings on a quarterly basis
12)Support mission critical systems 24×7 via pager, cell phone
Oracle DBA – Oracle database administrator is vested with the following responsibility as a quick summary :
1) Install Oracle software
2) Create Oracle database
3) Configure Oracle database parameters
4) create spfile from pfile – spfile is used for dynamic parameter changes
5) Start the oracle instance
6) Administer Oracle instance
7) Stop oracle instance
8) Move data across databases
9) Use datapump utilities – expdp/impdp
10) Use SQL*Loader utility
11) Transportable tablespaces feature
12) RMAN backup, restore, recovery
13) User managed backup technique
14) User managed recovery technique
15) Tuning instances
16) Tuning SQL
17) RAC – Real Application Cluster
18) Dataguard – Primary standby database, logical standby database configuration
19) V$views
20) Manage oracle objects
What are Daily Routine Tasks Of An Oracle DBA?
Monitoring for problems – running monitoring scripts or use the oracle built-in tools such as Enterprise manager.Check reports generated by monitoring scripts on a daily basis – disk space problems, memory allocation, disk I/O
Monitor alert log file – fix errors
Types of alert log errors – change some parameters, add some space, perform an administrative task
Use Metalink database and open a new tar with Oracle
Upgrade to latest versions – software, software and database( applications and DBA’s configuration parameters)
Test new applications to be deployed in the system

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner

Migrate temp tablespaces from dictionary managed to locally managed Oracle 12c

Oracle 12c offers provision to migrate temp tablespaces in older versions that are dictionary managed to locally managed tablespace. Though there is no provision to make use of dbms_space_admin and such direct approaches, this is possible by dropping and recreating the temporary tablespace
drop tablespace temp including contents; – This will remove the files physically as well
If there are transaction recovery in progress, this might take a while. Hence it would be better to issue following commands in addition to the above command
delete from fet$;
commit;
Now create a new temp tablespace that is locally managed using – EXTENT MANAGEMENT LOCAL

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner

Oracle 12c OMF Create tablespace without specifying datafile name

Tablespace is the logical structure that forms heart and soul of an Oracle database storing information in datafiles the physical files. This stays intact in Oracle database 12c as well. Oracle Managed Files aka OMF is an interesting feature that makes the file management easy and simple. Here the operations are specified at tablespace level and not at OS file level
So, how to we create a tablespace without specifying a datafile?
Oracle Managed Files feature makes this possible.
The default command to create a tablespace is as follows :
create tablespace learnersreferencedatafile ‘learnersreference.ora’ size 100MB;
By setting the parameter db_file_create_dest with scope at memory and spfile, now it is possible to create a tablespace by simply issuing:
alter system set db_create_file_dest=’location’ scope=both;
create tablespace learnersreference;
The above command sets default destination to create datafiles the folder within which physical files are created. Datafiles created using OMF will have autoextend ON with 100MB as default size. This is a smallfile tablespace by default. We cna choose to make it a BIGFILE tablespace. Include keyword BIGFILE
Is OMF applicable for tempfile?
Yes . We dont have to explicitly specify DATAFILE (or) TEMPFILE clause. Oracle internally takes care of it
Here is the simple command to create temporary tablespace using OMF;
alter system set db_create_file_Dest=’location’ scope=both;
create temporary tablespace temp1;
What is the significance of db_create_file_dest parameter?
In addition to specifying the location to create datafiles, tempfiles the location specified in db_create_file_dest becomes the default location for creating the following files:
1) Datafiles
2) Tempfiles
3) Redo log files
4) Control files
5) Block change tracking files

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner

RMAN Can’t Backup or Copy Active Files In NoArchivelog Mode


Oracle Recovery Manager is the backup solution from Oracle Corporation. Here are the steps I followed this morning to backup my oracle database running in NOARCHIVELOG mode. First connect to the database and issue BACKUP DATABASE command
1) In shell prompt type rman
2) Issue the following command in RMAN prompt to connect to the target database
RMAN>CONNECT TARGET ORACLE_SID
This will ask for target database password. On successfully issuing password, we’ll see connected to target database information
3) Issue BACKUP DATABASE command. This will end up in error as the database is in NOARCHIVELOG mode
RMAN> backup database;
Starting backup at 28-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/28/2010 08:09:04
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-APR-10
channel ORA_DISK_1: finished piece 1 at 28-APR-10
piecehandle=C:APPUsernameFLASH_RECOVERY_AREADatabase_SIDBACKUPSET2010_04_28O1_MF_NCSNF_TAG20100428T080904_5XJ9C258_.BKP tag=TAG20100428T080904co
mment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/28/2010 08:09:04
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
Reason behind ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode:
I tried to create a backup of the tablespace using the oracle rman utility. I’m making use of oracle database enterprise edition. I got the following error
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
This error is self-explanatory. We can backup a database, database storage structures like tablespace only if the database is in ARCHIVELOG mode. We need to bring the database into archivelog mode to overcome this problem. Connect to database using sql*plus as sys user with sysdba privilege.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1375792 bytes
Variable Size 679477712 bytes
Database Buffers 385875968 bytes
Redo Buffers 4603904 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
Now try the backup command and it works fine.
RMAN> backup tablespace users;
Starting backup at 27-SEP-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=C:APPorclORCLORCLUSERS01.DBF
channel ORA_DISK_1: starting piece 1 at 27-SEP-10
channel ORA_DISK_1: finished piece 1 at 27-SEP-10
piece handle=C:APPorclFLASH_RECOVERY_AREAORCLBACKUPSET2010_09_27O1_MF
_NNNDF_TAG20100927T172436_6B22WNFM_.BKP tag=TAG20100927T172436 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-SEP-10

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner