How to Change Archive Log Destination in Oracle 11g


sqlplus / as sysdba

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15


 To check current archiving location:

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/arch' scope=both;
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Now If you want to set log_archive_dest first reset DB_RECOVERY_FILE_DEST and then set .

SQL> alter system set DB_RECOVERY_FILE_DEST='';
System altered.

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/arch';
System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence           17





How to Hot backup & restore of an Oracle 11gR2 database

How to take Hot backup & restore in  Oracle 11gR2 database:-

Step 1:-
Prerequisites:-
$Select log_mode from v$database;
If the database is not in archivelog mode then put the database in the archive log mode
sql>shut immediate;
sql>startup mount;
sql>alter database archivelog ;
sql>alter database open;

Step 2:-
Taking a hot backup-

Now that we have prepared our database for a hot backup, we can go ahead with actually backing up the files.

Follow the following steps to take hot backup of the tablespaces

1. Find out the number of tablespaces associated with the database

sql>Select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

2. Find out if the tablespaces are ready for hot backup

sql>select * from v$backup;

If the output says not active then it is not in hot backup mode

3. Put the tablespaces in hot backup mode

sql>Alter database begin backup;

sql>Select * from v$backup;
 
     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                1128871 30-JUN-14
         2 ACTIVE                1128871 30-JUN-14
         3 ACTIVE                1128871 30-JUN-14
         4 ACTIVE                1128871 30-JUN-14
         5 ACTIVE                1128871 30-JUN-14

4. Copy the tablespace files on the hard drive to the backup location.

5. Put the tablespaces out of the backupmode

sql> Alter database end backup;

6. Verify that the tablespaces indeed have come out of the backup mode

sql>Select * from v$backup;

7. Switch the archive log

sql>Alter system archive log current;

8. Backup the control file

Note- Don’t use the operating system’s copy command to do this

sql> Alter database backup controlfile to '/u01/app/oracle/backup/controlfile.ctl';

9. Copy the archive logs to the backup location

Backup of the database finished.


Step 3:-
Restoring the oracle database from a hot backup

1. Copy the tablespace files from the backup location to the installation directory of the database instance. Also copy the controlfilebackup.

2. Rename it to CONTROL01.CTL as it was earlier.

Note- If you had another copy of the control file with the name CONTROL02.CTL, then just create a second copy of the CONTROL01.CTL and rename it CONTROL02.CTL

3. DO NOT COPY OR restore the REDO logs. If the REDO logs from the previous backup period persist then delete them

4. Start the database in mount mode

sql>startup mount;

5. Recover your database using the following
sql> Recover database until cancel using backup controlfile;

Note- The oracle system will suggest an ‘archive log file name’ to use for recovery, if you have copied the backup logs to the same location which was being used for storing the logs by the database, then u can just keep on pressing enter. Or you may give the full path to the log file.

6. When you have applied all the logs that you had used to take the backup, then write cancel on the prompt and press enter.

7. The transaction logs have been applied. Run the following query to open the database for transactions.

sql>Alter database open resetlogs;

Restore complete...

How to sync standby database which is lagging behind from primary database

Primary Database Server: server1.soumya.com
Standby Database Server: server2.soumya.com

Primary Database: prim
Standby database: stand

Database version:11.2.0.1.0


Reason:-
1. Might be due to the network outage between the primary and the standby database leading to the archive
gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as
soon as the connection is re-established.

2. It could also be due to archive logs getting missed out on the primary database or the archives getting
corrupted and there would be no valid backups.

In such cases where the standby lags far behind from the primary database, incremental backups can be used
as one of the  methods to roll forward the physical standby database to have it in sync with the primary database.

At primary database:-
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         prim             PRIMARY

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            214

At standby database:-
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         stand            PHYSICAL STANDBY

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             42
So we can see the standby database is having archive gap of around (214-42) 172 logs.

Step 1: Take a note of the Current SCN of the Physical Standby Database.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1022779

Step 2 : Cancel the Managed Recovery Process on the Standby database.
SQL> alter database recover managed standby database cancel;

Database altered.

Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (1022779)
At primary database:-

RMAN> backup incremental from scn 1022779 database format '/tmp/rman_bkp/stnd_backp_%U.bak';

Starting backup at 28-DEC-14

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-15
archived logs will not be kept or backed up
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=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-DEC-14
channel ORA_DISK_1: finished piece 1 at 28-DEC-14
piece handle=/tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak tag=TAG20141228T025048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-15
archived logs will not be kept or backed up
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
channel ORA_DISK_1: starting piece 1 at 28-DEC-14
channel ORA_DISK_1: finished piece 1 at 28-DEC-14
piece handle=/tmp/rman_bkp/stnd_backp_0dpr8v12_1_1.bak tag=TAG20141228T025048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-DEC-14

