HOW TO RESTORE ARCHIVE LOG FROM RMAN BACKUP

                             
                        Steps to restore missing or deleted archive logs from rman backup :


[root@localhost Desktop]# su - oracle
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 22 16:15:48 2019

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival                Enabled
Archive destination            +ORA_FRA
Oldest online log sequence       42
Next log sequence to archive    44
Current log sequence           44
   
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>/

System altered.

SQL> archive log list;

Database log mode        Archive Mode
Automatic archival              Enabled
Archive destination          +ORA_FRA
Oldest online log sequence        47
Next log sequence to archive     49
Current log sequence            49

(I generated new archives by commenting alter system switch logfile)

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

[oracle@localhost ~]$ su - oraasm
[oraasm@localhost ~]$ asmcmd
ASMCMD> cd /ORA_FRA/ORCL/ARCHIVELOG/2019_11_22/
ASMCMD> lsASMCMD> ls
thread_1_seq_33.278.1025001321
thread_1_seq_34.285.1025006701
thread_1_seq_35.282.1025006703
thread_1_seq_36.284.1025006701
thread_1_seq_37.286.1025006701
thread_1_seq_38.281.1025006703
thread_1_seq_39.283.1025006701
thread_1_seq_40.280.1025006703
thread_1_seq_41.279.1025006703
thread_1_seq_42.287.1025004541
thread_1_seq_43.288.1025004593
thread_1_seq_44.289.1025021797
thread_1_seq_45.290.1025021797
thread_1_seq_46.291.1025021799
thread_1_seq_47.292.1025021803
thread_1_seq_48.293.1025021813
ASMCMD> exit

[oraasm@localhost ~]$ exit
logout
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 22 16:20:16 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1545186911)

RMAN> backup database plus archivelog;


Starting backup at 22-NOV-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_34.279.1025001919 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 34
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_35.280.1025001919 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 35
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_36.281.1025001923 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 36
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_37.282.1025002441 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 37
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_38.283.1025002443 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 38
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_39.284.1025002445 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 39
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_40.285.1025002447 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 40
archived log +ORA_FRA/orcl/archivelog/2019_11_22/thread_1_seq_41.286.1025002449 not found or out of sync with catalog
trying alternate file for archived log of thread 1 with sequence 41
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=14 STAMP=1024651471
input archived log thread=1 sequence=29 RECID=15 STAMP=1024742576
input archived log thread=1 sequence=30 RECID=16 STAMP=1024743125
input archived log thread=1 sequence=31 RECID=17 STAMP=1024743168
input archived log thread=1 sequence=32 RECID=18 STAMP=1024914078
input archived log thread=1 sequence=33 RECID=19 STAMP=1025001324
input archived log thread=1 sequence=34 RECID=31 STAMP=1025006701
input archived log thread=1 sequence=35 RECID=34 STAMP=1025006702
input archived log thread=1 sequence=36 RECID=32 STAMP=1025006701
input archived log thread=1 sequence=37 RECID=30 STAMP=1025006701
input archived log thread=1 sequence=38 RECID=35 STAMP=1025006702
input archived log thread=1 sequence=39 RECID=33 STAMP=1025006702
input archived log thread=1 sequence=40 RECID=36 STAMP=1025006702
input archived log thread=1 sequence=41 RECID=37 STAMP=1025006702
input archived log thread=1 sequence=42 RECID=28 STAMP=1025004540
input archived log thread=1 sequence=43 RECID=29 STAMP=1025004593
input archived log thread=1 sequence=44 RECID=38 STAMP=1025021797
input archived log thread=1 sequence=45 RECID=39 STAMP=1025021798
input archived log thread=1 sequence=46 RECID=40 STAMP=1025021799
input archived log thread=1 sequence=47 RECID=41 STAMP=1025021803
input archived log thread=1 sequence=48 RECID=42 STAMP=1025021813
input archived log thread=1 sequence=49 RECID=43 STAMP=1025022031
channel ORA_DISK_1: starting piece 1 at 22-NOV-19
channel ORA_DISK_1: finished piece 1 at 22-NOV-19
piece handle=/apps/oracle/rdbms/product/11.2.0/db_home/dbs/0quhh62g_1_1 tag=TAG20191122T162031 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 22-NOV-19

