http://www.oracle-scripts.net/standard-datapump-use/
http://www.oracle-scripts.net/generate-user-ddl/
http://www.oracle-scripts.net/generate-user-ddl/
Sunday, December 7, 2014
Friday, November 7, 2014
RMAN catalog backup info
set lines 134
set pages 250
spool /home/oracle/scripts/logs/list_catalog_backup.log;
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
order by least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));
SELECT db_name, input_type, status,
to_char((start_time),'dd-mm-yyyy hh24:mi'),
to_char((end_time),'dd-mm-yyyy hh24:mi'),
output_bytes_display
FROM rc_rman_backup_subjob_details
WHERE start_time > sysdate -1 order by db_name;
spool off;
quit;
----------------------------------------------------
set echo off;
REM srdc_rman_performance.sql - collect RMAN performance information
define SRDCNAME='RMAN_PERFORMANCE'
set markup html on spool on
set TERMOUT off;
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
REM
spool &&SRDCSPOOLNAME..htm
Set heading off;
set feedback off;
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set heading on;
set pagesize 50000;
set echo on;
set feedback on;
Column session_id format 999999999 heading "SESS|ID"
Column session_serial# format 99999999 heading "SESS|SER|#"
Column event format a40
Column total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24MISS';
SELECT sid, spid, client_info
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND client_info LIKE '%id=rman%';
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%';
Select session_id, session_serial#, Event, sum(time_waited) total_waits
From v$active_session_history
Where sample_time > sysdate - 1
And program like '%rman%'
And session_state='WAITING' And time_waited > 0
Group by session_id, session_serial#, Event
Order by session_id, session_serial#, total_waits desc;
SELECT set_count, device_type, type, filename,
buffer_size, buffer_count, open_time, close_time
FROM v$backup_async_io
where type != 'AGGREGATE'
ORDER BY set_count,type, open_time, close_time;
select device_type "Device", type, filename,
open_time open,
close_time close,
elapsed_time Elapsed, effective_bytes_per_second
from v$backup_async_io
where close_time > sysdate-7
and type != 'AGGREGATE'
order by close_time desc;
SELECT set_count, device_type, type, filename,
buffer_size, buffer_count, open_time, close_time
FROM v$backup_sync_io
ORDER BY set_count,type, open_time, close_time;
select device_type "Device", type, filename,
open_time open,
close_time close,
elapsed_time Elapsed, effective_bytes_per_second
from v$backup_sync_io
where close_time > sysdate-7
and type != 'AGGREGATE'
order by close_time desc;
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
set markup html off
spool off
set pages 250
spool /home/oracle/scripts/logs/list_catalog_backup.log;
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
order by least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));
SELECT db_name, input_type, status,
to_char((start_time),'dd-mm-yyyy hh24:mi'),
to_char((end_time),'dd-mm-yyyy hh24:mi'),
output_bytes_display
FROM rc_rman_backup_subjob_details
WHERE start_time > sysdate -1 order by db_name;
spool off;
quit;
----------------------------------------------------
set echo off;
REM srdc_rman_performance.sql - collect RMAN performance information
define SRDCNAME='RMAN_PERFORMANCE'
set markup html on spool on
set TERMOUT off;
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
REM
spool &&SRDCSPOOLNAME..htm
Set heading off;
set feedback off;
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set heading on;
set pagesize 50000;
set echo on;
set feedback on;
Column session_id format 999999999 heading "SESS|ID"
Column session_serial# format 99999999 heading "SESS|SER|#"
Column event format a40
Column total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24MISS';
SELECT sid, spid, client_info
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND client_info LIKE '%id=rman%';
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%';
Select session_id, session_serial#, Event, sum(time_waited) total_waits
From v$active_session_history
Where sample_time > sysdate - 1
And program like '%rman%'
And session_state='WAITING' And time_waited > 0
Group by session_id, session_serial#, Event
Order by session_id, session_serial#, total_waits desc;
SELECT set_count, device_type, type, filename,
buffer_size, buffer_count, open_time, close_time
FROM v$backup_async_io
where type != 'AGGREGATE'
ORDER BY set_count,type, open_time, close_time;
select device_type "Device", type, filename,
open_time open,
close_time close,
elapsed_time Elapsed, effective_bytes_per_second
from v$backup_async_io
where close_time > sysdate-7
and type != 'AGGREGATE'
order by close_time desc;
SELECT set_count, device_type, type, filename,
buffer_size, buffer_count, open_time, close_time
FROM v$backup_sync_io
ORDER BY set_count,type, open_time, close_time;
select device_type "Device", type, filename,
open_time open,
close_time close,
elapsed_time Elapsed, effective_bytes_per_second
from v$backup_sync_io
where close_time > sysdate-7
and type != 'AGGREGATE'
order by close_time desc;
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
set markup html off
spool off
Saturday, November 1, 2014
Database size and after refresh- check below
Database size
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
This script will display a list of Oracle invalid objects:
break on c1 skip 2
set pages 999
col c1 heading 'owner' format a15
col c2 heading 'name' format a40
col c3 heading 'type' format a10
ttitle 'Invalid|Objects'
select
owner c1,
object_type c3,
object_name c2
from
dba_objects
where
status != 'VALID'
order by
owner,
object_type;
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
*/
select
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name
as invalid_objects
from ALL_objects
where status = ‘INVALID’ and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/
————
recompile.sql
/*
Script to generate the SQL commands needed to recompile invalid Oracle objects.
By Rodger Lepinsky
*/
select ‘alter ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name || ‘ compile ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘BODY’,”) ||';’
AS RECOMPILE
from ALL_objects
where status = ‘INVALID’
and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/
Database size
COLUMN "Total Gb" FORMAT 999,999,999.0
COLUMN "Redo Gb" FORMAT 999,999,999.0
COLUMN "Temp Gb" FORMAT 999,999,999.0
COLUMN "Data Gb" FORMAT 999,999,999.0
Prompt
Prompt "Database Size"
select (select sum(bytes/1073741824) from dba_data_files) "Data Gb",
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) "Temp Gb",
(select sum(bytes/1073741824)*max(members) from v$log) "Redo Gb",
(select sum(bytes/1073741824) from dba_data_files) +
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) +
(select sum(bytes/1073741824)*max(members) from v$log) "Total Gb"
from dual;
-- *****************************************************************
drop table SYSTEM.SEM_DASD_GROWTH_temp;
--truncate table SEM_CLONING_DATAFILES;
-- *****************************************************************
set echo off
set heading off
set pagesize 0
set LINESIZE 120
set feedback off
set verify off
-- *****************************************************************
create table SYSTEM.sem_dasd_growth_temp
(TABLESPACE VARCHAR2(44),
FILE_NAME VARCHAR2(64),
CURRENT_SIZE NUMBER(15),
FREE_SPACE NUMBER(15),
LARGEST_FREE NUMBER(15));
INSERT INTO SYSTEM.SEM_DASD_GROWTH_temp
SELECT d.tablespace_name
,d.file_name
,d.current_size
,round(nvl(f.free_space,0),2)
,round(nvl(f.largest_free,0),2)
FROM
(select tablespace_name
, file_id
, sum(f.bytes/(1024*1024)) free_space
, max(f.bytes/(1024*1024)) largest_free
from sys.dba_free_space f
group by tablespace_name, file_id) f
,
(select tablespace_name
, file_id
, file_name
, blocks*(select value from v$parameter
where name = 'db_block_size')/(1024*1024) current_size
, maxblocks*(select value from v$parameter
where name = 'db_block_size')/(1024*1024) maxblocks
, increment_by*(select value from v$parameter
where name = 'db_block_size')/(1024*1024) increment_by
, autoextensible
from sys.dba_data_files
group by tablespace_name, file_id, file_name, blocks, maxblocks,
increment_by, autoextensible) d
WHERE d.FILE_ID = f.FILE_ID(+);
commit;
-- *****************************************************************
/*
drop table SYSTEM.SEM_DASD_GROWTH;
--truncate table SYSTEM.RR_SEM_GROWTH;
--truncate table SEM_CLONING_DATAFILES;
-- *****************************************************************
-- column "Ddname" format a20
-- column "TABLESPACE" format a40
-- *****************************************************************
create table SYSTEM.RR_SEM_GROWTH
(TABLESPACE VARCHAR2(44),
FILE_NAME VARCHAR2(64),
CURRENT_SIZE NUMBER(15),
FREE_SPACE NUMBER(15),
LARGEST_FREE NUMBER(15),
DDNAME VARCHAR2(40),
run_date timestamp(7));
*/
-- *****************************************************************
insert all
into system.RR_SEM_GROWTH values(tablespace,substr(file_name,8,64),
current_size,free_space,largest_free,substr(file_name,20,40),sysdate)
select *
from SYSTEM.RR_SEM_GROWTH_temp;
-- order by tablespace;
commit;
exit
-- *****************************************************************
/*
select file_name from system.RR_SEM_GROWTH;
select * from SYSTEM.RR_SEM_GROWTH_temp;
select * from system.RR_SEM_GROWTH;
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
This script will display a list of Oracle invalid objects:
break on c1 skip 2
set pages 999
col c1 heading 'owner' format a15
col c2 heading 'name' format a40
col c3 heading 'type' format a10
ttitle 'Invalid|Objects'
select
owner c1,
object_type c3,
object_name c2
from
dba_objects
where
status != 'VALID'
order by
owner,
object_type;
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
*/
select
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name
as invalid_objects
from ALL_objects
where status = ‘INVALID’ and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/
————
recompile.sql
/*
Script to generate the SQL commands needed to recompile invalid Oracle objects.
By Rodger Lepinsky
*/
select ‘alter ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name || ‘ compile ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘BODY’,”) ||';’
AS RECOMPILE
from ALL_objects
where status = ‘INVALID’
and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/
Database size
COLUMN "Total Gb" FORMAT 999,999,999.0
COLUMN "Redo Gb" FORMAT 999,999,999.0
COLUMN "Temp Gb" FORMAT 999,999,999.0
COLUMN "Data Gb" FORMAT 999,999,999.0
Prompt
Prompt "Database Size"
select (select sum(bytes/1073741824) from dba_data_files) "Data Gb",
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) "Temp Gb",
(select sum(bytes/1073741824)*max(members) from v$log) "Redo Gb",
(select sum(bytes/1073741824) from dba_data_files) +
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) +
(select sum(bytes/1073741824)*max(members) from v$log) "Total Gb"
from dual;
-- *****************************************************************
drop table SYSTEM.SEM_DASD_GROWTH_temp;
--truncate table SEM_CLONING_DATAFILES;
-- *****************************************************************
set echo off
set heading off
set pagesize 0
set LINESIZE 120
set feedback off
set verify off
-- *****************************************************************
create table SYSTEM.sem_dasd_growth_temp
(TABLESPACE VARCHAR2(44),
FILE_NAME VARCHAR2(64),
CURRENT_SIZE NUMBER(15),
FREE_SPACE NUMBER(15),
LARGEST_FREE NUMBER(15));
INSERT INTO SYSTEM.SEM_DASD_GROWTH_temp
SELECT d.tablespace_name
,d.file_name
,d.current_size
,round(nvl(f.free_space,0),2)
,round(nvl(f.largest_free,0),2)
FROM
(select tablespace_name
, file_id
, sum(f.bytes/(1024*1024)) free_space
, max(f.bytes/(1024*1024)) largest_free
from sys.dba_free_space f
group by tablespace_name, file_id) f
,
(select tablespace_name
, file_id
, file_name
, blocks*(select value from v$parameter
where name = 'db_block_size')/(1024*1024) current_size
, maxblocks*(select value from v$parameter
where name = 'db_block_size')/(1024*1024) maxblocks
, increment_by*(select value from v$parameter
where name = 'db_block_size')/(1024*1024) increment_by
, autoextensible
from sys.dba_data_files
group by tablespace_name, file_id, file_name, blocks, maxblocks,
increment_by, autoextensible) d
WHERE d.FILE_ID = f.FILE_ID(+);
commit;
-- *****************************************************************
/*
drop table SYSTEM.SEM_DASD_GROWTH;
--truncate table SYSTEM.RR_SEM_GROWTH;
--truncate table SEM_CLONING_DATAFILES;
-- *****************************************************************
-- column "Ddname" format a20
-- column "TABLESPACE" format a40
-- *****************************************************************
create table SYSTEM.RR_SEM_GROWTH
(TABLESPACE VARCHAR2(44),
FILE_NAME VARCHAR2(64),
CURRENT_SIZE NUMBER(15),
FREE_SPACE NUMBER(15),
LARGEST_FREE NUMBER(15),
DDNAME VARCHAR2(40),
run_date timestamp(7));
*/
-- *****************************************************************
insert all
into system.RR_SEM_GROWTH values(tablespace,substr(file_name,8,64),
current_size,free_space,largest_free,substr(file_name,20,40),sysdate)
select *
from SYSTEM.RR_SEM_GROWTH_temp;
-- order by tablespace;
commit;
exit
-- *****************************************************************
/*
select file_name from system.RR_SEM_GROWTH;
select * from SYSTEM.RR_SEM_GROWTH_temp;
select * from system.RR_SEM_GROWTH;
Sunday, October 5, 2014
RMAN duplicate in oracle 11g
Ref: http://oracleinaction.com/duplicate-db-no-db-conn/
duplicate a database from its backups without any
connection to the source database. This method can be used if source database is not
available .
********************************
source database orcl
Duplicate database orclt
********************************
Overview:
on the source host
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
- Copy these backup files to the server where you want to create the duplicate copy.
- CREATE PFILE FROM SOURCE DATABASE
on the target host
- Add a line in the file /etc/oratab to reflect the database instance you are going to copy
- create folders
- Copy the backup files from the source database
- Copy the initialization parameter file from the source database add edit it.
- Copy the password file
- Startup the target database in nomount mode using modified parameter file
- Using RMAN connect to the duplicate database (orclt) as auxiliary instance
- Specify the location of the backups and duplicate the database orcl to orclt
Implementation
————–
*******************
on the source host
*******************
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
—-
—–
oracle@source$mkdir/home/oracle/stage
oracle@source$. oraenv orcl
rman target /
RMAN>backup database format ‘/home/oracle/stage/%U.bak’;
backup archivelog all format ‘/home/oracle/stage/arch_%r%_s_%t.bak’
;
backup current controlfile format ‘/home/oracle/stage/control.bak’;
– CREATE PFILE FROM SOURCE DATABASE
SQL>CREATE PFILE=’/home/oracle/stage/initsource.ora’
FROM SPFILE;
*****************
on the target host.
*****************
– Make a staging folder for backups and pfile
oracle@dest$mkdir -p /home/oracle/stage
– create other required folders
$mkdir -p /u01/app/oracle/oradata/orclt
mkdir -p /u01/app/oracle/flash_recovery_area/orclt
mkdir -p /u01/app/oracle/admin/orclt/adump
mkdir -p /u01/app/oracle/admin/orclt/dpdump
– Copy backup files from the source host
# scp source:/home/oracle/stage/*.bak /home/oracle/stage/
– Copy pfile of source database (orcl)
# scp source:/home/oracle/stage/initsource.ora /home/oracle/stage/inittarget.ora
– Copy the password file as well
$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt
– Add a line in the file /etc/oratab to reflect the database instance you are going to copy:
orclt:/u01/app/oracle/product/11.2.0/db1:N
– Edit the initialization parameter file from the main database.
$vi /home/oracle/stage/inittarget.ora
– Change db_name = orclt
– Edit it to reflect the new locations that might be appropriate
such as control file locations,audit dump destinations, datafile
locations, etc.
– add these lines –
db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
“/u01/app/oracle/oradata/orclt”)
log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
“/u01/app/oracle/oradata/orclt”)
In case sorce and destination databases ae ASM, following lines can be added :
db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)
log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)
– Now set the Oracle SID as the duplicated database SID:
$ . oraenv
ORACLE_SID = [orclt] ?
– Startup the target database in nomount mode using modified parameter file
$sqlplus sys/oracle as sysdba
SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
- Using RMAN connect to the duplicate database (orclt) as auxiliary instance
$. oraenv
orclt
$rman auxiliary /
– duplicate the database orcl to orclt
– the command performs the following steps:
* Creates an SPFILE
* Shuts down the instance and restarts it with the new spfile
* Restores the controlfile from the backup
* Mounts the database
* Performs restore of the datafiles. In this stage it creates the files in the
converted names.
* Recovers the datafiles up to the time specified and opens the database
– If duplicate database has the same directory structure as source (on a different host)
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ nofilenamecheck;
OR
– If duplicate database has different directory structure from source
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ ;
– check that duplicate database is up
$sqlplus / as sysdba
sql>conn hr/hr
select * from tab;
– Note that DBID is different from the main database so it can be backed up
independently and using the same catalog as well.
SQL> select dbid from v$database;
conn sys/oracle@orcl as sysdba
select dbid from v$database;
duplicate a database from its backups without any
connection to the source database. This method can be used if source database is not
available .
********************************
source database orcl
Duplicate database orclt
********************************
Overview:
on the source host
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
- Copy these backup files to the server where you want to create the duplicate copy.
- CREATE PFILE FROM SOURCE DATABASE
on the target host
- Add a line in the file /etc/oratab to reflect the database instance you are going to copy
- create folders
- Copy the backup files from the source database
- Copy the initialization parameter file from the source database add edit it.
- Copy the password file
- Startup the target database in nomount mode using modified parameter file
- Using RMAN connect to the duplicate database (orclt) as auxiliary instance
- Specify the location of the backups and duplicate the database orcl to orclt
Implementation
————–
*******************
on the source host
*******************
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
—-
—–
oracle@source$mkdir/home/oracle/stage
oracle@source$. oraenv orcl
rman target /
RMAN>backup database format ‘/home/oracle/stage/%U.bak’;
backup archivelog all format ‘/home/oracle/stage/arch_%r%_s_%t.bak’
;
backup current controlfile format ‘/home/oracle/stage/control.bak’;
– CREATE PFILE FROM SOURCE DATABASE
SQL>CREATE PFILE=’/home/oracle/stage/initsource.ora’
FROM SPFILE;
*****************
on the target host.
*****************
– Make a staging folder for backups and pfile
oracle@dest$mkdir -p /home/oracle/stage
– create other required folders
$mkdir -p /u01/app/oracle/oradata/orclt
mkdir -p /u01/app/oracle/flash_recovery_area/orclt
mkdir -p /u01/app/oracle/admin/orclt/adump
mkdir -p /u01/app/oracle/admin/orclt/dpdump
– Copy backup files from the source host
# scp source:/home/oracle/stage/*.bak /home/oracle/stage/
– Copy pfile of source database (orcl)
# scp source:/home/oracle/stage/initsource.ora /home/oracle/stage/inittarget.ora
– Copy the password file as well
$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt
– Add a line in the file /etc/oratab to reflect the database instance you are going to copy:
orclt:/u01/app/oracle/product/11.2.0/db1:N
– Edit the initialization parameter file from the main database.
$vi /home/oracle/stage/inittarget.ora
– Change db_name = orclt
– Edit it to reflect the new locations that might be appropriate
such as control file locations,audit dump destinations, datafile
locations, etc.
– add these lines –
db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
“/u01/app/oracle/oradata/orclt”)
log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
“/u01/app/oracle/oradata/orclt”)
In case sorce and destination databases ae ASM, following lines can be added :
db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)
log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)
– Now set the Oracle SID as the duplicated database SID:
$ . oraenv
ORACLE_SID = [orclt] ?
– Startup the target database in nomount mode using modified parameter file
$sqlplus sys/oracle as sysdba
SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
- Using RMAN connect to the duplicate database (orclt) as auxiliary instance
$. oraenv
orclt
$rman auxiliary /
– duplicate the database orcl to orclt
– the command performs the following steps:
* Creates an SPFILE
* Shuts down the instance and restarts it with the new spfile
* Restores the controlfile from the backup
* Mounts the database
* Performs restore of the datafiles. In this stage it creates the files in the
converted names.
* Recovers the datafiles up to the time specified and opens the database
– If duplicate database has the same directory structure as source (on a different host)
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ nofilenamecheck;
OR
– If duplicate database has different directory structure from source
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ ;
– check that duplicate database is up
$sqlplus / as sysdba
sql>conn hr/hr
select * from tab;
– Note that DBID is different from the main database so it can be backed up
independently and using the same catalog as well.
SQL> select dbid from v$database;
conn sys/oracle@orcl as sysdba
select dbid from v$database;
Dataguard status sync -
Ref: http://select-star-from.blogspot.com.au/search/label/DATAGUARD
Method :1
=========
PRIMARY
========
sqlplus "/ as sysdba"
set pages 50000 lines 32767col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------
Switch log minimun 3 times
--------------------------
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------
==================================================================================================================
Method :2
=========
STANDBY
========
sqlplus "/ as sysdba"
set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT
col time format a40
select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
==================================================================================================================
Method :3
=========
PRIMARY
========
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
Switch log minimun 3 times
--------------------------
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
0 OR min
==================================================================================================================
Method :4
=========
PRIMARY
========
Switch log minimun 3 times
--------------------------
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;
STANDBY
========
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;
==================================================================================================================
Method :5
=========
STANDBY
========
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0 N/A 1000 APPLIED_LOG
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log; ------ the status of redo log files
LGWR RFS 1000 CURRENT
Both the above current values must be same.
Method :1
=========
PRIMARY
========
sqlplus "/ as sysdba"
set pages 50000 lines 32767col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------
Switch log minimun 3 times
--------------------------
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------
==================================================================================================================
Method :2
=========
STANDBY
========
sqlplus "/ as sysdba"
set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT
col time format a40
select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
==================================================================================================================
Method :3
=========
PRIMARY
========
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
Switch log minimun 3 times
--------------------------
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
0 OR min
==================================================================================================================
Method :4
=========
PRIMARY
========
Switch log minimun 3 times
--------------------------
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;
STANDBY
========
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;
==================================================================================================================
Method :5
=========
STANDBY
========
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0 N/A 1000 APPLIED_LOG
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log; ------ the status of redo log files
LGWR RFS 1000 CURRENT
Both the above current values must be same.
Display the Data Guard related messages in the alert log file.
ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
SELECT FACILITY, ERROR_CODE, TIMESTAMP, MESSAGE
FROM V$DATAGUARD_STATUS
WHERE TRUNC(TIMESTAMP)= TRUNC(SYSTIMESTAMP)
ORDER BY TIMESTAMP;
Check if there is any redo log gap. This should be run from the standby database.
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
FROM V$ARCHIVE_GAP;
The following script is another way to check if there is any redo log gap, from the primary database:
SELECT MAX(SEQUENCE#), THREAD#
FROM V$ARCHIVED_LOG
WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)
GROUP BY THREAD#;
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'
In the standby database, examine the transport and apply lag statistics. Run the following script:
ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
SELECT NAME, VALUE, UNIT, TIME_COMPUTED
FROM V$DATAGUARD_STATS
WHERE NAME IN ('transport lag','apply lag','apply finish time');
In the standby database, list the processes that are running as part of the Data Guard configuration
SELECT PID, PROCESS, STATUS, CLIENT_PROCESS, CLIENT_PID, THREAD#, SEQUENCE# SEQ#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY
ORDER BY PROCESS;
Subscribe to:
Posts (Atom)
About Me
- RAJ
- Sydney, NSW, Australia
- An experienced IT professional (14+ years) worked in multiple industries such as consulting, education, financial, retail sectors. Carries good work experience in relational database design, analysis, modeling, development, administration, implementation, trouble shooting, support etc. Experienced in Oracle/SQL Server/MySQL DBA involving setup, configuration, tuning, backups, disaster recovery, high availability Oracle 11g/12C/19C RAC clusters; SQL server 2008/2012/2016 clusters, Oracle Engineered Systems such as EXADATA, ODA and Oracle and Azure Cloud. Performed Software Installations, Migrations, Database Capacity Planning, Automation of backup implementation, Cloud migration Tuning Oracle in windows/Unix platforms. In addition, experienced in UNIX administration, Shell scripting. PowerShell scripting A team player with communication skills.