We took the backup inside /tmp/rman_bkp directory and ensure that it contains nothing besides the incremental backups of scn.

Step 4: Take the standby controlfile backup of the Primary database controlfile.

At primary database:

RMAN> backup current controlfile for standby format '/tmp/rman_bkp/stnd_%U.ctl';

Starting backup at 28-DEC-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 28-DEC-14
channel ORA_DISK_1: finished piece 1 at 28-DEC-14
piece handle=/tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl tag=TAG20141228T025301 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-DEC-14

Starting Control File and SPFILE Autobackup at 28-DEC-14
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2014_12_28/o1_mf_s_867466384_b9y8sr8k_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-DEC-14

Step 5: Transfer the backups from the Primary Server to the Standby Server.
[oracle@server1 ~]$ cd /tmp/rman_bkp/
[oracle@server1 rman_bkp]$ ls -ltrh
total 24M
-rw-r-----. 1 oracle oinstall 4.2M Dec 28 02:51 stnd_backp_0cpr8v08_1_1.bak
-rw-r-----. 1 oracle oinstall 9.7M Dec 28 02:51 stnd_backp_0dpr8v12_1_1.bak
-rw-r-----. 1 oracle oinstall 9.7M Dec 28 02:53 stnd_0epr8v4e_1_1.ctl

oracle@server1 rman_bkp]$ scp *.* oracle@server2:/tmp/rman_bkp/
oracle@server2's password:
stnd_0epr8v4e_1_1.ctl                                                                      100% 9856KB   9.6MB/s   00:00  
stnd_backp_0cpr8v08_1_1.bak                                                                100% 4296KB   4.2MB/s   00:00  
stnd_backp_0dpr8v12_1_1.bak                                                                100% 9856KB   9.6MB/s   00:00

Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied
incremental backups so that the Controlfile of the Standby Database would be aware of these
incremental backups.

At standby database:-

SQL>

[oracle@server2 ~]$ rman target /
RMAN> catalog start with '/tmp/rman_bkp';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/rman_bkp

List of Files Unknown to the Database
=====================================
File Name: /tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl
File Name: /tmp/rman_bkp/stnd_backp_0dpr8v12_1_1.bak
File Name: /tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl
File Name: /tmp/rman_bkp/stnd_backp_0dpr8v12_1_1.bak
File Name: /tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak

Step 7. Shutdown the database and open it in mount stage for recovery purpose.
SQL> shut immediate;
SQL> startup mount;


Step 8.Now recover the database :-
RMAN> rman target /
RMAN> recover database noredo;

Starting recover at 28-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/stand/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stand/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stand/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stand/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stand/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak
channel ORA_DISK_1: piece handle=/tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak tag=TAG20141228T025048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 28-DEC-14
exit.

Step 9 : Shutdown the physical standby database, start it in nomount stage and restore the standby controlfile
backup that we had taken from the primary database.

SQL> shut immediate;
SQL> startup nomount;

[oracle@server2 rman_bkp]$ rman target /
RMAN> restore standby controlfile from '/tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl';
ecovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 28 03:08:45 2014

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

connected to target database: PRIM (not mounted)

RMAN> restore standby controlfile from '/tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl';

Starting restore at 28-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/stand/stand.ctl
output file name=/u01/app/oracle/flash_recovery_area/stand/stand.ctl
Finished restore at 28-DEC-14

Step 10: Shutdown the standby database and mount the standby database, so that the standby database would
be mounted with the new controlfile that was restored in the previous step.

SQL> shut immediate;
SQL> startup mount;

At standby database:-
SQL> alter database recover managed standby database disconnect from session;

At primary database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            215

At standby database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            215

Step 11.Now we will cancel the recovery to open the database
SQL> alter database recover managed standby database cancel;

SQL> alter database open;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Now standby database is in sync with the Primary Database.


How to resize Redo Logs in Dataguard Envrionment in oracle 11g


Primary Database Server: server1.soumya.com
Standby Database Server: server2.soumya.com

Primary Database: prim
Standby database: stand

Database version:11.2.0.1.0

Both the database prim and stand are in sync. For the “Real Time Apply” implementation, we need to make
sure that we have created the Standby Redo Logs on the standby database with the size same as that of the
Online Redo logs in primary database. The Standby Redo Logs also needs to be created on the primary
database, but this is not mandatory. The Standby Redo Logs would not be used on the Primary database until
there is a switchover operation performed and the primary database starts behaving as a standby database.

At Primary Database:
[oracle@server1 ~]$ sqlplus sys/sys@stand as sysdba
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         stand            PHYSICAL STANDBY

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
    GROUP# Size in MB
---------- ----------
         1         50
         2         50
         3         50

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50

At Standby Database:
[oracle@server2 ~]$ sqlplus sys/sys@prim as sysdba
SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         prim             PRIMARY

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

    GROUP# Size in MB