Starting backup at 22-NOV-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+ORA_DATA/orcl/datafile/system.260.1017406203
input datafile file number=00002 name=+ORA_DATA/orcl/datafile/sysaux.261.1017406247
input datafile file number=00003 name=+ORA_DATA/orcl/datafile/undotbs1.262.1017406285
input datafile file number=00004 name=+ORA_DATA/orcl/datafile/users.264.1017406313
channel ORA_DISK_1: starting piece 1 at 22-NOV-19
channel ORA_DISK_1: finished piece 1 at 22-NOV-19
piece handle=/apps/oracle/rdbms/product/11.2.0/db_home/dbs/0ruhh62o_1_1 tag=TAG20191122T162040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-NOV-19

Starting backup at 22-NOV-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=50 RECID=44 STAMP=1025022066
channel ORA_DISK_1: starting piece 1 at 22-NOV-19
channel ORA_DISK_1: finished piece 1 at 22-NOV-19
piece handle=/apps/oracle/rdbms/product/11.2.0/db_home/dbs/0suhh63i_1_1 tag=TAG20191122T162106 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-19

Starting Control File and SPFILE Autobackup at 22-NOV-19
piece handle=/apps/oracle/rdbms/product/11.2.0/db_home/dbs/c-1545186911-20191122-01 comment=NONE
Finished Control File and SPFILE Autobackup at 22-NOV-19

RMAN> exit
Recovery Manager complete.

[oracle@localhost ~]$ su - oraasm
[oraasm@localhost ~]$ asmcmd
ASMCMD> cd /ORA_FRA/ORCL/ARCHIVELOG/2019_11_22/
ASMCMD> ls
thread_1_seq_33.278.1025001321
thread_1_seq_34.285.1025006701
thread_1_seq_35.282.1025006703
thread_1_seq_36.284.1025006701
thread_1_seq_37.286.1025006701
thread_1_seq_38.281.1025006703
thread_1_seq_39.283.1025006701
thread_1_seq_40.280.1025006703
thread_1_seq_41.279.1025006703
thread_1_seq_42.287.1025004541
thread_1_seq_43.288.1025004593
thread_1_seq_44.289.1025021797
thread_1_seq_45.290.1025021797
thread_1_seq_46.291.1025021799
thread_1_seq_47.292.1025021803
thread_1_seq_48.293.1025021813
thread_1_seq_49.294.1025022031
thread_1_seq_50.295.1025022067

Here by deleting some archives manually;

[ Note: Before deleting archives make sure that you have taken full database backup ]

ASMCMD> rm thread_1_seq_36.284.1025006701 thread_1_seq_37.286.1025006701 thread_1_seq_38.281.1025006703 thread_1_seq_39.283.1025006701 thread_1_seq_40.280.1025006703 thread_1_seq_41.279.1025006703 thread_1_seq_42.287.1025004541

ASMCMD> exit
[oraasm@localhost ~]$ exit
logout
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 22 16:25:09 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1545186911)

RMAN> run
{
set archivelog destination to '+ORA_FRA/ORCL/ARCHIVELOG/ARCHIVELOG1';
restore archivelog from logseq=36 until logseq=42;
}

(You can set archivelog destination to '     '; whatever as present )
( I have deleted archivelog from logsequence 36 to 42 so i have mentioned above)

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 22-NOV-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=+ORA_FRA/ORCL/ARCHIVELOG/ARCHIVELOG1
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=37
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=40
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=42
channel ORA_DISK_1: reading from backup piece /apps/oracle/rdbms/product/11.2.0/db_home/dbs/0quhh62g_1_1
channel ORA_DISK_1: piece handle=/apps/oracle/rdbms/product/11.2.0/db_home/dbs/0quhh62g_1_1 tag=TAG20191122T162031
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-19

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ su - oraasm
Password:
[oraasm@localhost ~]$ asmcmd
ASMCMD> cd /ORA_FRA/ORCL/ARCHIVELOG/ARCHIVELOG1
ASMCMD> ls
1_34_1017406175.arch
1_35_1017406175.arch
1_36_1017406175.arch
1_37_1017406175.arch
1_38_1017406175.arch
1_39_1017406175.arch
1_40_1017406175.arch
1_41_1017406175.arch
1_42_1017406175.arch

