Hi Geeks, in this article we will see how to install Oracle Database 12C on a Linux host.  The Step by Step comes with all the commands and the video tutorial.  Please leave your comments and questions regarding the installation or you can like and share the article if you found it useful.

 

1 – Download the Oracle Database Software

Oracle Database 12C

2 – Create group and user that will install Oracle Database

groupadd dba groupadd oper useradd -G dba,oper -g oinstall oracle passwd oracle

3 – Create the folder where you install the Oracle Database

4 – Edit the sysctl.conf and limits.conf files and add the following parameter files

  • this values are required by Oracle Database

5 – Install all the required packages for Oracle Database to properly function

 

6 – Edit the host file to reflect the host name.

  • the ip and the hosts names are your choice.

7 –  Setup your environment variable for ORACLE user.

  • make sure you switch to Oracle user and add the following lines in the .bash_profile file.

Note: – this configurations will apply if you have used the same path as me, otherwise you will need change them accordingly.

8 – Unzip  the installer files

9 –  Install the Oracle Database

Note: Follow the video tutorial to see the rest of the Database Creation and validation.

 

Follow the Step by Step Oracle 12C installation Video Tutorial

How to Install Oracle Database 12C –  Part 1 Setup Host

Install Oracle Database 12C – Part 2 Install Oracle Database

 

Description or the error

When you encounter an ORA-00904 error, the following error message will appear:

  • ORA-00904: invalid identifier

Cause

You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

Solution

The option(s) to resolve this Oracle error are:

Rewrite your SQL to include a valid column name. To be a valid column name the following criteria must be met:

  • the column name must begin with a letter.
  • column name can not be longer than 30 characters.
  • name of the column must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
  • column name can not be a reserved word.

Let’s look at an example of how to resolve an ORA-00904 error. For example, if you ran the following SELECT statement, you would receive an ORA-00904 error:

This error was created by aliasing a column, but then mistyping the alias later. In this example, we created the alias called “name_id” for the name, but then called it as “nameid” in the ORDER BY clause.

To resolve this error, we can modify our SELECT statement to use the correct alias name in the ORDER BY:

Use this script to extract/display a specific user DDL(Data Definition), you must provide the script with a value for @user_ddl paramater.

Use with care and always test before you use.

 

a

    Starting up and shutting down of Oracle Database is a basic routine operation. But any other programmer or sysadmin  might end-up doing some basic DBA operations on development database. So, it is important for non DBA to understand some basic database administration activities.

So, let us see how to start and stop an Oracle database with simple examples.

How To Startup Oracle Database

1. Login to the system with oracle username

Typical oracle installation will have oracle as username and dba as group. On Linux, do su to oracle as shown below.

2. Connect to oracle sysdba

Make sure ORACLE_SID and ORACLE_HOME are set properly as shown below.


You can connect using either “/ as sysdba” or an oracle account that has DBA privilege.

3. Start Oracle Database

The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify PFILE.

Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.

  1. spfile$ORACLE_SID.ora
  2. spfile.ora
  3. init$ORACLE_SID.ora


Type “startup” at the SQL command prompt to startup the database as shown below.


If you want to startup Oracle with PFILE, pass it as a parameter as shown below.

How To Shutdown Oracle Database

Following three methods are available to shutdown the oracle database:

  1. Normal Shutdown
  2. Shutdown Immediate
  3. Shutdown Abort

1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

2. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

3. Shutdown Abort

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

After we have seen how we can create a cold backup of our Oracle database using RMAN in this article Complete Oracle Cold Backup Rotine now is time to see how we can restore the database using this cold backup.

restore

 For this task you need to follow the steps bellow:

1 – You need to shutdown your database.

  • connect to rman and run the bellow command.

 2 – Startup the database in nomount mode

 

3 – Restore the control file from the initial backup we took in the first article.

 

4 – Shutdown and startup the database in mount mode

 5 – From the RMAN command line CATALOG the backupset.

  • place the full path to the location of the created backupset from the first article.

 

