Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux :

 

Download Software

edelivery: 

https://edelivery.oracle.com/osdc/faces/Home.jspx;jsessionid=IL9VS4CSe4Gr1jMumqbTqZSJMjgvGRIHGJHgCDvtpK1fvcgudfRr!-1269853860

 

 

Unpack Files

Unzip the files.

unzip V38500-01_1of2.zip

unzip V38500-01_2of2.zip

Oracle Installation Prerequisites

Perform either the Automatic Setup or the Manual Setup to complete the basic prerequisites. The Additional Setup is required for all installations.

Automatic Setup

yum install -y oracle-database-preinstall-19c

 

Manual Setup

If you have not used the "oracle-database-preinstall-19c" package to perform all prerequisites, you will need to manually perform the following setup tasks.

Add the following lines to the "/etc/sysctl.conf" file, or in a file called "/etc/sysctl.d/98-oracle.conf".

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

 

Run one of the following commands to change the current kernel parameters, depending on which file you edited.

/sbin/sysctl -p

 

Add the following lines to a file called "/etc/security/limits.d/oracle-rdbms-server-12cR1-preinstall.conf" file.

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

 

Create the new groups and users.

groupadd -g 54321 oinstall

groupadd -g 54322 dba

groupadd -g 54323 oper

useradd -u 54321 -g oinstall -G dba,oper oracle

 

Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

Once the change is complete, restart the server or run the following command.

# setenforce Permissive

If you have the Linux firewall enabled, you will need to disable or configure it, as shown here. To disable it, do the following.

# systemctl stop firewalld

# systemctl disable firewalld

 

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/12.1.0.2/dbhome_1

chown -R oracle:oinstall /u01

chmod -R 775 /u01

 

 Oracle profile

vi .bash_profile

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

 

ORACLE_BASE=/u01/app/oracle;

export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/ product/12.1.0.2/dbhome_1;

export ORACLE_HOME

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;

export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

export CLASSPATH

Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=test.itsinc.com:0.0;

 export DISPLAY

./runInstaller

 

Run the root scripts when prompted.

As a root user, execute the following script(s):

        1. /u01/app/oraInventory/orainstRoot.sh

        2. /u01/app/oracle/product/19.0.0/dbhome_1/root.sh

 

Post Installation

Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

DEMO/u01/app/oracle/product/12.1.0.2/dbhome_1:Y

 

Tablespace Point-in-time Recovery

 Why Tablespace PITR Recovery?

Let us assume that you have a problem only with one user and the transaction that the user executed has impacted one table that reside under one tablespace or one data file. So rather than performing the entire database point in time recovery, We can perform single tablespace point in time recovery just before the transactions were issued. In this way, only the affected tablespace will not be available for the users. Rest all database will still be up and running.

How it works :

in TBPITR, we take the tablespace back in time or before the wrong transactions are issued.

 

 

TSPITR Recovery Methods:

There are three methods which you can use to recover dropped tablespace:

1. Create DB clone with PITR before tablespace drop, export tablespace from clone DB and import into original database
2. Perform entire database Point In Time recovery. This lead to downtime + loss of data
3. Use automated RMAN TSPITR method.

 

Lab Activity:=============

 

 

CREATE TEST TABLESPACE.

 

create tablespace TBPITR datafile '/u01/app/oracle/oradata/OEM/tbpitr.dbf' size 10M;

 

 

create a new emp table.

 

create table testdemo (id int, name varchar2(200));

insert into testdemo(id , name) VALUES(1,’test’);

 

Take RMAN backup for the activity. I am taking database backup in a specific location.

 

RMAN> backup database plus archivelog format '/u01/backup/oemdb_%U';

Query to check database size:

 

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

DROP THE TABLESPACE AND START TBPIT PROCESS

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

 

Session altered.

 

SQL> select sysdate from dual;

 

SYSDATE

-------------------

24/06/2024 10:44:19

 

drop tablespace TBPITR including contents and datafiles;

 

CONNECT TO RMAN AND START RECOVERY:

 

 

run{

recover tablespace TBPITR until time "to_date('22-jun-24 11:01:53','dd-mon-rr hh24:mi:ss')"

auxiliary destination '/u01/backup/auxdest';

}

Check the original database if the tablespace restore is done or not

select status,tablespace_name from dba_tablespaces;

Once tablespace restore is done, it will be in offline mode. Make it online