ASMCMD> exit

                   
                            To restore archivelg from rman backup is completed successfully.


                                               
                                                     "Thankyou, Have a nice-day"

HOW TO RESTORE A DROPPED TABLE

  We can restore a table that we  dropped unfortunately or knowingly by the help of below steps :


[oracle@localhost ~]$ sqlplus '/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 6 10:38:48 2019

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size     2254952 bytes
Variable Size   503318424 bytes
Database Buffers 1627389952 bytes
Redo Buffers     4923392 bytes
Database mounted.
Database opened.


SQL> conn football/messi

Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select * from tab;

TNAME               TABTYPE CLUSTERID
------------------------------ ------- ----------
BARCELONA                           TABLE
MANCHESTERCITY          TABLE
TOTTENHAM                           TABLE

SQL> drop table BARCELONA;

Table dropped.

SQL> drop table manchestercity;

Table dropped.

SQL> select original_name from dba_recyclebin;

ORIGINAL_NAME
--------------------------------
BARCELONA
MANCHESTERCITY


SQL> flashback table barcelona to before drop;

Flashback complete.

SQL> flashback table manchestercity to before drop;

Flashback complete.

SQL> select * from tab;

TNAME        TABTYPE CLUSTERID
------------------------------ ------- ----------
BARCELONA                      TABLE
MANCHESTERCITY          TABLE
TOTTENHAM                      TABLE


SQL>

HOW TO MOVE TABLE FROM ONE TABLESPACE TO ANOTHER TABLESPACE

           Here the easy steps to move a table from one tablespace to another tablespace !!


SQL> select  tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
KAVIN
FOOTBALL

6 rows selected.

SQL> create table spurs (playername VARCHAR(20),goals INT);

Table created.

SQL> insert into spurs (playername,goals)

  2  values ('Harry kane',187);

1 row created.

SQL> select * from tab;

TNAME        TABTYPE CLUSTERID

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

SPURS        TABLE

SQL> show user;

USER is "FOOTBALL"

SQL> 

SQL> select * from tab;

TNAME        TABTYPE CLUSTERID

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

SPURS        TABLE

SQL> alter table spurs move tablespace kavin;

Table altered.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME        TABLESPACE_NAME

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

SPURS                 KAVIN

SQL> alter table spurs move tablespace football;

Table altered.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME        TABLESPACE_NAME

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

SPURS                    FOOTBALL

SQL> 

HOW TO RESIZE REDOLOG FILES

 We cannot resize the redolog files .We must drop the redo log files and recreate .This           is only method to resize the redo log files. 

SQL> startup;

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size     2254952 bytes

Variable Size   503318424 bytes

Database Buffers 1627389952 bytes

Redo Buffers     4923392 bytes

Database mounted.

Database opened.

SQL> select group#,status,member from v$logfile;

GROUP# STATUS

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

MEMBER

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

3

/apps/oradata/redologs1/redo03.log

2

/apps/oradata/redologs1/redo02.log

1

/apps/oradata/redologs1/redo01.log

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

INACTIVE   1 50

CURRENT 2 50

UNUSED   3 50

[ ( SQL> alter database logfile group 1 '/apps/oradata/redologs1/redo01.log' resize 100m;

alter database logfile group 1 '/apps/oradata/redologs1/redo01.log' resize 100m

                       *

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE 

* We cannot easily drop a redolog file by using above sql queries) ]

SQL> alter system switch logfile;

System altered.

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

CURRENT  1 50

ACTIVE     2 50

ACTIVE     3 50

SQL> alter system switch logfile;

System altered.

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

INACTIVE   1 50

CURRENT  2 50

INACTIVE   3 50

Now we can drop the inactive redolog group 1 & 3;

SQL> alter system checkpoint global;

System altered.

( The following set of operations starts when checkpoint occurs: Log records from log buffer (including the last log record) are written to the disk  )

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m;

alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m

*

ERROR at line 1:

ORA-00301: error in adding log file '/apps/oradata/redologs1/redo01.log' - file

cannot be created

ORA-27038: created file already exists

Additional information: 1

*We have remove or delete the old redologs in OS level command :

SQL> exit

Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

