Tuesday, July 24, 2018

Kill session ion Oracle

Kill session in Oracle

This article describes in short how to kill a session in Oracle Database
Generally there are two methods to kill a session in Oracle Database:
  • do it directly in the database
  • do it on OS level – kill dedicated server process for the database

Before you kill a session you need to scan following views to find sid, serial#, inst_id for the session
  • V$SESSION – used for non RAC databases to find sid, serial#
  • GV$SESSION – used for RAC databases to find sid, serial#, inst_id
where
  • SID – session identifier
  • SERIAL# – Session serial number. Used to uniquely identify a session’s objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID
  • INST_ID – instance number. Required only for RAC.
--non RAC databases
SELECT s.sid, s.serial#
FROM v$session s
WHERE username = 'SCOTT';

--RAC databases
SELECT s.inst_id, s.sid, s.serial#
FROM gv$session s
WHERE username = 'SCOTT';
To kill a session outside database scan following views to find dedicated server process identifier spid on OS level
  • V$PROCESS – non RAC databases to find spid
  • GV$PROCESS – RAC databases to find spid
--non RAC databases
SELECT s.sid, s.serial#, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND username = 'SCOTT';

--RAC databases
SELECT s.inst_id, s.sid, s.serial#, p.spid
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr
  AND s.inst_id = p.inst_id
  AND username = 'SCOTT';
Kill session from database level
Following methods use sid, serial#, inst_id from view [G]V$SESSION.
1. First method is to use ALTER SYSTEM KILL SESSION
ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INSTANCE_ID' [IMMEDIATE]
Above command marks a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. If this activity is going on it can hang for a minute. To avoid this hang use IMMEDIATE.
Marked session will be killed by Oracle as soon as possible however sometimes it requires to kill dedicated process manually.
--non rac kill example
ALTER SYSTEM KILL SESSION '123,34216';
ALTER SYSTEM KILL SESSION '123,34216' IMMEDIATE;

--rac kill example
ALTER SYSTEM KILL SESSION '123,34216,@2';
ALTER SYSTEM KILL SESSION '123,34216,@1' IMMEDIATE;
2. Second method is to use ALTER SYSTEM DISCONNECT SESSION
ALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' 
POST_TRANSACTION | IMMEDIATE;
This is more effective than ALTER SYSTEM KILL SESSION because it disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever). It must be run on instance where you want to kill Oracle session.
  • POST_TRANSACTION – waits for current transaction to complete, then kill dedicated process
  • IMMEDIATE – kills immediately dedicated process
ALTER SYSTEM DISCONNECT SESSION '123,34213' POST_TRANSACTION;
ALTER SYSTEM DISCONNECT SESSION '123,34213' IMMEDIATE;
Kill session from os level
Other option to kill session is to kill dedicated process on OS. It will give the same very quick effect as ALTER SYSTEM DISCONNECT SESSION. SPID can be found in [G]V$PROCESS view.
--windows
c:\orakill ORACLE_SID spid

--unix
kill -9 spid
It’s worth on Unix to check if the process really exists before executing kill -9
ps -ef | grep spid

at No comments:

Monday, July 23, 2018

Fixing Oracle broken jobs

Fixing Oracle broken jobs


Scheduled jobs appears in two places in Oracle 10g, in the dba_scheduler_jobs (for automatic jobs and jobs scheduled via dbms_scheduler) as well as in the dba_jobs views for jobs that were scheduled with the dbms_job package.
The O'Reilly book on PL/SQL Built-in Packages: notes an automatic procedure for "fixing" special types of broken jobs:

Fixing Broken Jobs Automatically

Charles Dye recommended the next example, probably based on his experiences with replication. When jobs have relatively complex execution requirements in terms of the database objects on which they depend, they can easily become broken by incurring multiple execution failures. Perhaps the DBA has modified some database links or recreated tables or views, and the job's definition has been temporarily compromised.

Well, it's a pain to manually reset the broken flag for these "not really broken" jobs, so why not have a job that regularly tries to unbreak jobs? Sounds good to me; here is a procedure called job_fixer to do just that:
/* Filename on companion disk: job5.sql */*
CREATE OR REPLACE PROCEDURE job_fixer
AS
   /*
   || calls DBMS_JOB.BROKEN to try and set
   || any broken jobs to unbroken
   */
   
   /* cursor selects user's broken jobs */
   CURSOR broken_jobs_cur
   IS
   SELECT job
     FROM user_jobs
    WHERE broken = 'Y';
    