alter tablespace TBPITR online;

select status,tablespace_name from dba_tablespaces;

Start up and shut down the Oracle Database Instance

 

1. Background SQL>  shutdown

An Oracle database system consists of an Oracle database and an Oracle instance. An Oracle instance (also known as a database instance) contains the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work. In this tutorial you learn how to manage your Oracle Database instance.

 

                               

1

Start up and shut down the Oracle Database Instance

You may need to shut down the database instance to perform certain administrative tasks. To shut down and restart the database instance, perform the following steps:

2.      Open a terminal window as the oracle user. Execute the oraenv command to set the environment variables.

3.      Log in to SQL*Plus as the SYSDBA user

4.      Issue the SHUTDOWN command to close the database and shut down the instance.

5. SQL>  shutdown

6. Issue the STARTUP command to start the instance and open the database.

SQL> startup

 

Backup based RMAN Duplicate database || Migrate and Clone database to another server using RMAN Duplicate | Cloning Oracle database

 

Oracle database (ENTOPDBA)

YouTube: https://www.youtube.com/@entopdba501

Backup based RMAN Duplicate database.

 

Ø  Duplicate a database from its backups without connecting to source database.

Ø  This technique is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions.

Ø  In duplication without a TARGET connection, the source database is unaffected by the duplication.

Ø  RMAN duplication, we will connect rman to the auxiliary instance and run duplicate command.

Ø  In this Demo we are doing RMAN Cloning on same server.

Ø  Please plan Hardware requirement such as Storage,CPU,RAM before perform duplicate.

2. Environment

 

Hostname                                  : test

Source DB Name                  : OEM       it’s a production database we want to clone it into test database

Duplicate DB Name                 : TEST

Version                                           : 11.2.0.4

Filesystem                                  : Normal

Non-RAC                                       : Single Instance

Oracle Home                             : /u01/app/oracle/product/11.2.0/dbhome_1

3. Backup Source Database

 

Take full backup/ full backup script  (PROD database)

4. Create PFILE from source db and Edit OEM to TEST

SQL> create pfile='/home/oracle/initTEST.ora' from spfile;

[oracle@test tmp]$ cat initTEST.ora

 

5. Create required Directories

[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/TEST/adump

[oracle@test ~]$ mkdir -p /u01/oracle/oradata/TEST

[oracle@test ~]$ mkdir -p /u01/oracle/TEST/fast_recovery_area

[oracle@test ~]$ mkdir -p /u01/oracle/archive/TEST

6. Copy Password for Clone Database

[oracle@test ~]$ cd /u01/app/oracle/product/11.2.0.4/dbs/

[oracle@test dbs]$ ls -ltr orapwOEM

-rw-r-----. 1 oracle dba 1536 Nov  7 11:40 orapwOEM

[oracle@test dbs]$

[oracle@test dbs]$ cp orapwOEM orapwTEST

[oracle@test dbs]$

[oracle@test dbs]$ ls -ltr orapwOEM*

-rw-r-----. 1 oracle dba 1536 Nov  7 11:40 orapwOEM

-rw-r-----. 1 oracle dba 1536 Nov  9 07:58 orapwTEST

7. Add oratab entry

 

[oracle@test ~]$ echo "TEST:/u01/app/oracle/product/11.2.0.4:N" >> /etc/oratab

[oracle@test ~]$ cat /etc/oratab | grep -i TEST

TEST:/u01/app/oracle/product/11.2.0.4:N  <-----

[oracle@test~]$

 

8. Startup Nomount TEST

[oracle@rac1 ~]$ . oraenv

ORACLE_SID = [TEST] ? TESTARC

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@test ~]$

 

[oracle@rac1 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 9 08:11:25 2018

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup nomount pfile='/tmp/initTEST.ora';

SQL> create spfile from pfile='/home/oracle/initTEST.ora';

SQL> shut immediate;

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL>

SQL> startup nomount;

ORACLE instance started.

9. Run RMAN Duplicate

vi restore_duplicate.sh 

execute the script to restore database

oracle# ./restore_duplicate.sh

10. Verify DB ID for Clone DB (TEST)

SQL> select name,open_mode,dbid from v$database;

 

11. Add TNS entry

TESTARC =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = test.itsinc.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = TEST)

    )

  )

 

12. Verify connectivity

Sqlplus / as sysdba

SQL> select open_mode from v$database;