---------- ----------
         1        100
         2        100
         3        100

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

    GROUP# Size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50
Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database.
If it is not set to MANUAL, then set it.

At standby database:-
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      auto

SQL> alter system set standby_file_management=manual;
System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      manual

On the primary database(prim), check the status of the Online Redo Logs and resize them by dropping
the INACTIVE redo logs and re-creating them with the new size.

At primary database:-
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT
Here you can see that Online Redo Log groups 1 and 2 are INACTIVE. Hence we can drop them and re-create
with the new size.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 100M;

Database altered.

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

GROUP# STATUS
------ -------
1      UNUSED
2      INACTIVE
3      CURRENT

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 100M;

Database altered.

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

GROUP# STATUS
------ -------
1      UNUSED
2      UNUSED
3      CURRENT

Now that Online Redo Log Groups 1 and 2 are resized and the status of Group 3 is CURRENT, switch logfiles
manually until Group 3 becomes INACTIVE.

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

GROUP# STATUS
------ -------
1      ACTIVE
2      CURRENT
3      INACTIVE

Now that Group 3 is INACTIVE, we can drop it and re-create it with the new size.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3 size 100M;
Database altered.

Now, we have resized all the Online Redo Logs on the Primary Database from 50M to 100M.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

GROUP# size in MB
------ -------------
1      100
2      100
3      100

Moving on to the Standby Redo Logs on the Primary Database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50


SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED
         7 UNASSIGNED
The status of the Standby Redo Logs (SRL) on the Primary database would be UNASSIGNED as they would be
used only when the primary database starts behaving as a Standby (Switchover)
We can easily drop the UNASSIGNED SRLs and re-create them with the new size.

At standby database:-
SQL> alter database drop standby logfile group 4;
Now while using the above query i faced an error

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

To solve this issue we have to cancel the recovery on standby database
At standy database:-
SQL> alter database recover managed standby database cancel ;
Database altered.

SQL>  alter database drop standby logfile group 4;
Database altered.
SQL>  alter database drop standby logfile group 5;
Database altered.
SQL>  alter database drop standby logfile group 6;
Database altered.
SQL>  alter database drop standby logfile group 7;
Database altered.

SQL> alter database add standby logfile group 4 size 100M;
Database altered.
SQL> alter database add standby logfile group 5 size 100M;
Database altered.
SQL> alter database add standby logfile group 6 size 100M;
Database altered.
SQL> alter database add standby logfile group 7 size 100M;
Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6        100
         4        100
         5        100
         7        100
Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database
set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.
At standby database:-
SQL> alter system set standby_file_management=auto;
System altered.

To sync both databases we will start recovery process at standby database:-
At standby database:-
SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
        29 28-DEC-14 28-DEC-14 NO
        30 28-DEC-14 28-DEC-14 NO
        31 28-DEC-14 28-DEC-14 NO
        32 28-DEC-14 28-DEC-14 NO
        33 28-DEC-14 28-DEC-14 NO
        34 28-DEC-14 28-DEC-14 NO
        35 28-DEC-14 28-DEC-14 NO
        36 28-DEC-14 28-DEC-14 NO
        37 28-DEC-14 28-DEC-14 NO

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>  select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
        29 28-DEC-14 28-DEC-14 YES
        30 28-DEC-14 28-DEC-14 YES
        31 28-DEC-14 28-DEC-14 YES
        32 28-DEC-14 28-DEC-14 YES
        33 28-DEC-14 28-DEC-14 YES
        34 28-DEC-14 28-DEC-14 YES
        35 28-DEC-14 28-DEC-14 YES
        36 28-DEC-14 28-DEC-14 YES
        37 28-DEC-14 28-DEC-14 YES
SQL> alter database recover managed standby database cancel;

Database altered.
QL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY



At primary database:-
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
37

At standby database:-
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
37
   

How to enable Block change tracking

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording
changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the
change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan
every block in the datafile.

Prior to 10.2, all incremental backups had to read every single block in the database, and if the block
has changed, it was backed up. This meant the RMAN backup job took nearly as long as a normal full backup
because every block had to be read regardless.

Change tracking is disabled by default, because it introduces some minimal performance overhead on
database during normal operations. However, the benefits of avoiding full datafile scans during backup
are considerable, especially if only a small percentage of data blocks are changed between backups.
If backup strategy involves incremental backups, then we should enable change tracking.

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing
modified block details to block change tracking file.

Checking Whether Change Tracking is enabled:-
SQL> SELECT status FROM v$block_change_tracking;

STATUS
----------
DISABLED

Enabling and Disabling Change Tracking:-
We can enable or disable change tracking when the database is either open or mounted as sysdba.