BEGIN
   FOR job_rec IN broken_jobs_cur
   LOOP
      DBMS_JOB.BROKEN(job_rec.job,FALSE);
   END LOOP;
END job_fixer;

The Oracle documentation suggests that fixing "disabled" jobs in 10g dbms_scheduler is different from the "broken" in dbms_job (emphasis added) and adds many "state" column values for jobs in dba_scheduler_jobs: select state from dba_scheduler_jobs;
Job State Description
disabledThe job is disabled.
scheduledThe job is scheduled to be executed.
runningThe job is currently running.
completedThe job has completed, and is not scheduled to run again.
brokenThe job is broken.
failedThe job was scheduled to run once and failed.
retry scheduledThe job has failed at least once and a retry has been scheduled to be executed.
succeededThe job was scheduled to run once and completed successfully.


Enabling Jobs

You enable jobs by using the ENABLE procedure. The effect of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by default, so you need to enable them before they can run. When a job is enabled, a validity check is performed. If the check fails, the job is not enabled.
You can enable several jobs in one call by providing a comma-delimited list of job names or job class names to the ENABLE procedure call. For example, the following statement combines jobs with job classes:
BEGIN
DBMS_SCHEDULER.ENABLE ('job1, job2, job3, 
   sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
/
Also, note this on dbms_scheduler. 
Finally, Dr. Hall notes about the "state" column of dba_scheduler_jobs:

A regular failure doesn't alter the state column. We can prove this by creating a procedure that fails and scheduling it: CREATE OR REPLACE PROCEDURE proc_fail AS
BEGIN
  RAISE_APPLICATION_ERROR(-20000, 'I forced this error!');
END;
/

BEGIN
  DBMS_SCHEDULER.create_job(
    job_name        => 'test1',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN proc_fail; END;',
    repeat_interval => 'freq=minutely;',
    enabled         => TRUE,
    start_date      => SYSTIMESTAMP);
END;
/

If we let it run a few times we can see that the state column stays as
SCHEDULED, even after failures. You can check this using the following
query: SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name = 'TEST1';

As you say, you can check the details of the failures by querying the
DBA_SCHEDULER_JOB_RUN_DETAILS view.

The possible values for the STATE column vary a little, depending on the database version, but here is the state list for 10gR2.
Here is a query shows which dbms_scheduler jobs failed and why they failed:

-- What scheduled tasks failed during execution, and why?
COL log_id              FORMAT 9999   HEADING 'Log#'
COL log_date            FORMAT A32    HEADING 'Log Date'
COL owner               FORMAT A06    HEADING 'Owner'
COL job_name            FORMAT A20    HEADING 'Job'
COL status              FORMAT A10    HEADING 'Status'
COL actual_start_date   FORMAT A32    HEADING 'Actual|Start|Date'
COL error#              FORMAT 999999 HEADING 'Error|Nbr'

TTITLE 'Scheduled Tasks That Failed:'

select
   log_date,
   job_name,
   status,
   req_start_date,
   actual_start_date,
   run_duration
from
   dba_scheduler_job_run_details
where
--   job_name = 'MYJOB'
   status <> 'SUCCEEDED'
order by
   actual_start_date;

at No comments:

Thursday, July 5, 2018

Step by step guide on how to create a physical standby database using RMAN without using duplicate command

Step by step guide on how to create a physical standby database using RMAN without using duplicate command


Below are the steps to accomplish the task :
Step 1: Backup the database that includes backup of datafiles, archivelogs and controlfile for standby
Step 2: Move the backups to the standby server
Step 3: Make proper changes in the parameter files of both primary and standby database
Step 4: Do the restore and recover on standby database
Step 5: Put the standby database in recover managed mode
Example :
Step 1: Backup the primary database that includes backup of datafiles, archivelogs and controlfile for standby
[oracle@test-br ~]$ rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Dec 9 14:56:49 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=3959250272)
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate channel c3 type disk;
6> backup database plus archivelog;
7> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=159 devtype=DISK
allocated channel: c2
channel c2: sid=138 devtype=DISK
allocated channel: c3
channel c3: sid=137 devtype=DISK
Starting backup at 09:DEC:2011:14:57:27
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=32 recid=44 stamp=640387774
input archive log thread=1 sequence=33 recid=45 stamp=640387945
input archive log thread=1 sequence=34 recid=48 stamp=640668312
channel c1: starting piece 1 at 09:DEC:2011:14:57:32
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=35 recid=50 stamp=640882651
channel c2: starting piece 1 at 09:DEC:2011:14:57:32
channel c3: starting archive log backupset
channel c3: specifying archive log(s) in backup set
input archive log thread=1 sequence=20 recid=20 stamp=640385760
input archive log thread=1 sequence=21 recid=21 stamp=640385772
input archive log thread=1 sequence=22 recid=22 stamp=640385775
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c3: finished piece 1 at 09:DEC:2011:14:57:36
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c3: backup set complete, elapsed time: 00:00:05
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=26 recid=26 stamp=640386253
input archive log thread=1 sequence=27 recid=27 stamp=640386256
input archive log thread=1 sequence=28 recid=36 stamp=640387757
input archive log thread=1 sequence=29 recid=37 stamp=640387764
input archive log thread=1 sequence=30 recid=40 stamp=640387769
input archive log thread=1 sequence=31 recid=41 stamp=640387771
channel c2: starting piece 1 at 09:DEC:2011:14:57:37
channel c2: finished piece 1 at 09:DEC:2011:14:57:40
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c1: finished piece 1 at 09:DEC:2011:14:57:41
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:10
Finished backup at 09:DEC:2011:14:57:41
Starting backup at 09:DEC:2011:14:57:41
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/oracle/product/oradata/PROD/data/undo01.dbf
input datafile fno=00003 name=/u01/oracle/product/oradata/PROD/data/sysaux01.dbf
channel c1: starting piece 1 at 09:DEC:2011:14:57:41
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oracle/product/oradata/PROD/data/sys01.dbf
input datafile fno=00004 name=/u01/oracle/product/oradata/PROD/data/rman.dbf
channel c2: starting piece 1 at 09:DEC:2011:14:57:41
channel c3: starting full datafile backupset
channel c3: specifying datafile(s) in backupset
including current control file in backupset
channel c3: starting piece 1 at 09:DEC:2011:14:57:45
channel c3: finished piece 1 at 09:DEC:2011:14:57:52
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T145741_comment=NONE
channel c3: backup set complete, elapsed time: 00:00:11
channel c3: starting full datafile backupset
channel c3: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c3: starting piece 1 at 09:DEC:2011:14:57:53
channel c3: finished piece 1 at 09:DEC:2011:14:57:56
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnsnf_TAG20111209T145741_comment=NONE
channel c3: backup set complete, elapsed time: 00:00:04
channel c1: finished piece 1 at 09:DEC:2011:14:58:31
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:50
channel c2: finished piece 1 at 09:DEC:2011:14:58:31
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_comment=NONE
channel c2: backup set complete, elapsed time: 00:00:50
Finished backup at 09:DEC:2011:14:58:31
Starting backup at 09:DEC:2011:14:58:31
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=36 recid=51 stamp=640882711
channel c1: starting piece 1 at 09:DEC:2011:14:58:32
channel c1: finished piece 1 at 09:DEC:2011:14:58:33
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 09:DEC:2011:14:58:33
released channel: c1
released channel: c2
released channel: c3
Backup controlfile for standby 
RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup current controlfile for standby;
5> }
allocated channel: c1
channel c1: sid=159 devtype=DISK
Starting backup at 09:DEC:2011:15:00:09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including standby control file in backupset
channel c1: starting piece 1 at 09:DEC:2011:15:00:10
channel c1: finished piece 1 at 09:DEC:2011:15:00:11
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T150009_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 09:DEC:2011:15:00:11
released channel: c1

