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;

 

 

 

Unix/Linux get started Lesson 01

 

Unix/Linux get started

What is Unix ?

The Unix operating system is a set of programs that act as a link between the computer and the user.

The computer programs that allocate the system resources and coordinate all the details of the computer's internals is called the operating system or the kernel.

Unix Architecture

·         Kernel − The kernel is the heart of the operating system. It interacts with the hardware and most of the tasks like memory management, task scheduling and file management.

·         Shell − The shell is the utility that processes your requests. When you type in a command at your terminal, the shell interprets the command and calls the program that you want. The shell uses standard syntax for all commands. C Shell, Bourne Shell and Korn Shell are the most famous shells which are available with most of the Unix variants.

·         Commands and Utilities − There are various commands and utilities which you can make use of in your day to day activities. cp, mv, cat and grep, etc. are few examples of commands and utilities. There are over 250 standard commands plus numerous others provided through 3^rd party software. All the commands come along with various options.

·         Files and Directories − All the data of Unix is organized into files. All files are then organized into directories. These directories are further organized into a tree-like structure called the filesystem.

 

Lets see how we log in to Linux:

 

login : root

root's password:

Last login: Mon Feb  5 09:58:32 2024 from 192.168.15.60

Change Password

#passwd

Changing password for root
(current) Unix password:******

New UNIX password:*******

Listing Directories and Files

All data in Unix is organized into files. All files are organized into directories. These directories are organized into a tree-like structure called the filesystem.

You can use the ls command to list out all the files or directories available in a directory. Following is the example of using ls command with -l option.

Who Are You?

Who is Logged in?

System Shutdown

The most consistent way to shut down a Unix system properly via the command line is to use one of the following commands −

1 halt Brings the system down immediately 2 init 0 Powers off the system using predefined scripts to synchronize and clean up the system prior to shutting down 3 init 6 Reboots the system by shutting it down completely and then restarting it 4 poweroff Shuts down the system by powering off 5 reboot Reboots the system 6 shutdown Shuts down the system