To enable:-
SQL> alter database enable block change tracking  using file '/u01/app/oracle/oradata/prim/rman_change_track.f' ;

The REUSE option tells Oracle to overwrite any existing file with the specified name.
SQL> alter database enable block change tracking  using file '/u01/app/oracle/oradata/prim/rman_change_track.f' REUSE;

To disable:-
SQL> alter database disable block change tracking  ;

Moving the Change Tracking File:-
If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file
to refer to the new location.

1.Check the file name
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

FILENAME
-------------------------------------------------------------
/u01/app/oracle/oradata/prim/rman_change_track.f

2.Shutdown the dbase
SQL> shut immediate;
exit

3.Move the file into different location
[oracle@server1 ~]$mv /u01/app/oracle/oradata/prim/rman_change_track.f /home/oracle/rman_change_track.f

4.Mount the database and move the change tracking file to a location.
SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/prim/rman_change_track.f' TO '/home/oracle/rman_change_track.f';

5.Open the database
SQL> alter database open;

6.Determine the new location of block change tracking file
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

FILENAME
--------------------------------------------------------------------------------
/home/oracle/rman_change_track.f


If you cannot shutdown the database, then you must disable change tracking and re-enable it, at the new location:
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/home/oracle/rman_change_track.f';

How to Perform Block Recovery without having RMAN backup

Oracle Version :- 11g
Os Version:-    Rhel 6.4

To demonstrate this scenario we need some corrupted datablocks on some data files.


It’s possible to perform Block Media Recovery with having only OS based “hot” backups and having NO RMAN backups.

Step 1. Create a new user and a table in that schema
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> create user soumya identified by soumya;
User created.

SQL> grant dba to soumya;
Grant succeeded.

Step 2. Now create a table inside the newly created user
SQL> conn soumya/soumya
Connected.
SQL> create table tbs_corruption (id number);
Table created.

SQL> insert into tbs_corruption values (1);
1 row created.

SQL> commit;
Commit complete.

Step 4. Confirm created user's tablespace and datafile
SQL> COLUMN segment_name FORMAT a45
SQL> SELECT segment_name, tablespace_name from dba_segments WHERE segment_name='TBS_CORRUPTION';
SEGMENT_NAME                                  TABLESPACE_NAME
--------------------------------------------- ------------------------------
TBS_CORRUPTION                                    USERS

SQL> COLUMN segment_name FORMAT a15
SQL> COLUMN tablespace_name FORMAT a15
SQL> COLUMN name FORMAT a45
SQL> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b
  2  WHERE a.header_file=b.file# AND a.segment_name=
  3  'TBS_CORRUPTION';

SEGMENT_NAME    TABLESPACE_NAME NAME
--------------- --------------- ---------------------------------------------
TBS_CORRUPTION  USERS           /u01/app/oracle/oradata/prim/users01.dbf


Step 5. Take hot backup of users01.dbf datafile.
SQL> ALTER TABLESPACE users BEGIN BACKUP;
Tablespace altered.

SQL> ! cp /u01/app/oracle/oradata/prim/users01.dbf /u01/app/oracle/oradata/prim/users01_bkp.dbf

SQL> ALTER TABLESPACE users END BACKUP;

Tablespace altered.

Step 6. Find out the header block where the below table exists.
SQL> SELECT header_block FROM dba_segments WHERE segment_name='TBS_CORRUPTION ';

HEADER_BLOCK
------------
         706

[oracle@server1 ~]$ dd of=/u01/app/oracle/oradata/prim/users01.dbf bs=8192 conv=notrunc seek=707  <<EOF
> corruption
> EOF
0+1 records in
0+1 records out
11 bytes (11 B) copied, 7.5949e-05 s, 145 kB/s

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 19:01:19 2015

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


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

SQL> conn soumya/soumya
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> SELECT * FROM TBS_CORRUPTION;
SELECT * FROM TBS_CORRUPTION
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 707)
ORA-01110: data file 4: '/u01/app/oracle/oradata/prim/users01.dbf'


SQL> exit

       
Step 7. Now we will try to recover the corrupted datablock.
[oracle@server1 ~]$ rman target sys

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jan 19 19:02:33 2015

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

target database Password:
connected to target database: PRIM (DBID=4130009889)

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 707;

Starting recover at 19-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/19/2015 19:03:08
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore

Step 8.Catalog the “hot backup”
to the RMAN repository
RMAN> CATALOG DATAFILECOPY '/u01/app/oracle/oradata/prim/users01_bkp.dbf';

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/prim/users01_BKP.DBF RECID=2 STAMP=869425424

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 707;

Starting recover at 19-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/oracle/oradata/prim/users01_BKP.DBF

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 19-JAN-15

RMAN> EXIT

Step 9.Verify the data.
[oracle@server1 ~]$ sqlplus SOUMYA/SOUMYA

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 19:04:08 2015

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


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