Step 2: Move the backups to the standby server

+ FTP or SCP the backup pieces.
+ If the backups are on NFS mount then mount the NFS on standby server with the same name as you mounted on primary database.
+ Until 9i, you need to move the backup-pieces in exactly the same location on standby as they were created on primary.
+ From 10g onwards, use CATALOG BACKUPPIECE command if you are moving backup-pieces to a different location.
+ If the backups are on tape then make sure that you make proper changes on standby server so that you can restore the backups on standby server.


Step 3: Make proper changes in the parameter files of both primary and standby database



Add the below parameter in primary database parameter file :
log_archive_dest_2='SERVICE=STANDBY'

Add the below parameters in standby database parameter file :


Copy the primary database parameter file and make necessary changes :
db_unique_name='standby'
instance_name='standby'
db_file_name_convert='/u01/oracle/product/oradata/PROD/data/','/u01/oracle/product/PROD/oradata/standby/data/','/log_file_name_convert='/u01/oracle/product/oradata/PROD/log/','/u01/oracle/product/PROD/oradata/standby/log'
standby_archive_dest='/u01/oracle/product/PROD/oradata/standby/arch1'


Step 4: Do the restore and recover on standby database


Note : After the restore try to identify the last archivelog sequence that is backed up and add 1 to it and do the recovery using it. In the below example the last archivelog that is backed up is sequence 36