6 – Restore the database.

  • once the backupset are cataloged we can restore our database.

 

To monitor the progress of your RMAN restore you can use this script bellow.

The script will return the progress in % per backup piece so it will reset to 0 every time it starts restoring from a new backup set.

 6.1 – The Restore command should return you the following when finished.

 7 –  Now we need to connect to the database via SQLPLUS and open it with resetlogs.

Great the database was restored using an COLD RMAN backup and the database is up and ready to receive connections.

To get a better idea on how RMAN backups work and what are the types of backup we can take using RMAN see this article Oracle RMAN Diagram.

 

Cold backup is done when database is not open. While performing cold backup using RMAN the database need to be in MOUNT mode. This is because RMAN needs the data file details which are available while database is in MOUNT mode.

Cold backup is also called consistent backup. This is because before bringing the database in MOUNT mode, the database is first shutdown with IMMEDIATE or TRANSACTIONAL option.

As this backup is a cold backup the, the archive log files will be not required to restore and recover the database. Hence archive log files are not added.

I use for this task two script :

cold_bkp.rman – is holding the rman commands

exec_cold_bkp.sh – is holding the environment variables and execution string.

This are the scripts i use for this task:

cold_bkp.rman

 

exec_cold_bkp.sh

  • the script will hold the environment variables need and also the rman connector string where you need to provide it with a tns entry and with a user and password account.

 How to run the script

  • you just need to make them execuables and run them.

 To monitor the execution just run tail on the log file.

 

 Make sure you follow up the next Article on how you can restore the cold backup we just did now on top of the same database.

 

 From time to time we will need to recomplie database objects. Among the many resons why objects become invalid are : upgrades, DDL changes, patches.

Although invalid objects are recompiled automatically on use, it is useful to run this script prior to operation because this will either eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime.
So in this article we will go thru some methods i normally use to recompile invalid objects.

Using the dba_objects table

We will start by using the dba_objects table to identify our invalid objects and we will build a query on top of those invalid objects so  the output will generate the sql syntax to recompile invalid objects.
– this method is good when you have a small number of invalid objects, maybe after a DDL change.

The output of this script will be some thing like this:

Just copy and paste the output to recomplie.

Also another method is using the EXEC DBMS_DDL package to perform the recompilations.

  • views and synonyms will not be recomplied using this method.

 

The same as with the previous script you will need to copy and paste the output in order to recomplie the invalid objects.

Last method i use is using the UTL_RECOMP package.

I use it after a major version upgrade. The UTL_RECOMP package contains two procedures used to recompile invalid objects.

To do it at Schema Level

  • Single threaded

 

  • Is executed in Paralell(you set the number of threads to be used)

 

To do at Database Level

  • Single threaded

 

  • Is executed in Paralell(you set the number of threads to be used)

 

Ok ,ok… here is what took me about 2 hours on my time today.
One colleague of mine cloned an Oracle Home to a new server and passed it to me to create a duplicate oracle database a production database into the new Oracle installed home.

So i am trying to login the empty instance using the sqlplus utility and i get the error:

What the hell …. !!!

All the parameters are set ORACLE_SID, ORACLE_HOME, TNS_ADMIN, etc..

What now ?

First thing i did i have commented the SQLNET.AUTHENTICATION_SERVICES parameter from the sqlnet.ora file but with no success.

Finally after many failed tests and try’s i have reached the solution thanks to post on stack page.

So here is the solution: 

There is a file in your $ORACLE_HOME/rdbms/lib called config.c. This file holds the os groups that the oracle users is included in.

This groups in my case the dba group was not declared in the config.c file, during the Oracle_Home cloning the groups were not replicated as on the source host.

Here is the content of the file after update:

After you update the config.c file you will need to generate a new config.o file. 

  • make copy of the config.o file before.

 

Re-link Oracle now

After all this is done try to connect to the instance using the sqlplus 

 

I would recommend you bounce the instance as well.

What the hell is this ?

Just got this error while moving an Oracle Home to another server in order to replicate my Oracle Database.