SQL> select * from TBS_CORRUPTION;

        ID
----------
         1

Performing Block Media Recovery using RMAN

Oracle Version :- 11g
Os Version:-    Rhel 6.4

To demonstrate this scenario we need some corrupted datablocks on some data files.

So here we will corrupt a data block manually in order to test the block recovery feature of RMAN.

Step 1:-We will create a user and will grant him dba privilge.

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL> create user soumya identified by soumya;
User created.

SQL> grant dba to soumya;
Grant succeeded.


Step 2:- Now create a table inside the newly created user
SQL> conn soumya/soumya
Connected.
SQL> create table tbs_corruption (id number);
Table created.

SQL> insert into tbs_corruption values (1);
1 row created.

SQL> commit;
Commit complete.

Step 3:-Now we will take rman backup of database
[oracle@server1 ~]$ rman target /

RMAN> backup database plus archivelog;
Starting backup at 17-JAN-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
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=6 RECID=1 STAMP=869264964
input archived log thread=1 sequence=7 RECID=2 STAMP=869265416
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_07psvs08_7_1 tag=TAG20150117T223656 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-15

Starting backup at 17-JAN-15
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=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_08psvs0a_8_1 tag=TAG20150117T223657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 17-JAN-15

Starting backup at 17-JAN-15
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=8 RECID=3 STAMP=869265483
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_09psvs2c_9_1 tag=TAG20150117T223804 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-15

Starting Control File and SPFILE Autobackup at 17-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2015_01_17/o1_mf_s_869265485_bco5qp79_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-JAN-15

Step 4:-Now we will find out the datablock number which we need to restore
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> conn soumya/soumya
Connected.
SQL>  select HEADER_BLOCK from dba_segments where segment_name='TBS_CORRUPTION';

HEADER_BLOCK
------------
         522

The header block of the segment is 522.We are going to corrupt the next data block which is 523.

Step 5:- We will check in which datafile the above data block exists.
SQL> select a.name from v$datafile a, dba_segments b
  2  where a.file#=b.header_file and b.segment_name='TBS_CORRUPTION';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf

So the table is  stored inside users01.dbf datafile.

Step 6:-Now lets corrupt the datafile
[oracle@server1 ~]$ dd of=/u01/app/oracle/oradata/prim/users01.dbf bs=8192 conv=notrunc seek=523 <<EOF
> testing corruption
> EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 5.8942e-05 s, 322 kB/s

Now we have successfully corrupted the datafile

Step 7:- Now we need to flush the database buffer cache
[oracle@server1 ~]$ sqlplus soumya/soumya
SQL> alter system flush buffer_cache;
System altered.

SQL> select * from tbs_corruption;
select * from tbs_corruption
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 523)
ORA-01110: data file 4: '/u01/app/oracle/oradata/prim/users01.dbf'