[oracle@test-br ~]$ export ORACLE_SID=standby


[oracle@test-br ~]$ rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Dec 9 15:18:28 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 603979776 bytes
Fixed Size 1262224 bytes
Variable Size 163581296 bytes
Database Buffers 432013312 bytes
Redo Buffers 7122944 bytes
RMAN> set dbid=3959250272;  -- From primary database 
executing command: SET DBID
For 9i to restore controlfile you need to use the below command :
RMAN> restore controlfile from '/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T150009_3oqff2fb_.bkp';
From 10g onwards you need to use the below command to restore controlfile :
RMAN> restore standby controlfile from '/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T150009_3oqff2fb_.bkp';
Starting restore at 09:DEC:2011:15:19:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oracle/product/PROD/oradata/standby/control01.ctl
output filename=/u01/oracle/product/PROD/oradata/standby/control02.ctl
output filename=/u01/oracle/product/PROD/oradata/standby/control03.ctl
Finished restore at 09:DEC:2011:15:19:53
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1

Restore Database


RMAN> restore database;



Starting restore at 09:DEC:2011:15:20:18
 Starting implicit crosscheck backup at 09:DEC:2011:15:20:18
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=156 devtype=DISK
 Crosschecked 14 objects
 Finished implicit crosscheck backup at 09:DEC:2011:15:20:20
 Starting implicit crosscheck copy at 09:DEC:2011:15:20:20
 using channel ORA_DISK_1
 Crosschecked 1 objects
 Finished implicit crosscheck copy at 09:DEC:2011:15:20:20
 searching for all files in the recovery area
 cataloging files…
 no files cataloged
 using channel ORA_DISK_1
 channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00002 to /u01/oracle/product/PROD/oradata/standby/data/undo01.dbf
 restoring datafile 00003 to /u01/oracle/product/PROD/oradata/standby/data/sysaux01.dbf
 channel ORA_DISK_1: reading from backup piece /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fk4_.bkp
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fk4_.bkp tag=TAG20111209T145741
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
 channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00001 to /u01/oracle/product/PROD/oradata/standby/data/sys01.dbf
 restoring datafile 00004 to /u01/oracle/product/PROD/oradata/standby/data/rman.dbf
 channel ORA_DISK_1: reading from backup piece /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fod_.bkp
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fod_.bkp tag=TAG20111209T145741
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
 Finished restore at 09:DEC:2011:15:21:21
 The below command will list all the archivelogs which are backed up and from this list we need to identify the maximum sequence for recovery.


RMAN> list backup of archivelog all;