After doing some search on Oracle Meta-link site for a couple of hours i finally got it.

The problem was solved by doing the following:

  • Change the permissions to 777 and make oracle user the owner of this file

/oraInventory/ContentsXML/comps.xml

  • Remove the directory /oraInventory/locks
  • Remove the file under $ORACLE_HOME/.patch_storage  —  if it exists
  • Change the permissions of /oraInventory to “777” and make oracle user the owner

After this i could list my Oracle Inventory using the opatch tool with no problem.

Standby Sequence Difference

Few days ago for a difficult reasons to explain i lost a production an Oracle database and i had to restore it for a tape backup.(we lost few hours of data). I had standby database attached to the production database that was in sync and it got corrupted as well  meaning that i had to rebuild my standby from scratch.
Well so far so good i put the production database back online and duplicated it do a standby as it was before but in my monitoring dashboard(i am using Nagios as an alternative monitoring tool) the Primary database sequence was way ahead of my Standby database sequence even thou in my Standby database alert.log the recovery process was doing correct recovery and the status of it reached the “transit” state.

What the hell is going on ? As my database is replicating the data in the correct manner but still i get this error !

 Let’s start digging to see why this happens and how to fix it !

  • First i will check my Production Sequence# 

 

  • Next i will check my Standby last applied  Sequence# 

 Hmmmm … how come ?

  •  Let’s see now the the archive logs list on my Production database

Well here is the problem ! My logs sequence have been reinitialized(reset), and this was my mistake  when i restored the database.

  •  So to make sure this was the case we will check the RESETLOGS_CHANGE# value stored in the v$archived_log table.

It is exactly what happened !!! My old incarnation sequence numbers are still registered in my v$archived_log table. (we can fix this by re-creating the controlfile but i don’t want any downtime right now)

So now that i know that the Standby is getting all the updates and is in sync with my production database i need to fix my monitoring system so that he can recognize that.

Here is the script that my Nagios is using to monitor the replication.

-it uses the result of the query and matches with the result from the standby. This is not going to work, i need to write a query that will recognize the most recent incarnation.

So here the query i managed to come up with after doing some goggling…

This the query as per Nagios usage(notice the escape \ signs), you can remove the \ and test is inside your Production database.

Now my monitoring dashboard is green and my manager is happy .

 

 

 

 

 

This are the commands that you need to Start and Stop a running Oracle Standby Database in Oracle 11g.

  • this scripts might be used when you need to do maintenance on your Oracle Standby Database for what ever reason.

 

Syntax to Stop a running Oracle Standby Database

  • the commands will stop the recover process which is identified as the MRP0 process, followed by the “shutdown immediate” – a clean shutdown of the database.

 

Syntax to Start a Oracle Standby Database

 

 

 

 

 

