SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;
Monday, October 23, 2023
Query to get 10 Large Object in Oracle Database
SELECT * FROM (select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB,TABLESPACE_NAME from dba_segments order by 3 desc) WHERE ROWNUM <= 10;
Saturday, October 21, 2023
Recovery file usage
col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
Meta data tablespace
set lines 190
set pages 5000
set long 50000
set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces where tablespace_name='TSTDOCMGMT01';
Wednesday, October 11, 2023
killing inactive session from Oracle database
SELECT 'ALTER SYSTEM KILL SESSION '''
|| s.sid
|| ','
|| s.serial#
|| ''' IMMEDIATE;'
AS ddl
FROM v$session s
WHERE s.status='INACTIVE';
|| s.sid
|| ','
|| s.serial#
|| ''' IMMEDIATE;'
AS ddl
FROM v$session s
WHERE s.status='INACTIVE';
Wednesday, September 27, 2023
UNDO Main
SET SERVEROUTPUT ON
-- Not wrriten by me but its a good script to check details on UNDO tablespace usage and recommendations
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
-- Not wrriten by me but its a good script to check details on UNDO tablespace usage and recommendations
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
Thursday, June 22, 2023
Oracle UNDO related document
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
Wednesday, July 12, 2017
12c Binaries Installation - Silent Installation Oracle Software 12c
I have prepared silent mode installation of 12c software , please validate
cd /local/dbatemp/software/12102/database
export DISTRIB=`pwd`
./runInstaller -silent \
-responseFile $DISTRIB/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=jbs_sudo \
INVENTORY_LOCATION=/local/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/local/app/oracle/product/12.1.0.2 \
ORACLE_BASE=/local/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=jbs_sudo \
oracle.install.db.OPER_GROUP=jbs_sudo \
oracle.install.db.BACKUPDBA_GROUP=jbs_sudo \
oracle.install.db.DGDBA_GROUP=jbs_sudo \
oracle.install.db.KMDBA_GROUP=jbs_sudo \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
This has started installation in the background: you need to wait until something similar to the following is displayed:
The installation of Oracle Database 12c was successful.
Please check '/local/app/oraInventory/logs/silentInstallXXX.log' for more details.
As a root user, execute the following script(s):
1. /local/app/oraInventory/orainstRoot.sh
2. /local/app/oracle/product/12.1.0.2/root.sh
Successfully Setup Software.
cd /local/dbatemp/software/12102/database
export DISTRIB=`pwd`
./runInstaller -silent \
-responseFile $DISTRIB/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=jbs_sudo \
INVENTORY_LOCATION=/local/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/local/app/oracle/product/12.1.0.2 \
ORACLE_BASE=/local/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=jbs_sudo \
oracle.install.db.OPER_GROUP=jbs_sudo \
oracle.install.db.BACKUPDBA_GROUP=jbs_sudo \
oracle.install.db.DGDBA_GROUP=jbs_sudo \
oracle.install.db.KMDBA_GROUP=jbs_sudo \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
This has started installation in the background: you need to wait until something similar to the following is displayed:
The installation of Oracle Database 12c was successful.
Please check '/local/app/oraInventory/logs/silentInstallXXX.log' for more details.
As a root user, execute the following script(s):
1. /local/app/oraInventory/orainstRoot.sh
2. /local/app/oracle/product/12.1.0.2/root.sh
Successfully Setup Software.
Thursday, March 16, 2017
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP
autobackup, catalog, dbid, restore controlfile, rman, RMAN-06563
Backup and Recovery best practices dictate that we must use a RMAN recovery catalog and also have the controlfile AUTOBACKUP enabled.
If we do not do either and we lose all the controlfiles, we cannot restore the controlfiles even if we have taken a backup to tape as shown in the case below.
We will encounter the RMAN-06563 error even if we set the DBID or explicitly alllocate a channel for a tape device.
set dbid=693232013;
executing command: SET DBID
RMAN> run {
2> restore controlfile;
3> recover database;
4> }
Starting restore at 28-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/28/2009 12:17:19
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP
RMAN> run {
2> allocate channel c1 device type sbt_tape;
3> restore controlfile;
4> alter database mount;
5> recover database;
6> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=159 devtype=SBT_TAPE
channel c1: Data Protection for Oracle: version 5.5.1.0
Starting restore at 28-JUL-09
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/28/2009 12:19:36
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP
However, if we connect to a RMAN recovery catalog, we can restore a controlfile without using the AUTOBACKUP keyword.
$ rman target / catalog rman9p/xxx@rcatp
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ofsap (not mounted)
connected to recovery catalog database
RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
3> restore controlfile;
4> release channel ch1;
5> }
allocated channel: ch1
channel ch1: sid=8 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 27-JUN-08
channel ch1: starting datafile backupset restore
channel ch1: restoring controlfile
output filename=/u04/oradata/ofsap/control01.ctl
channel ch1: restored backup piece 1
piece handle=c-2764499561-20080627-08 tag=null params=NULL
channel ch1: restore complete
replicating controlfile
input filename=/u04/oradata/ofsap/control01.ctl
output filename=/u04/oradata/ofsap/control02.ctl
Finished restore at 27-JUN-08
released channel: ch1
Saturday, March 4, 2017
Block Corruption in Oracle
Normally when block Corruption Happened with Oracle Database Oracle reports the corrupted block number in Error Message,
SQL> select * from update7sp7.test1;
select * from update7sp7.test1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 138 )
ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.8 19379005'
This way ..
we need to fix Corrupted block using few available methods, Best approach is RMAN where we have other Utilities as well
BBED for block Corruption recovery.
here in this case i tried to recover a block using RMAN
---------
check DB / Datafile / status using validate command , which will list you the entire block details, with
Used / Un Used and corrupted block if any
RMAN> validate database;
select * from v$database_block_corruption;
once we get the corrupted block details , we can recover it with steps given below.
[oracle@ora1-2 trace]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 418:41:21 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRPRIM (DBID=216679430)
RMAN> recover datafile 8 block 137 to 138,146;
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 418:41:21 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRPRIM (DBID=216679430)
RMAN> recover datafile 8 block 137 to 138,146;
Starting recover at 04-OCT-13using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=59 device type=DISKsearching flashback logs for block images until SCN 2501389finished flashback log search, restored 0 blockschannel ORA_DISK_1: restoring block(s) from datafile copy /u02/bkp/myts_8failover to previous backupchannel ORA_DISK_1: restoring block(s)channel ORA_DISK_1: specifying block(s) to restore from backupsetrestoring blocks of datafile 00008channel ORA_DISK_1: reading from backup piece /u02/bkp/srprim_inc0_37olibpj_1_1.bakchannel ORA_DISK_1: piece handle=/u02/bkp/srprim_inc0_37olibpj_1_1.bak tag=TAG20131004T120611channel ORA_DISK_1: restored block(s) from backup piece 1channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01starting media recoverymedia recovery complete, elapsed time: 00:00:03Finished recover at 04-OCT-13SQL> select status,instance_name,database_role from v$instance,v$database;STATUS INSTANCE_NAME DATABASE_ROLE------ ------------- --------------OPEN SRPRIM PRIMARYSQL> select * from v$database_block_corruption;no rows selectedSunday, January 22, 2017
Online migration of ASM disk groups
Online migration of ASM disk groups from one SAN to another
Below mentioned are the detailed steps from Database Admin perspective for migrating ASM disks from one SAN to another[from EMC to HP] in a RAC setup.
1. Capture the current ASM disk/diskgroups details as below. Also decide upon the new LUN and ASM disk names. Share these with OS Admins, so that they can create same size LUN's with new SAN.
To get ASM disk, group and size details use the below queries.
select group_number,disk_number,name,mount_status,state,total_mb,free_mb From v$asm_disk order by 1,2;
select GROUP_NUMBER,NAME from v$asm_diskgroup;
To get the LUN-ASM disk mapping details use the below command.
/usr/sbin/oracleasm querydisk -p /dev/mapper/*
This command needs to be run with root privilege and in all the RAC instances just to be sure that you see the same.
ASM Disk Group Existing ASM Disk Existing LUN ASM disk Size in GB New LUN New Disk Name
ASMVOTE01 OCR_VOTE1 OCR_VOTE1p1 1 N_OCR_VOTE1p1 N_OCR_VOTE1
OCR_VOTE2 OCR_VOTE2p1 1 N_OCR_VOTE2p1 N_OCR_VOTE2
OCR_VOTE3 OCR_VOTE3p1 1 N_OCR_VOTE3p1 N_OCR_VOTE3
OCR_VOTE4 OCR_VOTE4p1 1 N_OCR_VOTE4p1 N_OCR_VOTE4
OCR_VOTE5 OCR_VOTE5p1 1 N_OCR_VOTE5p1 N_OCR_VOTE5
ASMDATA01 ASMDATA01 ASM1p1 100 N_ASM1p1 N_ASMDATA01
ASMDATA02 ASM2p1 100 N_ASM2p1 N_ASMDATA02
ASMDATA03 ASM3p1 100 N_ASM3p1 N_ASMDATA03
ASMDATA04 ASM4p1 100 N_ASM4p1 N_ASMDATA04
ASMFRA01 ASMDATA05 ASM5p1 100 N_ASM5p1 N_ASMDATA05
ASMDATA06 ASM6p1 100 N_ASM6p1 N_ASMDATA06
ASMDATA07 ASM7p1 100 N_ASM7p1 N_ASMDATA07
ASMDATA08 ASM8p1 100 N_ASM8p1 N_ASMDATA08
ASMDATA09 ASM9p1 100 N_ASM9p1 N_ASMDATA09
ASMDATA10 ASM10p1 100 N_ASM10p1 N_ASMDATA10
2. Take full database backup.
3. Both old and new SAN LUNs must be presented to all the RAC nodes.
Confirm whether you see the new LUNs under /dev/mapper/ from all the cluster nodes.
4. Once the LUNs are visible we need to stamp them to use them as ASM disks.
Below commands needs to be run with root privilege. This commands should be run only once from anyone of the cluster node.
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE1 '/dev/mapper/N_OCR_VOTE1p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE2 '/dev/mapper/N_OCR_VOTE2p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE3 '/dev/mapper/N_OCR_VOTE3p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE4 '/dev/mapper/N_OCR_VOTE4p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE5 '/dev/mapper/N_OCR_VOTE5p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA1 '/dev/mapper/N_ASM1p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA2 '/dev/mapper/N_ASM2p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA3 '/dev/mapper/N_ASM3p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA4 '/dev/mapper/N_ASM4p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA5 '/dev/mapper/N_ASM5p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA6 '/dev/mapper/N_ASM6p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA7 '/dev/mapper/N_ASM7p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA8 '/dev/mapper/N_ASM8p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA9 '/dev/mapper/N_ASM9p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA10 '/dev/mapper/N_ASM10p1'
5. Now scan and list the newly added ASM disks on other nodes.
Again this needs to be run with root privilege.
sudo /usr/sbin/oracleasm scandisks
sudo /usr/sbin/oracleasm listdisks
You should be able to see both old and newly added ASM disks from all the nodes.
6. Login to any ASM instance and verify the newly added ASM disks.Make sure you login as sysasm to avoid any privilege issues while logging into ASM instances.
select group_number,disk_number,name,mount_status,state,total_mb,free_mb From v$asm_disk order by 1,2;
7. Rebalancing
a. Add the newly created ASM disks to its mapping ASM diskgroups, which was decided in step 1.
Once the ALTER DISKGROUP is run rebalancing operation will begin in background and the prompt will be returned. Rebalancing of one diskgroup is not dependent on other, so below commands can be run at once.
SQL> alter diskgroup ASMDATA01
add disk 'ORCL:N_ASMDATA1','ORCL:N_ASMDATA2','ORCL:N_ASMDATA3','ORCL:N_ASMDATA4'
rebalance power 11;
SQL> alter diskgroup ASMFRA01
add disk 'ORCL:N_ASMDATA5','ORCL:N_ASMDATA6',
'ORCL:N_ASMDATA7','ORCL:N_ASMDATA8','ORCL:N_ASMDATA9','ORCL:N_ASMDATA10'
rebalance power 11;
SQL> alter diskgroup ASMVOTE01
add disk
'ORCL:N_OCR_VOTE1','ORCL:N_OCR_VOTE2','ORCL:N_OCR_VOTE3' ,'ORCL:N_OCR_VOTE4',
'ORCL:N_OCR_VOTE5' rebalance power 11;
b. Drop the old ASM disks from its corresponding ASM diskgroups.
SQL> alter diskgroup ASMDATA01
drop disk 'ORCL:ASMDATA1','ORCL:ASMDATA2','ORCL:ASMDATA3','ORCL:ASMDATA4'
rebalance power 11;
SQL> alter diskgroup ASMFRA01
drop disk 'ORCL:ASMDATA5','ORCL:ASMDATA6',
'ORCL:ASMDATA7','ORCL:ASMDATA8','ORCL:ASMDATA9','ORCL:ASMDATA10'
rebalance power 11;
SQL> alter diskgroup ASMVOTE01
drop disk
'ORCL:OCR_VOTE1','ORCL:OCR_VOTE2',
'ORCL:OCR_VOTE3' ,'ORCL:OCR_VOTE4','ORCL:OCR_VOTE5' rebalance power 11;
Both steps a and b can be performed in a single command and oracle recommends the same to save time. But to be on the safer side its better to perform them separately.
8. Monitor Rebalancing operation
Login to any ASM instance and monitor rebalancing operation using the below queries.
select * from v$asm_operation;
select * from gv$asm_operation;
9. Verify the diskgroups for the last time.
select group_number,disk_number,mount_status,state,redundancy from v$asm_disk;
select group_number,name,state,type from v$asm_diskgroup;
select group_number,disk_number,name,mount_status,state,total_mb,free_mb from v$asm_disk order by 1,2;
This successfully completes the migration.
10. Once above steps are complete, the old SAN can be dismounted.
Below mentioned are the detailed steps from Database Admin perspective for migrating ASM disks from one SAN to another[from EMC to HP] in a RAC setup.
1. Capture the current ASM disk/diskgroups details as below. Also decide upon the new LUN and ASM disk names. Share these with OS Admins, so that they can create same size LUN's with new SAN.
To get ASM disk, group and size details use the below queries.
select group_number,disk_number,name,mount_status,state,total_mb,free_mb From v$asm_disk order by 1,2;
select GROUP_NUMBER,NAME from v$asm_diskgroup;
To get the LUN-ASM disk mapping details use the below command.
/usr/sbin/oracleasm querydisk -p /dev/mapper/*
This command needs to be run with root privilege and in all the RAC instances just to be sure that you see the same.
ASM Disk Group Existing ASM Disk Existing LUN ASM disk Size in GB New LUN New Disk Name
ASMVOTE01 OCR_VOTE1 OCR_VOTE1p1 1 N_OCR_VOTE1p1 N_OCR_VOTE1
OCR_VOTE2 OCR_VOTE2p1 1 N_OCR_VOTE2p1 N_OCR_VOTE2
OCR_VOTE3 OCR_VOTE3p1 1 N_OCR_VOTE3p1 N_OCR_VOTE3
OCR_VOTE4 OCR_VOTE4p1 1 N_OCR_VOTE4p1 N_OCR_VOTE4
OCR_VOTE5 OCR_VOTE5p1 1 N_OCR_VOTE5p1 N_OCR_VOTE5
ASMDATA01 ASMDATA01 ASM1p1 100 N_ASM1p1 N_ASMDATA01
ASMDATA02 ASM2p1 100 N_ASM2p1 N_ASMDATA02
ASMDATA03 ASM3p1 100 N_ASM3p1 N_ASMDATA03
ASMDATA04 ASM4p1 100 N_ASM4p1 N_ASMDATA04
ASMFRA01 ASMDATA05 ASM5p1 100 N_ASM5p1 N_ASMDATA05
ASMDATA06 ASM6p1 100 N_ASM6p1 N_ASMDATA06
ASMDATA07 ASM7p1 100 N_ASM7p1 N_ASMDATA07
ASMDATA08 ASM8p1 100 N_ASM8p1 N_ASMDATA08
ASMDATA09 ASM9p1 100 N_ASM9p1 N_ASMDATA09
ASMDATA10 ASM10p1 100 N_ASM10p1 N_ASMDATA10
2. Take full database backup.
3. Both old and new SAN LUNs must be presented to all the RAC nodes.
Confirm whether you see the new LUNs under /dev/mapper/ from all the cluster nodes.
4. Once the LUNs are visible we need to stamp them to use them as ASM disks.
Below commands needs to be run with root privilege. This commands should be run only once from anyone of the cluster node.
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE1 '/dev/mapper/N_OCR_VOTE1p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE2 '/dev/mapper/N_OCR_VOTE2p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE3 '/dev/mapper/N_OCR_VOTE3p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE4 '/dev/mapper/N_OCR_VOTE4p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE5 '/dev/mapper/N_OCR_VOTE5p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA1 '/dev/mapper/N_ASM1p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA2 '/dev/mapper/N_ASM2p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA3 '/dev/mapper/N_ASM3p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA4 '/dev/mapper/N_ASM4p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA5 '/dev/mapper/N_ASM5p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA6 '/dev/mapper/N_ASM6p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA7 '/dev/mapper/N_ASM7p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA8 '/dev/mapper/N_ASM8p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA9 '/dev/mapper/N_ASM9p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA10 '/dev/mapper/N_ASM10p1'
5. Now scan and list the newly added ASM disks on other nodes.
Again this needs to be run with root privilege.
sudo /usr/sbin/oracleasm scandisks
sudo /usr/sbin/oracleasm listdisks
You should be able to see both old and newly added ASM disks from all the nodes.
6. Login to any ASM instance and verify the newly added ASM disks.Make sure you login as sysasm to avoid any privilege issues while logging into ASM instances.
select group_number,disk_number,name,mount_status,state,total_mb,free_mb From v$asm_disk order by 1,2;
7. Rebalancing
a. Add the newly created ASM disks to its mapping ASM diskgroups, which was decided in step 1.
Once the ALTER DISKGROUP is run rebalancing operation will begin in background and the prompt will be returned. Rebalancing of one diskgroup is not dependent on other, so below commands can be run at once.
SQL> alter diskgroup ASMDATA01
add disk 'ORCL:N_ASMDATA1','ORCL:N_ASMDATA2','ORCL:N_ASMDATA3','ORCL:N_ASMDATA4'
rebalance power 11;
SQL> alter diskgroup ASMFRA01
add disk 'ORCL:N_ASMDATA5','ORCL:N_ASMDATA6',
'ORCL:N_ASMDATA7','ORCL:N_ASMDATA8','ORCL:N_ASMDATA9','ORCL:N_ASMDATA10'
rebalance power 11;
SQL> alter diskgroup ASMVOTE01
add disk
'ORCL:N_OCR_VOTE1','ORCL:N_OCR_VOTE2','ORCL:N_OCR_VOTE3' ,'ORCL:N_OCR_VOTE4',
'ORCL:N_OCR_VOTE5' rebalance power 11;
b. Drop the old ASM disks from its corresponding ASM diskgroups.
SQL> alter diskgroup ASMDATA01
drop disk 'ORCL:ASMDATA1','ORCL:ASMDATA2','ORCL:ASMDATA3','ORCL:ASMDATA4'
rebalance power 11;
SQL> alter diskgroup ASMFRA01
drop disk 'ORCL:ASMDATA5','ORCL:ASMDATA6',
'ORCL:ASMDATA7','ORCL:ASMDATA8','ORCL:ASMDATA9','ORCL:ASMDATA10'
rebalance power 11;
SQL> alter diskgroup ASMVOTE01
drop disk
'ORCL:OCR_VOTE1','ORCL:OCR_VOTE2',
'ORCL:OCR_VOTE3' ,'ORCL:OCR_VOTE4','ORCL:OCR_VOTE5' rebalance power 11;
Both steps a and b can be performed in a single command and oracle recommends the same to save time. But to be on the safer side its better to perform them separately.
8. Monitor Rebalancing operation
Login to any ASM instance and monitor rebalancing operation using the below queries.
select * from v$asm_operation;
select * from gv$asm_operation;
9. Verify the diskgroups for the last time.
select group_number,disk_number,mount_status,state,redundancy from v$asm_disk;
select group_number,name,state,type from v$asm_diskgroup;
select group_number,disk_number,name,mount_status,state,total_mb,free_mb from v$asm_disk order by 1,2;
This successfully completes the migration.
10. Once above steps are complete, the old SAN can be dismounted.
Monday, October 24, 2016
Tning.sql - performance analyser
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/tuning.sql
-- Author : Tim Hall
-- Description : Displays several performance indicators and comments on the value.
-- Requirements : Access to the V$ views.
-- Call Syntax : @tuning
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SELECT *
FROM v$database;
PROMPT
DECLARE
v_value NUMBER;
FUNCTION Format(p_value IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || ' ';
END;
BEGIN
-- --------------------------
-- Dictionary Cache Hit Ratio
-- --------------------------
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
INTO v_value
FROM v$rowcache;
DBMS_Output.Put('Dictionary Cache Hit Ratio : ' || Format(v_value));
IF v_value < 90 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -----------------------
-- Library Cache Hit Ratio
-- -----------------------
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
INTO v_value
FROM v$librarycache;
DBMS_Output.Put('Library Cache Hit Ratio : ' || Format(v_value));
IF v_value < 99 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -------------------------------
-- DB Block Buffer Cache Hit Ratio
-- -------------------------------
SELECT (1 - (phys.value / (db.value + cons.value))) * 100
INTO v_value
FROM v$sysstat phys,
v$sysstat db,
v$sysstat cons
WHERE phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';
DBMS_Output.Put('DB Block Buffer Cache Hit Ratio : ' || Format(v_value));
IF v_value < 89 THEN
DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ---------------
-- Latch Hit Ratio
-- ---------------
SELECT (1 - (Sum(misses) / Sum(gets))) * 100
INTO v_value
FROM v$latch;
DBMS_Output.Put('Latch Hit Ratio : ' || Format(v_value));
IF v_value < 98 THEN
DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;
-- -----------------------
-- Disk Sort Ratio
-- -----------------------
SELECT (disk.value/mem.value) * 100
INTO v_value
FROM v$sysstat disk,
v$sysstat mem
WHERE disk.name = 'sorts (disk)'
AND mem.name = 'sorts (memory)';
DBMS_Output.Put('Disk Sort Ratio : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ----------------------
-- Rollback Segment Waits
-- ----------------------
SELECT (Sum(waits) / Sum(gets)) * 100
INTO v_value
FROM v$rollstat;
DBMS_Output.Put('Rollback Segment Waits : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;
-- -------------------
-- Dispatcher Workload
-- -------------------
SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
INTO v_value
FROM v$dispatcher;
DBMS_Output.Put('Dispatcher Workload : ' || Format(v_value));
IF v_value > 50 THEN
DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;
END;
/
PROMPT
SET FEEDBACK ON
- ORDS (oracle Rest Data services)
- ORDS (oracle Rest Data services)
Oracle now support data services - running on standalone machines, means no need of weblogic , glassfish application/ tools,
installation / is simple need to define
Port and Image in
standalone.http.port=8080
standalone.static.images=/home/oracle/apex/images
Based on the installation in the linked article, you will find the standalone settings in the following file.
/home/oracle/ords-3.0.7-conf/ords/standalone/standalone.properties
-------------
you can always star the service using
cd ~/ords-3.0.7
$JAVA_HOME/bin/java -jar ords.war
ctrl+C will stop the service, for production environment , use Nohup to run the process in backgrond and you can kill the process when you want to stop.
Oracle now support data services - running on standalone machines, means no need of weblogic , glassfish application/ tools,
installation / is simple need to define
Port and Image in
standalone.http.port=8080
standalone.static.images=/home/oracle/apex/images
Based on the installation in the linked article, you will find the standalone settings in the following file.
/home/oracle/ords-3.0.7-conf/ords/standalone/standalone.properties
-------------
you can always star the service using
cd ~/ords-3.0.7
$JAVA_HOME/bin/java -jar ords.war
ctrl+C will stop the service, for production environment , use Nohup to run the process in backgrond and you can kill the process when you want to stop.
Sunday, October 23, 2016
Script to get Details ASM - Oracle Given Script 1
SPOOL ASM<#>_GENERIC_ASM_METADATA.html
-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2 & 12.1
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " " FROM DUAL;
SELECT 'INSTANCE NAME: ==)> ' , INSTANCE_NAME " " FROM V$INSTANCE;
SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';
SELECT * FROM V$INSTANCE;
SELECT * FROM GV$INSTANCE;
SELECT * FROM V$ASM_DISKGROUP;
SELECT GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, OS_MB, TOTAL_MB, FREE_MB, NAME, FAILGROUP, PATH
FROM V$ASM_DISK ORDER BY GROUP_NUMBER, FAILGROUP, DISK_NUMBER;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT SUBSTR(D.NAME,1,16) AS ASMDISK, D.MOUNT_STATUS, D.STATE,
DG.NAME AS DISKGROUP FROM V$ASM_DISKGROUP DG, V$ASM_DISK D
WHERE DG.GROUP_NUMBER = D.GROUP_NUMBER;
SELECT * FROM V$ASM_CLIENT;
SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C
WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;
SELECT * FROM V$ASM_ATTRIBUTE;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM GV$ASM_OPERATION;
SELECT * FROM V$VERSION;
SELECT * FROM V$ASM_ACFSSNAPSHOTS;
SELECT * FROM V$ASM_ACFSVOLUMES;
SELECT * FROM V$ASM_FILESYSTEM;
SELECT * FROM V$ASM_VOLUME;
SELECT * FROM V$ASM_VOLUME_STAT;
SELECT * FROM V$ASM_USER;
SELECT * FROM V$ASM_USERGROUP;
SELECT * FROM V$ASM_USERGROUP_MEMBER;
SELECT * FROM V$ASM_DISK_IOSTAT;
SELECT * FROM V$ASM_DISK_STAT;
SELECT * FROM V$ASM_DISKGROUP_STAT;
SELECT * FROM V$ASM_TEMPLATE;
SHOW PARAMETER
SHOW SGA
!echo "SELECT '" > /tmp/GPNPTOOL.SQL 2> /dev/null
! $ORACLE_HOME/bin/gpnptool get >> /tmp/GPNPTOOL.SQL 2>> /dev/null
!echo "' FROM DUAL;" >> /tmp/GPNPTOOL.SQL 2>> /dev/null
! cat /tmp/GPNPTOOL.SQL
SET ECHO OFF
--DISPLAYS INFORMATION ABOUT THE CONTENTS OF THE SPFILE.
SELECT * FROM V$SPPARAMETER ORDER BY 2;
SELECT * FROM GV$SPPARAMETER ORDER BY 3;
--DISPLAYS INFORMATION ABOUT THE INITIALIZATION PARAMETERS THAT ARE CURRENTLY IN EFFECT IN THE INSTANCE.
SELECT * FROM V$SYSTEM_PARAMETER ORDER BY 2;
SELECT * FROM GV$SYSTEM_PARAMETER ORDER BY 3;
-- 12C ACFS VIEWS
SELECT * FROM V$ASM_ACFS_ENCRYPTION_INFO;
SELECT * FROM V$ASM_ACFSREPL;
SELECT * FROM V$ASM_ACFSREPLTAG;
SELECT * FROM V$ASM_ACFS_SEC_ADMIN;
SELECT * FROM V$ASM_ACFS_SEC_CMDRULE;
SELECT * FROM V$ASM_ACFS_SEC_REALM;
SELECT * FROM V$ASM_ACFS_SEC_REALM_FILTER;
SELECT * FROM V$ASM_ACFS_SEC_REALM_GROUP;
SELECT * FROM V$ASM_ACFS_SEC_REALM_USER;
SELECT * FROM V$ASM_ACFS_SEC_RULE;
SELECT * FROM V$ASM_ACFS_SEC_RULESET;
SELECT * FROM V$ASM_ACFS_SEC_RULESET_RULE;
SELECT * FROM V$ASM_ACFS_SECURITY_INFO;
SELECT * FROM V$ASM_ACFSTAG;
-- 12C ASM AUDIT VIEWS
SELECT * FROM V$ASM_AUDIT_CLEAN_EVENTS;
SELECT * FROM V$ASM_AUDIT_CLEANUP_JOBS;
SELECT * FROM V$ASM_AUDIT_CONFIG_PARAMS;
SELECT * FROM V$ASM_AUDIT_LAST_ARCH_TS;
-- 12C ASM ESTIMATE VIEW
SELECT * FROM V$ASM_ESTIMATE;
SELECT * FROM GV$ASM_ESTIMATE;
SPOOL OFF
EXIT
-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2 & 12.1
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " " FROM DUAL;
SELECT 'INSTANCE NAME: ==)> ' , INSTANCE_NAME " " FROM V$INSTANCE;
SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';
SELECT * FROM V$INSTANCE;
SELECT * FROM GV$INSTANCE;
SELECT * FROM V$ASM_DISKGROUP;
SELECT GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, OS_MB, TOTAL_MB, FREE_MB, NAME, FAILGROUP, PATH
FROM V$ASM_DISK ORDER BY GROUP_NUMBER, FAILGROUP, DISK_NUMBER;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT SUBSTR(D.NAME,1,16) AS ASMDISK, D.MOUNT_STATUS, D.STATE,
DG.NAME AS DISKGROUP FROM V$ASM_DISKGROUP DG, V$ASM_DISK D
WHERE DG.GROUP_NUMBER = D.GROUP_NUMBER;
SELECT * FROM V$ASM_CLIENT;
SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C
WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;
SELECT * FROM V$ASM_ATTRIBUTE;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM GV$ASM_OPERATION;
SELECT * FROM V$VERSION;
SELECT * FROM V$ASM_ACFSSNAPSHOTS;
SELECT * FROM V$ASM_ACFSVOLUMES;
SELECT * FROM V$ASM_FILESYSTEM;
SELECT * FROM V$ASM_VOLUME;
SELECT * FROM V$ASM_VOLUME_STAT;
SELECT * FROM V$ASM_USER;
SELECT * FROM V$ASM_USERGROUP;
SELECT * FROM V$ASM_USERGROUP_MEMBER;
SELECT * FROM V$ASM_DISK_IOSTAT;
SELECT * FROM V$ASM_DISK_STAT;
SELECT * FROM V$ASM_DISKGROUP_STAT;
SELECT * FROM V$ASM_TEMPLATE;
SHOW PARAMETER
SHOW SGA
!echo "SELECT '" > /tmp/GPNPTOOL.SQL 2> /dev/null
! $ORACLE_HOME/bin/gpnptool get >> /tmp/GPNPTOOL.SQL 2>> /dev/null
!echo "' FROM DUAL;" >> /tmp/GPNPTOOL.SQL 2>> /dev/null
! cat /tmp/GPNPTOOL.SQL
SET ECHO OFF
--DISPLAYS INFORMATION ABOUT THE CONTENTS OF THE SPFILE.
SELECT * FROM V$SPPARAMETER ORDER BY 2;
SELECT * FROM GV$SPPARAMETER ORDER BY 3;
--DISPLAYS INFORMATION ABOUT THE INITIALIZATION PARAMETERS THAT ARE CURRENTLY IN EFFECT IN THE INSTANCE.
SELECT * FROM V$SYSTEM_PARAMETER ORDER BY 2;
SELECT * FROM GV$SYSTEM_PARAMETER ORDER BY 3;
-- 12C ACFS VIEWS
SELECT * FROM V$ASM_ACFS_ENCRYPTION_INFO;
SELECT * FROM V$ASM_ACFSREPL;
SELECT * FROM V$ASM_ACFSREPLTAG;
SELECT * FROM V$ASM_ACFS_SEC_ADMIN;
SELECT * FROM V$ASM_ACFS_SEC_CMDRULE;
SELECT * FROM V$ASM_ACFS_SEC_REALM;
SELECT * FROM V$ASM_ACFS_SEC_REALM_FILTER;
SELECT * FROM V$ASM_ACFS_SEC_REALM_GROUP;
SELECT * FROM V$ASM_ACFS_SEC_REALM_USER;
SELECT * FROM V$ASM_ACFS_SEC_RULE;
SELECT * FROM V$ASM_ACFS_SEC_RULESET;
SELECT * FROM V$ASM_ACFS_SEC_RULESET_RULE;
SELECT * FROM V$ASM_ACFS_SECURITY_INFO;
SELECT * FROM V$ASM_ACFSTAG;
-- 12C ASM AUDIT VIEWS
SELECT * FROM V$ASM_AUDIT_CLEAN_EVENTS;
SELECT * FROM V$ASM_AUDIT_CLEANUP_JOBS;
SELECT * FROM V$ASM_AUDIT_CONFIG_PARAMS;
SELECT * FROM V$ASM_AUDIT_LAST_ARCH_TS;
-- 12C ASM ESTIMATE VIEW
SELECT * FROM V$ASM_ESTIMATE;
SELECT * FROM GV$ASM_ESTIMATE;
SPOOL OFF
EXIT
Friday, November 13, 2015
Mount Point Utilization in readable format
Mount Point Utilization in readable format HP UX
df -Pk | awk '
BEGIN {print "Filesystem Mount Point Total GB Avail GB Used GB Used"
print "----------------------------------- ------------------------- ---------- ---------- ---------- -----"}
END {print ""}
/dev/ || /^[0-9a-zA-Z.]*:\// {
printf ("%-35.35s %-25s %10.2f %10.2f %10.2f %4.0f%\n",$1,$6,$2/1024/1024,$4/1024/1024,$3/1024/1024,$5)
}'
should work for other servers too.
df -Pk | awk '
BEGIN {print "Filesystem Mount Point Total GB Avail GB Used GB Used"
print "----------------------------------- ------------------------- ---------- ---------- ---------- -----"}
END {print ""}
/dev/ || /^[0-9a-zA-Z.]*:\// {
printf ("%-35.35s %-25s %10.2f %10.2f %10.2f %4.0f%\n",$1,$6,$2/1024/1024,$4/1024/1024,$3/1024/1024,$5)
}'
should work for other servers too.
Sunday, November 8, 2015
How much Archives generated Today with Switches - Script
you can modify the below script by modifying sysdate -1,-2,-3 .. etc for the number of days you want.
set pagesize 10000 linesize 10000
set feedback 0
column date format a18
select to_char(first_time, 'YYYY-MM-DD Dy') as "date",
round( sum((blocks + 1) * block_size)
/ 1024 / 1024 / 1024) as "size_gb",
count (*) as "number_of_switches_per_day"
from v$archived_log
where dest_id = 1
group by to_char(first_time,'YYYY-MM-DD Dy')
order by to_char(first_time,'YYYY-MM-DD Dy');
select round(sum((blocks + 1) * block_size)
/ 1024 / 1024 / 1024) as "todays_size_gb",
count (*) as "number_of_switches_today"
from v$archived_log
where dest_id = 1
and first_time >= sysdate - 1;
set pagesize 10000 linesize 10000
set feedback 0
column date format a18
select to_char(first_time, 'YYYY-MM-DD Dy') as "date",
round( sum((blocks + 1) * block_size)
/ 1024 / 1024 / 1024) as "size_gb",
count (*) as "number_of_switches_per_day"
from v$archived_log
where dest_id = 1
group by to_char(first_time,'YYYY-MM-DD Dy')
order by to_char(first_time,'YYYY-MM-DD Dy');
select round(sum((blocks + 1) * block_size)
/ 1024 / 1024 / 1024) as "todays_size_gb",
count (*) as "number_of_switches_today"
from v$archived_log
where dest_id = 1
and first_time >= sysdate - 1;
Monday, November 2, 2015
Recompile Objects Script
set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select
'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects where status = 'INVALID' and
object_type in ('PACKAGE','FUNCTION','PROCEDURE');
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select
'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects where status = 'INVALID' and
object_type in ('PACKAGE','FUNCTION','PROCEDURE');
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql
Thursday, October 29, 2015
ORA-10997: another startup/shutdown operation of this instance inprogress
SQL> startup
ORA-10997: another startup/shutdown operation of this instance inprogress
ORA-09968: unable to lock file
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Delete or rename the file $ORACLE_HOME/dbs/lkinst<instance_name>
A new file will be created when the instance is started up again.
Then startup normal
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 20 20:36:34 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2233336 bytes
Variable Size 2969570312 bytes
Database Buffers 1291845632 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened.
Delete or rename the file $ORACLE_HOME/dbs/lkinst<instance_name>
A new file will be created when the instance is started up again.
Then startup normal
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 20 20:36:34 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2233336 bytes
Variable Size 2969570312 bytes
Database Buffers 1291845632 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened.
Tuesday, October 27, 2015
RMAN HOT BACKUP CLONING Steps
Test/Development Database
Refresh From Production Procedure
The following note describes the generic procedure to be followed to
refresh a Energy Test or Development environment database from a
Energy production database backup.
In this example, we are refreshing the DEVE72 database from a backup
taken of the PRD22 database.
The assumption here is that the required RMAN production backup has
already been either copied from the production database via scp or
has been restored from tape or has been placed in an NFS shared
location which is accessible from both machines.
On the target machine the backups have been restored in the location
u02/backup/DEVE72
Procedure:
- Shutdown the DEVE72 database if it is already running
[oracle@DEVE72
DEVE72]$ ps
-ef |grep pmon
oracle
12701 29275 0 15:36:00 pts/3 0:00 grep pmon
oracle
7377 2235 0 May 19 ? 84:59
ora_pmon_DEVE72
[oracle@DEVE72
DEVE72]$ echo
$ORACLE_SID
DEVE72
[oracle@DEVE72
DEVE72]$ sqlplus
sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - Production on Thu Jun 17 15:36:22 2010
Copyright
(c) 1982, 2007, Oracle. All Rights Reserved.
Enter
password:
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
- Delete the database data files, redo log files and control files
Note:
**Ensure we are connected to the right server and are in the
appropriate directory location**
[oracle@DEVE72
DEVE72]$ hostname
DEVE72
[oracle@DEVE72
DEVE72]$ pwd
/u03/oradata/DEVE72
[oracle@DEVE72
DEVE72]$ rm
*.dbf
[oracle@DEVE72
DEVE72]$ cd /u04/oradata/DEVE72
[oracle@DEVE72
DEVE72]$ ls
control2.ctl
redo01a.log redo02a.log redo03a.log
[oracle@DEVE72
DEVE72]$ rm
*.ctl
[oracle@DEVE72
DEVE72]$ rm
*.log
[oracle@DEVE72
DEVE72]$ cd
/u05/oradata/DEVE72
[oracle@DEVE72
DEVE72]$ ls
control3.ctl
redo01b.log redo02b.log redo03b.log
- Copy the current init.ora parameter file of the DEVE72 database and create a parameter file with the name of the source production database (PRD22)
[oracle@DEVE72
~]$ cd
$ORACLE_HOME/dbs
[oracle@DEVE72
dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@DEVE72
dbs]$ cp
initDEVE72.ora initprd22.ora
- Make the following changes to the initprd22.ora
*.db_name='prd22'
- Set the environment to reflect the source production database and start the instance in NOMOUNT mode
[oracle@DEVE72
dbs]$ export
ORACLE_SID=prd22
[oracle@DEVE72
dbs]$ sqlplus
sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - Production on Mon Jun 21 12:58:32 2010
Copyright
(c) 1982, 2007, Oracle. All Rights Reserved.
Enter
password:
Connected
to an idle instance.
SQL>
startup
nomount pfile=$ORACLE_HOME/dbs/initprd22.ora
ORACLE
instance started.
Total
System Global Area 3154116608 bytes
Fixed
Size 2043904 bytes
Variable
Size 637538304 bytes
Database
Buffers 2499805184 bytes
Redo
Buffers 14729216 bytes
SQL>
quit
- Restore the control file from the backup location
The control file backup exists in the format
“c-<DBID>-<DATE>-<BACKUP SEQUENCE NUMBER>
Select the controlfile appropriate to the period of time that we wish
to restore the database from
[oracle@DEVE72
DEVE72]$ rman
target /
Recovery
Manager: Release 10.2.0.4.0 - Production on Fri Jun 18 11:05:20 2010
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
connected
to target database: prd22 (not mounted)
RMAN>
restore
controlfile from '/u02/backup/DEVE72/c-4031762323-20100616-00';
Starting
restore at 18-JUN-10
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=538 devtype=DISK
channel
ORA_DISK_1: restoring control file
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:06
output
filename=/u03/oradata/DEVE72/control1.ctl
output
filename=/u04/oradata/DEVE72/control2.ctl
output
filename=/u05/oradata/DEVE72/control3.ctl
Finished
restore at 18-JUN-10
- Mount the database
RMAN>
alter
database mount
2>
;
database
mounted
released
channel: ORA_DISK_1
- Catalog the RMAN backup sets which have been copied from the source production database
RMAN>
catalog
start with '/u02/backup/DEVE72';
searching
for all files that match the pattern /u02/backup/DEVE72
List
of Files Unknown to the Database
=====================================
File
Name: /u02/backup/DEVE72/c-4031762323-20100616-00
File
Name: /u02/backup/DEVE72/21lgatsk_1_1
File
Name: /u02/backup/DEVE72/c-2263349373-20100419-00
...
...
Do
you really want to catalog the above files (enter YES or NO)? YES
cataloging
files...
cataloging
done
List
of Cataloged Files
=======================
File
Name: /u02/backup/DEVE72/c-4031762323-20100616-00
File
Name: /u02/backup/DEVE72/21lgatsk_1_1
File
Name: /u02/backup/DEVE72/2flgdi89_1_1
...
...
List
of Files Which Where Not Cataloged
=======================================
File
Name: /u02/backup/DEVE72/c-2263349373-20100419-00
RMAN-07518:
Reason: Foreign database file DBID: 2263349373 Database Name: DEVE72
Note – ignore any errors reported for files that are not cataloged
- Determine the last archivelog sequence included in the backup. We will be recovering the database until this particular sequence number.
Look
for the string "List of archived logs"
RMAN
> list
backup of archivelog all
List
of Archived Logs in backup set 69
Thrd
Seq Low SCN Low Time Next SCN Next Time
----
------- ---------- --------- ---------- ---------
1
79 7970987 16-JUN-10 7973402 16-JUN-10
In this case, the last archivelog backed up belongs to sequence
number 79. If we wish to recover the database until the last archived
log which has been backed up, we need to increment the last sequence
number by 1. So in this case it will be 79+1 or 80
- Create the following files in the location “/u02/backup/{DB_NAME}”
vi
rman_head
RUN
{
#
allocate a channel to the tape device
ALLOCATE
CHANNEL d1 DEVICE TYPE disk;
#
rename the datafiles and online redo logs
vi
rman_tail
#
Do a SET UNTIL to prevent recovery of the online logs
SET
UNTIL SEQUENCE 80;
#
restore the database and switch the datafile names
RESTORE
DATABASE;
SWITCH
DATAFILE ALL;
#
recover the database
RECOVER
DATABASE;
}
vi
generate_datafiles.sql
set
head off pages 0 feed off echo off verify off
set
lines 200
spool
rename_datafiles.lst
select
'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' ||
'/u03/oradata/&1/' || substr(name,instr(name,'/',-1)+1) || ''';'
from v$datafile;
spool
off
exit;
vi
generate_logfiles.sql
set
head off pages 0 feed off echo off
spool
rename_logfiles.lst
SELECT
'SQL "ALTER DATABASE RENAME FILE '''''|| MEMBER ||''''''
||chr(10)||'to
''''' || member || '''''" ;' FROM V$LOGFILE;
exit
- Generate data file rename script
While in directory /u02/backup/{DB_NAME}, connect as sys as sysdba
via SQL*PLUS session and run the generate_datafiles.sql script.
The generate_datafiles.sql script accepts a parameter which is the
target database name.
SQL> @generate_datafiles DEVE72
It will create a file rename_datafiles.lst . The contents of this
file will be like this:
SET
NEWNAME FOR DATAFILE 1 TO '/u03/oradata/DEVE72/system01.dbf';
SET
NEWNAME FOR DATAFILE 2 TO '/u03/oradata/DEVE72/undotbs01.dbf';
SET
NEWNAME FOR DATAFILE 3 TO '/u03/oradata/DEVE72/sysaux01.dbf';
….
….
- Generate redo log file rename script
While in directory /u02/backup/{DB_NAME}, connect as sys as sysdba
via SQL*PLUS session and run the generate_logfiles.sql script.
SQL> @generate_logfiles.sql
It will create a file called rename_logfiles.lst
Edit the rename_logfiles.lst file and change values of prd22 to
DEVE72
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo01a.log''
to ''/u04/oradata/DEVE72/redo01a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo01b.log''
to ''/u05/oradata/DEVE72/redo01b.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo02a.log''
to ''/u04/oradata/DEVE72/redo02a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo02b.log''
to ''/u05/oradata/DEVE72/redo02b.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo03a.log''
to ''/u04/oradata/DEVE72/redo03a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo03b.log''
to ''/u05/oradata/DEVE72/redo03b.log''" ;
- Specify the archive log sequence until which recovery will be performed
Edit the rman_tail file and change the line with the words “>>
SET UNTIL SEQUENCE 80” to include the appropriate archive log
sequence which was noted in an earlier step.
- Prepare the RMAN restore and recover database script
[oracle@DEVE72
DEVE72]$ cat
rman_head rename_datafiles.lst rename_logfiles.lst rman_tail >
rman_recovery.rcv
- Connect to the target database via RMAN and execute the rman_recovery.rcv script
[oracle@DEVE72
dbs]$ rman
target /
Recovery
Manager: Release 10.2.0.4.0 - Production on Mon Jun 21 13:04:04 2010
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
connected
to target database (not started)
RMAN>
@rman_recovery.rcv
Note:
At this stage, we can continue to recover the database and keep it
in sync with the source production database by manually applying the
archive log files which are copied from the production server to the
log archive destination of the test database on the target server.
We can do this via SQL*PLUS connected as SYS by issuing the command
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
When there are no more archive log files to apply, we enter CANCEL
- Open the database with RESETLOGS
After the RMAN script has successfully run and recovered the database
until the last archive log sequence, we will now open the database
using the ALTER DATABASE OPEN RESETLOGS command executed either via
RMAN or from SQL*PLUS connected as SYS.
SQL>
alter
database open resetlogs;
Database
altered.
- Temporary Tablespace Reconfiguration
After the restore, we will note that the temporary tablespace files
are still pointing to the source production database as these
tempfiles have not been renamed when we renamed all the database data
files in an earlier step.
Obtain the name of the current tempfile -
SQL>
select
name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u03/oradata/prd22/temp01.dbf
Drop the tempfile-
SQL>
ALTER DATABASE TEMPFILE '/u03/oradata/prd22/temp01.dbf' drop
including datafiles;
Database
altered.
Add a new tempfile for the refreshed database in the appropriate
location-
SQL>
ALTER TABLESPACE temp ADD TEMPFILE '/u03/oradata/DEVE72/temp01.dbf'
size 2G;
Tablespace
altered.
- At this stage we will change the passwords if required for the SYS and SYSTEM or any other database accounts.
- Change the database name using nid
We will now shutdown the database and then mount it.
We will then run the nid utility to change the database name –
we need to provide the appropriate password for the user SYS and the
new value we want for the database name.
[oracle@DEVE72
dbs]$ nid
target=sys dbname=DEVE72
DBNEWID:
Release 10.2.0.4.0 - Production on Fri Jun 18 13:55:14 2010
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
Password:
Connected
to database PRD22 (DBID=4031762323)
Connected
to server version 10.2.0
Control
Files in database:
/u03/oradata/DEVE72/control1.ctl
/u04/oradata/DEVE72/control2.ctl
/u05/oradata/DEVE72/control3.ctl
Change
database ID and database name PRD22 to DEVE72? (Y/[N]) => Y
Proceeding
with operation
Changing
database ID from 4031762323 to 2271553224
Changing
database name from PRD22 to DEVE72
Control
File /u03/oradata/DEVE72/control1.ctl - modified
Control
File /u04/oradata/DEVE72/control2.ctl - modified
Control
File /u05/oradata/DEVE72/control3.ctl - modified
Datafile
/u03/oradata/DEVE72/system01.dbf - dbid changed, wrote new name
Datafile
/u03/oradata/DEVE72/undotbs01.dbf - dbid changed, wrote new name
Datafile
/u03/oradata/DEVE72/sysaux01.dbf - dbid changed, wrote new name
Datafile
/u03/oradata/DEVE72/users01.dbf - dbid changed, wrote new name
...
...
...
Datafile
/u03/oradata/DEVE72/COGNOSPAD_CLOB01.dbf - dbid changed, wrote new
name
Datafile
/u03/oradata/DEVE72/temp01.dbf - dbid changed, wrote new name
Control
File /u03/oradata/DEVE72/control1.ctl - dbid changed, wrote new name
Control
File /u04/oradata/DEVE72/control2.ctl - dbid changed, wrote new name
Control
File /u05/oradata/DEVE72/control3.ctl - dbid changed, wrote new name
Instance
shut down
Database
name changed to DEVE72.
Modify
parameter file and generate a new password file before restarting.
Database
ID for database DEVE72 changed to 2271553224.
All
previous backups and archived redo logs for this database are
unusable.
Database
has been shutdown, open database with RESETLOGS option.
Succesfully
changed database name and ID.
DBNEWID
- Completed succesfully.
At this stage the database has been shutdown and now we need to mount
it and issue the RESETLOGS command after the database change.
Note:
We will now set the environment to the target database (until
this stage, for example, ORACLE_SID had been set to the production
database value)
[oracle@DEVE72
backup] export
ORACLE_SID=DEVE72
[oracle@DEVE72
backup]$ sqlplus
sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - Production on Mon Jun 21 14:12:00 2010
Copyright
(c) 1982, 2007, Oracle. All Rights Reserved.
Enter
password:
Connected
to an idle instance.
SQL>
startup
mount
ORACLE
instance started.
Total
System Global Area 3154116608 bytes
Fixed
Size 2043904 bytes
Variable
Size 637538304 bytes
Database
Buffers 2499805184 bytes
Redo
Buffers 14729216 bytes
SQL>
alter
database open resetlogs;
Database altered.
- Post Database Refresh Tasks
Create import and export directories required for Data Pump
/u02/export/{DB_NAME} - export_dir
/u02/import/{DB_NAME} - import_dir
RMAN – run the appropriate script to register details in the RMAN
catalog database where required.
Subscribe to:
Posts (Atom)