Step 8:-Now we will do block media recovery using rman
[oracle@server1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 17 23:15:40 2015

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

connected to target database: PRIM (DBID=4130009889)

RMAN> blockrecover datafile 4 block 523;

Starting recover at 17-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman_backup/PRIM_DB_0cpsvs3b_12_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/rman_backup/PRIM_DB_0cpsvs3b_12_1 tag=TAG20150117T223835
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 17-JAN-15

RMAN> exit

Now recovery has been done. we will check the table's data now
[oracle@server1 ~]$ sqlplus soumya/soumya
SQL> select * from tbs_corruption;

        ID
----------
         1

So we have successfully recovered the corrupted data block .






Noarchivelog database recovery on oracle 11g

Oracle Version:- Oracle 11g


Scenario: Database is in nonarchive log mode. And all the datafiles , redologfiles and controlfiles have been lost.Rman backup is present.
And only controlfile is present from multiplexed location.

Solution:-

SQL> select log_mode from V$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> select name from V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/somtbs.dbf

Now let's see what happens when I move all datafiles, redo log and control files to another directory
simulating a disks failure. I assume for this scenario to be able to get back with a consistent copy of at least one multiplexed control files.

[oracle@server1 prim]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ ls -ltrh
total 1.6G
drwxr-xr-x  2 oracle oinstall 4.0K Oct 20 22:15 temp
-rw-r-----  1 oracle oinstall  51M Oct 20 22:23 redo02.log
-rw-r-----  1 oracle oinstall  51M Oct 20 22:23 redo03.log
-rw-r-----  1 oracle oinstall 5.1M Oct 20 22:23 users01.dbf
-rw-r-----  1 oracle oinstall 101M Oct 20 22:23 example01.dbf
-rw-r-----  1 oracle oinstall 101M Oct 20 22:23 somtbs.dbf
-rw-r-----  1 oracle oinstall  21M Oct 20 22:23 temp01.dbf
-rw-r-----  1 oracle oinstall 681M Oct 20 22:29 system01.dbf
-rw-r-----  1 oracle oinstall  56M Oct 20 22:29 undotbs01.dbf
-rw-r-----  1 oracle oinstall 511M Oct 20 22:29 sysaux01.dbf
-rw-r-----  1 oracle oinstall  51M Oct 20 22:30 redo01.log
-rw-r-----. 1 oracle oinstall 9.3M Oct 20 22:30 control01.ctl

[oracle@server1 prim]$ mkdir temp
[oracle@server1 prim]$ mv *.* temp

Now lets try to shutdown the running database:-
SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Well it doesn't work. Of course the instance is not able to find and write any SCN on the
current control file. So issue the following command

SQL> shutdown abort; 
ORACLE instance shut down. 
Let's see what happens when an instance is not able to find a control file. The instance is not
able to be open in MOUNT mode

[oracle@localhost old]$ sqlplus / as sysdba 
SQL> startup mount; 
ORACLE instance started. 
 
Total System Global Area  456146944 bytes 
Fixed Size                  1344840 bytes 
Variable Size             352324280 bytes 
Database Buffers           96468992 bytes 
Redo Buffers                6008832 bytes 
ORA-00205: error in identifying control file, check alert log for more info 
 
SQL> shutdown immediate; 
ORA-01507: database not mounted 
 
ORACLE instance shut down. 
SQL> exit 

Since you have lost all your database files except one control file that was wisely multiplexed
on another disk (in my case it's in the flash recovery area path). We can use it to mount the
database, copying it to the original location.

[oracle@server1 prim]$ cd /u01/app/oracle/flash_recovery_area/prim
[oracle@server1 prim]$ ls -ltrh
-rw-r----- 1 oracle oinstall 9.3M Oct 20 22:40 control02.ctl
[oracle@server1 prim]$ cp control02.ctl /u01/app/oracle/oradata/prim/
[oracle@server1 prim]$ mv control02.ctl control01.ctl

Now the instance is able to open in mount mode using the CURRENT control file .
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             314574840 bytes
Database Buffers           92274688 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> select controlfile_type from v$database; 
 
CONTROL 
------- 
CURRENT 

[oracle@server1 prim]$ rman target /
RMAN> restore database;

Also check in OS level that after restore the database all datafiles are back.

Now it's time to open the database using the resetlogs option, but...

SQL> alter database open resetlogs; 
alter database open resetlogs 

ERROR at line 1: 
ORA-01139: RESETLOGS option only valid after an incomplete database recovery 

The following error happens because on the restored datafiles is written an SCN that was valid
at the time the backup was taken and equal to the control file of that moment.
The control file we are using is ahead compared with the SCN written on the datafiles...
You have to perform ("simulate") an incomplete recovery, typing CANCEL when requested

SQL> recover database until cancel;
ORA-00279: change 1214685 generated at 10/20/2014 22:05:35 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2014_10_20/o1_mf_1_34_%u_.arc
ORA-00280: change 1214685 for thread 1 is in sequence #34


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2014_10_20/o1_mf_1_34_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2014_10_20/o1_mf_1_34_%u_.a
rc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
[Here type Cancel]
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select name from V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf

Now the database has been restored to the time when it was backed up.

Expdp backup using shell script in oracle 11g

Oracle Version: oracle 11g
OS Version:- RHEL 6


[oracle@server1 ~]$ mkdir /backups/db_backup/
[oracle@server1 ~]$ chown -Rf oracle:oinstall /backups/db_backup/
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> CREATE DIRECTORY backup AS '/backups/db_backup/';
SQL> exit

# vi /home/oracle/expdp_backup.sh

#!/bin/bash
export PS1="`/bin/hostname -s`-> "
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=soumyadb
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
path1=/backups/db_backup
date1=`date +%d%m%y_%H%M%S`
/u01/app/oracle/product/11.2.0/db_1/bin/expdp system/system full=y directory=backup dumpfile=backup_$date1.dmp logfile=log_$date1.log exclude=statistics
cd /backups/db_backup/
/bin/tar -zcvf /backups/db_backup/backup_$date1.tar.gz backup_$date1.dmp

:wq



Now schedule the script inside crontab:-
#The expdp script will run everynight at 12 A.M
#crontab -e
0 0 * * * /home/oracle/expdp_backup.sh > /dev/null

Performing Disaster Recovery with RMAN

Here i'm trying to do the disaster recovery using RMAN.

Primary machine:192.168.2.102
Secondary Machine:192.168.2.104

In primary machine:-
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> select name from v$database;

NAME
---------
PRIM

Now creating a table and we will check it on the secondary machine which i would use for distaster recovery.
SQL> create table students (name varchar(20));

Table created.


SQL> insert into students values ('soumya');

1 row created.

Recovering Dropped tablespace using Flashback Database

Oracle Version :11g
OS: Rhel 6.4

#Turn on flashback and archivelog from mount stage
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL>alter database archivelog;
Database altered.

SQL>  alter database flashback on;
Database altered.

SQL> alter database open;

Database altered.

#Now create a tablespace to test the scenario
SQL> create tablespace test datafile '/u01/app/oracle/oradata/prim/testtbs.dbf' size 20m;
Tablespace created.

SQL> create user test identified by test;
User created.

SQL> grant connect , reesource to test;

SQL> alter user test default tablespace test;
User altered.

SQL> conn test/test
Connected.

SQL> create table t1 (id number);

SQL> insert into t1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t1;
        ID
----------
         1


SQL> connect sqlplus as sysdba
Enter password:
Connected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1075681

#Now drop the tablespace:-
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.

SQL> shutdown immediate
SQL> startup mount

SQL> flashback database to scn 1075681;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/prim/testtbs.dbf';
Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/testtbs.dbf

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TEST

#Login to test schema and verify the data
SQL> conn test/test
Connected.
SQL> select * from t1;

        ID
----------
         1





Cold Backup Steps and restoring it into destination server

Scenario:-Create a new oracle database on your local system(192.168.72.169) from existing database 'prim' on ip (192.168.72.172) using oracle cold backup

Source database ip :-     192.168.72.169
Destination database ip:- 192.168.72.172
Database version:-        Oracle 11g
Os version:-               Rhel 6


IN SOURCE DATABASE :

# To take a cold backup first I have to get location of pfile,datafiles,controlfiles,redolog files from
source database. As I have  got location like below:-

[oracle@server1 ~]$ sqlplus / as sysdba

SQL>select count(*) from dba_objects;

  COUNT(*)
----------
     72476

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileprim.ora

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl

SQL> select group#, member from v$logfile;
---------- ----------------------------------------------------------------------
         3 /u01/app/oracle/oradata/prim/redo03.log
         2 /u01/app/oracle/oradata/prim/redo02.log
         1 /u01/app/oracle/oradata/prim/redo01.log


IN DESTINATION DATABASE(192.168.72.172) :

# Install Oracle Binaries only. Don't Create Database on Destination Server.

# Create folder in target database same as source database and give ownership to oracle user and oinstall group.
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/oradata/prim
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/prim
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/admin/prim/adump
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@server1 ~]$ chown -Rf oracle:oinstall  /u01

IN SOURCE DATABASE :
# Shut Down the database.
SQL> shut immediate;

Copy controlfiles, datafiles, logfiles and pfile from source to destination in the same location as oracle user :-

# scp -r /u01/app/oracle/oradata/prim/* oracle@192.168.72.172:/u01/app/oracle/oradata/prim
# scp /u01/app/oracle/flash_recovery_area/prim/control02.ctl oracle@192.168.72.172:/u01/app/oracle/flash_recovery_area/prim/
# scp /u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora oracle@192.168.72.172:/u01/app/oracle/product/11.2.0/db_1/dbs/

IN DESTINATION DATABASE :

# Set Oracle SID
export ORACLE_SID=prim
# Login as sysdba
sqlplus / as sysdba
# Start the database in nomount stage using pfile.
SQL> STARTUP nomount PFILE =/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora
# Enter in mount stage :-
SQL> alter database mount;
# Open database:-
SQL> alter database open;
# Create spfile from pfile
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
# Restart the database to use default spfile.
SQL> shut immediate;
SQL> startup;

SQL>select count(*) from dba_objects;

  COUNT(*)
----------
     72476

IN SOURCE DATABASE :
# Start the source Database
SQL> startup;


Hence we can say the database has been successfully restored from source database using cold backup.

Restrict User access to database within certain limit of time


We need to create a trigger for this purpose.

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL> create user sam identified by sam;

User created.

SQL> grant connect , resource to sam;

Grant succeeded.

SQL> conn sam/sam
Connected.
SQL> exit

[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> CREATE OR REPLACE TRIGGER limit_connection
         AFTER LOGON ON DATABASE
       BEGIN
          IF USER = 'SAM' THEN
             IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 20 AND 22
             THEN
                RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 20hrs to 22hrs');
             END IF;
          END IF;
      END limit_connection;
      /

Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO
--
21

SQL> conn sam/sam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user SAM! You can't login between 20hrs to 22hrs
ORA-06512: at line 5
Warning: You are no longer connected to ORACLE.

Done...

How to Multiplex Redo Log Files in Oracle 11g



Step 1:- List all the current redo log file in database
SQL> Select * from v$logfile;

    GROUP#   TYPE    MEMBER                      
---------- ------- ------- --------------------------------------
         1   ONLINE  /u01/app/oracle/oradata/prim/redo01.log
         2   ONLINE  /u01/app/oracle/oradata/prim/redo02.log
         3   ONLINE  /u01/app/oracle/oradata/prim/redo03.log



Step-2: Add redo log file members for different location

ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/redolog/redo01.log' TO GROUP 1;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/redolog/redo02.log' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/redolog/redo03.log' TO GROUP 3;

Database altered.

SQL> alter system switch logfile;

SQL> select GROUP#,SEQUENCE#,(BYTES/1024/1024)MB,MEMBERS from v$log;

    GROUP#  SEQUENCE# (BYTES/1024/1024)MB    MEMBERS
----------          ----------           -----------------                 ----------
         1                7                         50                                  2
         2                5                         50                                  2
         3                6                         50                                  2


SQL> Select * from v$logfile;

   GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/prim/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/prim/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/prim/redo01.log            NO
         1         ONLINE  /home/oracle/redolog/redo01.log                    NO
         2         ONLINE  /home/oracle/redolog/redo02.log                    NO
         3         ONLINE  /home/oracle/redolog/redo03.log      


Done..

How to Multiplex Control Files in Oracle 11g

Step 1:-Backup your current control file with a trace option
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

Step-2: List all the controlfiles

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl


Step-3 Shutdown the database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4:-Add one more controlfile
[oracle@server1 prim]$cp /u01/app/oracle/oradata/prim/control01.ctl /home/oracle/Multiplexed_control_files/control03.ctl

Step-5: Create PFILE using SPFILE
[oracle@server1 dbs]$ sqlplus / as sysdba
SQL> create pfile from spfile;

File created.

[oracle@server1 prim]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
##Add the newly created control file.
[oracle@server1 dbs]$ vi initprim.ora
*.control_files='/u01/app/oracle/oradata/prim/control01.ctl','/u01/app/oracle/flash_recovery_area/prim/control02.ctl','/home/oracle/Multiplexed_control_files/control03.ctl'

:wq

Step 5:-Create SPFILE using PFILE
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
File created.

Step 6:- Start the database
SQL> startup
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             327157752 bytes
Database Buffers           79691776 bytes
Redo Buffers                4308992 bytes
Database mounted.
Database opened.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl
/home/oracle/Multiplexed_control_files/control03.ctl

Done...

What's View and Synonym?

What is a view?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
A view is based on a table or another view and acts as a window through which data on tables can be viewed or changed. A view does not contain data. The definition of the view is stored in the data dictionary. You can see definition of view in user_view data dictionary table.
A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables.
What is a synonym and what types it has?
A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.
Synonyms allow underlying objects to be renamed or moved, where only the synonym needs to be redefined and applications based on the synonym continue to function without modification.
You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and the user’s grantees.

Create oracle 11g database manually on linux

Make sure oracle binaries are installed.We will proceed with database installation if binaries are already installed.

Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u01

[oracle@localhost u01]$ mkdir testdb

[oracle@localhost u01]$ chmod -R 777 /u01/testdb/*

[oracle@localhost u01]$ chown -R oracle:oinstall /u01/testdb/*

[oracle@localhost u01] cd testdb

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u01]$ chmod -Rf 777 /u01/testdb/*

[oracle@localhost u01]$ chown -Rf oracle:oinstall /u01/testdb/*


Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost testdb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_testdb.ora

db_name='testdb'
memory_target=1G
processes = 150
audit_file_dest='/u01/testdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/testdb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/testdb/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/testdb/ora_control1.ctl,/u01/testdb/ora_control2.ctl)
compatible ='11.2.0'


Step 3:Prepare Create Database script :

[oracle@localhost u01]$ cd /u01/testdb/

[oracle@localhost testdb]$ vi createdb_test.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u01/testdb/redo1.log' SIZE 10M,
GROUP 2 '/u01/testdb/redo2.log' SIZE 10M,
GROUP 3 '/u01/testdb/redo3.log' SIZE 10M
DATAFILE
'/u01/testdb/system.dbf' size 200M REUSE
sysaux datafile '/u01/testdb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u01/testdb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u01/testdb/temp01.dbf'
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost testdb]# su - oracle

[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_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=testdb
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
     This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$ export ORACLE_SID=testdb
     This will set the SID of the current Database in Unix-based operating system.


Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwtestdb password=Oracle entries=10


Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

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

Connected to an idle instance.

SQL>create spfile from pfile='$ORACLE_HOME/dbs/init_testdb.ora';


step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED



Step 8:Execute Create Database script created in Step 3

SQL> @/u01/testdb/createdb_test.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql  =>Creates user profiles.


So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00942: table or view does not exist


DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.


Step 10:Verify the Dictionary views created.


SQL> select name from v$database;
NAME
---------
TESTDB

Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

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


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

This completes the manual Database creation on Unix based Operating system....

Enjoy learning...





Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.