Arquivo de Controle/ControlFile é um componente físico crucial de cada banco de dados Oracle.
Multiplexagem de arquivos de controle a vários sistemas de arquivos diferentes diminui a probabilidade de perder arquivos de controle

  • 1-Como localizar e identificar os arquivos de controle.
  • 2-Altere o parâmetro control_files, de modo que irá representar os novos locais de arquivo de controle.
  • 3-Desligue seu banco de dados.
  • 3-Mova o controlfile para o novo local.
  • 4-Inicie o seu banco de dados e verifique se o novo local foi armazenado.
  • Vamos ver como podemos alterar uma senha de usuário no Oracle.

      

    Alterando a senha do usuário pode ser muito fácil, mas o que, quando queremos redefinir a senha do usuário bloqueado para a senha antiga, mesmo que você não sabe disso! Isso é útil no caso de usuários Db_links bloqueadas ou usuários de gestão de idade.

      
      
     

  • Passo 1 – você precisa executar o script a seguir. Isto irá pedir o nome de usuário que você o que para redefinir a senha.


  • Exemplo:

  • Step 2 – Execute o resultado gerado pelo último script:
  • Isso irá redefinir a senha para o usuário “DBSNMP” para a última senha.

    Isto pode ser útil quando chegarmos “ORA-28000: a conta está bloqueada”. erro

    AODBA

    Aqui está uma lista de artigos que irá mostrar-lhe como instalar, usar e entender de banco de dados Oracle.
       Nenhum dos artigos representa Oracle Corporation de qualquer forma, e não é aconselhado ser usados em ambientes de produção antes de teste. Então aodba.com não será responsabilizado por quaisquer danos que o materiais apresentados irá produzir ao quaisquer ambientes de banco de dados.
    Use com cuidado.

    Oracle Audit

    In this article we will talk about Oracle Audit features.
    By default actions like “Connections to the instance with administrator privileges; Database start-up; Database shutdown“are enabled by default.

    How to enable Audit in my Oracle database ?

    Oracle parameters AUDIT_SYS_OPERATIONS and AUDIT_TRAIL used for audit purpose are STATIC, this means that you will have to shutdown and startup your database instance in order for them to work.
    Here is an example of this can be done:
    – we will set the audit_trail to the DB values, this will direct all audit records to the database audit trail.
    List of SYS.AUD$ views

    • DBA_AUDIT_EXISTS
    • DBA_AUDIT_OBJECT
    • DBA_AUDIT_SESSION
    • DBA_AUDIT_STATEMENT
    • DBA_AUDIT_TRAIL
    • DBA_OBJ_AUDIT_OPTS
    • DBA_PRIV_AUDIT_OPTS
    • DBA_STMT_AUDIT_OPTS


    Where is the audit data stored ?

    The parameter responsible for this is called AUDIT_TRAIL
    Types of audit_trails:

    • none – Disables database auditing.
    • os – Records the information to a o/s file (uses parameter audit_file_dest for the file location)
    • db – Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).
    • db,extended – Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.
    • xml – Enables database auditing and writes all audit records to XML format OS files.
    • xml,extended – Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.


    What can we monitor with Oracle Audit in our database ?

    Types of Audit Levels

    • Normal/Standard Database Activity Audit
    • Suspicious Database Activity Audit
    • Administrative Users Audit

    Normal Database Activity Audit

    When planning to do Normal/Standard Database Audit we need to know what type of option we will use.
    Standard auditing support 4 type of audit option level:
    Statement Audit
    – this option will create audit records of a specific SQL statements or groups of statements that affect a particular type of database object.
    Example:
    -the “audit create table” will only audit create table and not alter or drop table statements.
    To get a list of audible statements just query this view:

    – there about 268 options.



    Privilege Audit
    When using the Privilege option all privileges can be audited,

    To get a list of audible statements just query this view system_privilege_map:

    – there about 208 Privilege audit options.
    Object Audit

    • The options available for this type of audit are :

    alter, audit, comment, delete, grant, index, insert, lock, rename,select, update, reference and execute .
    The syntax use is

    Here is an example of object audit



    Also is important to know how to Disable Standard Audit Options

    For this task the command is NOAUDIT . Is good to know that when using the NOAUDIT command this will not turn off in the entire Oracle Database for this we need to alter the AUDIT_TRAIL parameter and set it to the values of none.
    The NOAUDIT command will will disable the audit as per audit option.
    Syntax used to disable audit

    To disable the audit select from the TEST_AUDIT user:

    To disable all of the privilege audits

    – in this case all of the privileges audits will be disabled.

    To disable all of the object audits on a specific object

    – in this case all of the tbl_audit object audits will be disabled.

    Suspicious Database Activity Audit

    In this case specific audit options should be enabled.
    This process should continue until enough evidence is gathered to draw conclusions about the origin of the suspicious database activity.

    Administrative Users Audit

    This audit option is all about the admin account usage.
    The AUDIT_SYS_OPERATIONS initialization parameter is used to specify whether such users are to be audited.



    • By default this parameter is set to FALSE

    Since is a static parameter you will have to stop and start your database instance in order for the audit to be enabled.

    How to enable the Administrative Users Audit ?

    Finding the location for the Administrative Users Audit
    Different from the other types of audit the “Administrative Users Audit” is stored in the location of the AUDIT_FILE_DEST parameter.
    To see the location



    So the audit is enabled now and we know where the audit file is, now let’s see the content of it:



    To disable the Administrative Users Audit you will have to alter the parameter AUDIT_SYS_OPERATIONS to FALSE.

    I hope this was helpful
    If you liked the article share and follow for more articles on Database techs.

    Database Scripts

    The script will generate the GATHER_TABLE_STATS script for all the tables that don’t have statistics up to data.
    You can do it for the entire database instance or per user/owner/schema.

    Database Scripts

    The script will calculate the optimal size of your undo retention for the period you have set in your database.

    Database Scripts

    The script will list your tablespace state / backup mode in Oracle

    Database Scripts

    The script will list top 10 objects that used Buffer Cache Block per Object

    The output should be something like this

    When you have an Oracle Standby Database sometimes you need to put it in READ ONLY mode for what ever the reason would be.
    To put the Standby database in READ ONLY mode you need to stop the Recover process and alter it’s state to READ ONLY by using the following commands.

    To put it back in Recover mode you just need to shut it down and startup the recover from mount mode.

    I hope this was helpful.

    Database Scripts

    The script will give you an report of the status of you RMAN running backup, you will get the start time ,
    actual time size and amount of work done so far.

    Database Scripts

    The Script will give a report on the RMAN backups.
    You will get the Start date and the End Date also the size of the backup in MB along with the status.
    Also the backup type, time it took it to finish.

    Database Scripts

    The script will list all RMAN backups that have finished in the last 24 hours and are FULL , you get the status, start time and end time

          Sometimes because of space requirements or other reasons we need to change the FRA(Flash Recovery Area) location.
    This task requires you to stop and start your Oracle instance.
    I will go thrum all of the steps that are needed to be done in order to complete the relocation of your
    FRA(Flash Recovery Area) location.

    1 – You need to have your new area created(the directory) and make sure that the Oracle user has the appropriate rights over the directory.

    2 – Open a SQL command line and alter the value of the DB_RECOVERY_FILE_DEST parameter using the bellow syntax:

    At this point the all new Flash Recovery Area files will be created in the new location.

    3- Make the new changes available at this point you need to restart your database

    4- Move your current permanent files, transient files, to the new Flash Recovery Area, then follow the following steps:

    4.1 — To move the existing backup sets and archived redo log files,use the following

    command:

    4.2 — To move the datafile copies. Run the below command for each data file copy:

    -where the is the datafilecopy name in the old recovery area.

    4.3 — To move the control file from the old Flash Recovery Area to new one. Run the
    following command with database in NOMOUNT state.

    4.4 — To move the online redo logs. Use the commands to add a a log file stored in
    the new Flash Recovery Area and drop the logfile in the old Flash Recovery Area
    for each redo log group.

    Oracle will clean up transient files remaining in the old Flash Recovery Area
    location as they become eligible for deletion.

    Database Scripts

    The script will provide important information about the Memory Allocation on your database present session.

    Database Scripts

    The script will list who is using the undo space. You will get the session id ,user name , sql id ,undoseg id , size and session status.

    AODBA

    You need to drop the TEMP files before running this command, You can use the following script to generate drop SQL to resolve this NID-00137 error.



    After the temp table spaces are dropped and the NID command is executed with no problems you can recreate your temp table spaces.

    How to drop an Oracle database using SQL Plus commands:

  • 1- Setup your Oracle SID
  • In this example my database SID is DB01.

  • Shutdown the database:
  • Start up your database in mount – exclusive – restrict mode:
  • Now issue the  drop database command.
  • During the drop database command your data files and control files will be erased from your operation system.

    Working as a DBA you need to recompile your database objects sometimes.
    These is needed when you create new objects or recreate some objects that make reference of others.

    A easy way to do this is by using the build-in scripts that Oracle provide in your Oracle installation.

    The name of the script is utlrp.sql. let’s see how we can use this script.

  • Locate your utlrp.sql script.


  • Now login as sys dba and run the script.