List of Backup Sets
 ===================
 BS Key Size Device Type Elapsed Time Completion Time
 ——- ———- ———– ———— ——————–
 4 65.50K DISK 00:00:01 03:DEC:2011:20:10:18
 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20111203T201017
 Piece Name: /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_03/o1_mf_annnn_TAG20111203T201017_3o85blm6_.bkp
 List of Archived Logs in backup set 4
 Thrd Seq Low SCN Low Time Next SCN Next Time
 —- ——- ———- ——————– ———- ———
 1 9 272579 03:DEC:2011:20:08:38 272631 03:DEC:2011:20:10:16
 BS Key Size Device Type Elapsed Time Completion Time
 ——- ———- ———– ———— ——————–
 5 688.80M DISK 00:00:48 03:DEC:2011:20:12:22
 BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20111203T201133
 Piece Name: /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_03/o1_mf_annnn_TAG20111203T201133_3o85f0hl_.bkp
 List of Archived Logs in backup set 5
 Thrd Seq Low SCN Low Time Next SCN Next Time
 —- ——- ———- ——————– ———- ———
 1 1 1 03:DEC:2011:18:49:10 66282 03:DEC:2011:18:54:55
 1 2 66282 03:DEC:2011:18:54:55 100553 03:DEC:2011:18:56:09
 1 3 100553 03:DEC:2011:18:56:09 131293 03:DEC:2011:18:57:52
 1 4 131293 03:DEC:2011:18:57:52 168479 03:DEC:2011:19:05:44
 1 5 168479 03:DEC:2011:19:05:44 210171 03:DEC:2011:19:06:50
 1 6 210171 03:DEC:2011:19:06:50 240733 03:DEC:2011:19:08:25
 1 7 240733 03:DEC:2011:19:08:25 266780 03:DEC:2011:19:10:40
 1 8 266780 03:DEC:2011:19:10:40 272579 03:DEC:2011:20:08:38
 1 9 272579 03:DEC:2011:20:08:38 272631 03:DEC:2011:20:10:16
 1 10 272631 03:DEC:2011:20:10:16 272683 03:DEC:2011:20:10:42
 1 11 272683 03:DEC:2011:20:10:42 272706 03:DEC:2011:20:10:54
 1 12 272706 03:DEC:2011:20:10:54 272724 03:DEC:2011:20:10:57
 1 13 272724 03:DEC:2011:20:10:57 272744 03:DEC:2011:20:11:00
 1 14 272744 03:DEC:2011:20:11:00 272763 03:DEC:2011:20:11:03
 1 15 272763 03:DEC:2011:20:11:03 272782 03:DEC:2011:20:11:06
 1 16 272782 03:DEC:2011:20:11:06 272802 03:DEC:2011:20:11:09
 1 17 272802 03:DEC:2011:20:11:09 272821 03:DEC:2011:20:11:12
 1 18 272821 03:DEC:2011:20:11:12 272840 03:DEC:2011:20:11:15
 1 19 272840 03:DEC:2011:20:11:15 272861 03:DEC:2011:20:11:33
 List of Archived Logs in backup set 10
 Thrd Seq Low SCN Low Time Next SCN Next Time
 —- ——- ———- ——————– ———- ———
 1 32 275335 03:DEC:2011:21:29:31 275338 03:DEC:2011:21:29:34
 1 33 275338 03:DEC:2011:21:29:34 275545 03:DEC:2011:21:32:25
 1 34 275545 03:DEC:2011:21:32:25 400351 07:DEC:2011:03:25:09
 BS Key Size Device Type Elapsed Time Completion Time
 ——- ———- ———– ———— ——————–
 15 2.00K DISK 00:00:01 09:DEC:2011:14:58:32
 BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20111209T145831
 Piece Name: /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_3oqfb0p6_.bkp
 List of Archived Logs in backup set 15
 Thrd Seq Low SCN Low Time Next SCN Next Time
 —- ——- ———- ——————– ———- ———
 1 36 489499 09:DEC:2011:14:57:27 489527 09:DEC:2011:14:58:31
 RMAN> recover database until sequence 37;  -- or just "recover database"
 Starting recover at 09:DEC:2011:15:22:38
 using channel ORA_DISK_1
 starting media recovery
 channel ORA_DISK_1: starting archive log restore to default destination
 channel ORA_DISK_1: restoring archive log
 archive log thread=1 sequence=36
 channel ORA_DISK_1: reading from backup piece /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_3oqfb0p6_.bkp
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_3oqfb0p6_.bkp tag=TAG20111209T145831
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
 archive log filename=/u01/oracle/product/PROD/flash_recovery_area/ABC/archivelog/2011_12_09/o1_mf_1_36_3oqgq81h_.arc thread=1 sequence=36
 channel default: deleting archive log(s)
 archive log filename=/u01/oracle/product/PROD/flash_recovery_area/ABC/archivelog/2011_12_09/o1_mf_1_36_3oqgq81h_.arc recid=1 stamp=640884160
 Oracle Error:
 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 ORA-01152: file 1 was not restored from a sufficiently old backup
 ORA-01110: data file 1: ‘/u01/oracle/product/PROD/oradata/standby/data/sys01.dbf’
 media recovery complete, elapsed time: 00:00:00
 Finished recover at 09:DEC:2011:15:22:41
 RMAN> exit
 Recovery Manager complete.



Note : No need to worry about the errors, you can safely ignore and move to step 5.

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: ‘/u01/oracle/product/PROD/oradata/standby/data/sys01.dbf’



Step 5: Put the standby database in recover managed mode



After ensuring connectivity from primary to standby using the service specified in LOG_ARCHIVE_DEST_2 parameter of primary and creating a password file on standby with the same sys password as primary, 

[oracle@test-br ~]$ sqlplus “/ as sysdba”

SQL*Plus: Release 10.2.0.2.0 – Production on Sun Dec 9 15:49:26 2011

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production

With the Partitioning, OLAP and Data Mining options


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














at





No comments:
  


































        

      









Subscribe to:
Posts (Atom)










  How to Change Instance Type & Security Group of EC2 in AWS By David Taylor Updated April 29, 2023 EC2 stands for Elastic Compute Cloud...