[oracle@localhost dbs]$ cd /apps/oradata/redologs1/

[oracle@localhost redologs1]$ ls

redo01.log  redo02.log  redo03.log  redolog3a.log

[oracle@localhost redologs1]$ rm redo01.log

[oracle@localhost redologs1]$ ls

redo02.log  redo03.log  redolog3a.log

[oracle@localhost redologs1]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 11:28:35 2019

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

Connected to:

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

CURRENT   2 50

INACTIVE   3 50

SQL> alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m;

Database altered.

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

UNUSED        1 100

CURRENT     2 50 

INACTIVE   3 50

   In beginning the size of redolog group 1 is of 50m and by now the group 1 is resized as 100m

                                    The simplest way to resize a redo logs is done !! 

HOW TO INSTALL ORACLE 11G DATABASE SOFTWARE IN LINUX

             HOW TO INSTALL  ORACLE 11G DATABASE SOFTWARE IN LINUX !!


[root@localhost ~]#useradd oracle
[root@localhost ~]#groupadd oinstall
[root@localhost ~]#groupadd dba
[root@localhost ~]#usermod -g oinstall -G dba oracle

[root@localhost apps] mkdir -p oracle/oracle_base/rdbms/product/11.2.0/db_home/

[root@localhost oradata]# mkdir datafiles redologs controlfiles udump bdump cdump
[root@localhost oradata]#ls
controlfiles  datafiles  redologs udump bdump cdump

[root@localhost apps]#  mkdir -p oradump/software


[root@localhost /]# chown -R oracle:oinstall apps

drwxr-xr-x.   5 oracle oinstall  4096 Nov 19  2018 apps
dr-xr-xr-x.   2 root   root      4096 Aug 30 12:28 bin
dr-xr-xr-x.   5 root   root      4096 Nov 20  2018 boot


[root@localhost apps]# cd oradump/software/
[root@localhost software]# ll
total 2487208
-rwxr-xr-x. 1 oracle oinstall 1395582860 Aug 30 12:39 p13390677_112040_Linux-x86-64_1of7.zip
-rwxr-xr-x. 1 oracle oinstall 1151304589 Aug 30 12:42 p13390677_112040_Linux-x86-64_2of7.zip

[root@localhost software]# unzip p13390677_112040_Linux-x86-64_1of7.zip


[root@localhost software]# unzip p13390677_112040_Linux-x86-64_2of7.zip

[root@localhost software]# su - oracle

[oracle@localhost ~]$ cd /apps/oradump/software/
[oracle@localhost software]# ls

database
p13390677_112040_Linux-x86-64_2of7.zip
p13390677_112040_Linux-x86-64_1of7.zip

[oracle@localhost software]$ cd database/
install      response  runInstaller  stage
readme.html  rpm       sshsetup      welcome.html

[oracle@localhost database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 13238 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3999 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-08-30_01-02-45PM. Please wait ...[oracle@localhost database]$

By typing the ( yum install package_name)  utility install the required packages;

[root@localhost software]# yum install elfutils-libelf-devel*Complete!
[root@localhost software]#

 Login as root and install the above packages ;

[root@localhost ~]# /apps/oracle/oraInventory/orainstRoot.sh
[root@localhost ~]# /apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/root.sh


[oracle@localhost ~]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_HOME=/apps/oracle/oracle_base/rdbms/product/11.2.0/db_home
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl


[oracle@localhost ~]$ . .bash_profile
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 30 15:05:14 2019

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

Connected to an idle instance.

SQL> exit

[oracle@localhost ~]$ cd /apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/dbs
[oracle@localhost dbs]$ ls
createdatabase.sql  init.ora  initorcl.ora


[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 30 15:15:56 2019

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

Connected to an idle instance.

SQL> startup nomount pfile='/apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/dbs/initorcl.ora'
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size     2254952 bytes
Variable Size   503318424 bytes
Database Buffers 1627389952 bytes
Redo Buffers     4923392 bytes
SQL> @/apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/dbs/createdatabase.sql;

Database created.


SQL> select name,open_mode from v$database;

NAME   OPEN_MODE
--------- --------------------
ORCL   READ WRITE

SQL>




                                         The simplest way to create database is done !!