-- Take a count of invalid objects
-- check for locks

echo "Purpose: To spool out the invalid objects before and after applying the patch"
if [ $# -ne 2 ]; then
echo "Usage: $0 "
exit 1;
fi
genInvList(){
USER=apps
PASS=$1
TYPE=$2
DATEFMT=`date +%Y%m%d-%H%M`

if [ "x$ORACLE_SID" = "x" ]; then
if [ "x$TWO_TASK" = "x" ]; then
echo "set up the env first, duh!"
exit
else
DB=`echo $TWO_TASK`
fi
else
DB=`echo $ORACLE_SID`
fi


sqlplus -s /nolog<<_END_
conn ${USER}/${PASS}@${DB}
set pages 10000
col owner for a14
col object_type for a20
col status for a10
col OBJECT_NAME for a30
spool ${DB}-${TYPE}-invalid-${DATEFMT}.lst
select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;
select instance_name,status from v\$instance;
select owner, object_type, object_name
from dba_objects
where status = 'INVALID'
order by owner, object_type, object_name;
select owner, count(status)
from dba_objects
where status = 'INVALID'
group by owner;

select 'Total Invalids: '||count(*) from dba_objects where status != 'VALID';
spool off
exit
_END_
}
genInvList $1 $2



sh invalid.sh apps pre

recreate UNDO Tablespace

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g
How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g?

Solution:

1. Determine the size of your undo tablespace

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
12.09375

2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 400M;

Tablespace created.

3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
Change undo_tablespace=UNDOTBS2
Change undo_management=MANUAL

Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.

4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.
5. Startup the database
6. Confirm the new tablespace is in use:

SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------- ----------- ------------
undo_tablespace string UNDOTBS2

7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ --------------------------- ------------------------------ -----------
PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE

....etc.

If the old segments are online, then they must be taken offline:

SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;

This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
SQL>drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

9. Edit your init.ora file do the following changes.

undo_management='AUTO'
undo_tablespace='UNDOTBS2'

10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]

recreate TEMP Tablespace

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g
How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

a. Find Session Number from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

b. Find Session ID from V$SESSION:

If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

c. Kill Session:

Now kill the session with IMMEDIATE.

ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

Script – Database structure and file location

***************************************************
Script – Database structure and file location
****************************************************

set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name format a60 heading "Control Files"

select name
from sys.v_$controlfile
/

Prompt
Prompt Redo Log File Locations >>>>
Prompt

col Grp format 9999
col member format a50 heading "Online REDO Logs"
col File# format 9999
col name format a50 heading "Online REDO Logs"
break on Grp
select group#,member
from sys.v_$logfile
/


Prompt Data Files Locations >>>>

col Tspace format a25
col status format a3 heading Sta
col Id format 9999
col Mbyte format 999999999
col name format a50 heading "Database Data Files"
col Reads format 99,999,999
col Writes format 99,999,999

break on report
compute sum label 'Total(MB)' of Mbyte on report

select F.file_id Id,
F.file_name name,
F.bytes/(1024*1024) Mbyte,
decode(F.status,'AVAILABLE','OK',F.status) status,
F.tablespace_name Tspace
from sys.dba_data_files F
order by tablespace_name;

Full Database Backup script Through Rman

Execute Rman script:
------------------------

today=$(date +'%m-%d-%C%y;%H:%M')
echo $today
ORACLE_SID=testdb
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.
export ORACLE_HOME PATH ORACLE_SID
now=$(date +"%d-%m-%Y")
mkdir /u01/Rman_Backup/FULLDB_testdb_$now
export DAY='date +%d'
export NUM='expr $DAY / 7'

$ORACLE_HOME/bin/rman << EOF
connect target /
run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
#allocate channel ch02 type disk;
#allocate channel ch03 type disk;
#allocate channel ch04 type disk;
#allocate channel ch05 type disk;
#allocate channel ch06 type disk;
set command id to 'hot_database_backup';
backup
as compressed backupset
incremental level = 0
check logical
database tag='WEEKLY_BACKUP'
FORMAT '/u01/Rman_Backup/FULLDB_testdb_$now/testdb_%c_%d_S_%s_P_%p_T_%t' ;
backup
format '/u01/Rman_Backup/FULLDB_testdb_$now/c_%d_%Y%M%D.%s.%p.%t'
current controlfile;
release channel ch00;
release channel ch01;
#release channel ch02;
#release channel ch03;
#release channel ch04;
#release channel ch05;
#release channel ch06;
}
delete noprompt obsolete ;
list backup;
crosscheck backup;
delete noprompt expired backup;
delete noprompt expired copy;
delete noprompt expired archivelog all;
EOF
today=$(date +'%m-%d-%C%y;%H:%M')
echo $today

Rman Backup and Restore Steps

Rman Restore Backup Steps:
--------------------------

On Source Database:
-------------------


*********************************************************************

Datafiles Information:
-----------------------

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select 'set newname for datafile ' || file_id || ' to ''/u01/oradata/dhana/' ||
substr(file_name,instr(file_name,'/',-1)+1) || ''';'
from dba_data_files;

spool off
exit;

Redo log Location:
------------------------

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_logfiles.lst
select 'alter database rename file '||''''||member||''''||' to '||''''||'/u01/oradata/dhana/'||substr(member,instr(member,'/',-1)+1)||''''||';' from

v$logfile ;
spool off
exit;

Temp files Information:
-------------------------
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_tempfiles.lst
select 'set newname for tempfile '||''''||name||''''||' to ''/u01/oradata/dhana/' ||
substr(name,instr(name,'/',-1)+1) || ''';'
from v$tempfile;

spool off
exit;

********************************************************************

Execute Rman script:
------------------------

***************************************************************************
today=$(date +'%m-%d-%C%y;%H:%M')
echo $today

ORACLE_SID=testdb

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.

export ORACLE_HOME PATH ORACLE_SID

now=$(date +"%d-%m-%Y")

mkdir /u01/Rman_Backup/FULLDB_testdb_$now

export DAY='date +%d'

export NUM='expr $DAY / 7'

$ORACLE_HOME/bin/rman << EOF connect target / run { allocate channel ch00 type disk; allocate channel ch01 type disk; set command id to 'hot_database_backup'; backup as compressed backupset incremental level = 0 check logical database tag='WEEKLY_BACKUP' FORMAT '/u01/Rman_Backup/FULLDB_testdb_$now/testdb_%c_%d_S_%s_P_%p_T_%t' ; backup format '/u01/Rman_Backup/FULLDB_testdb_$now/c_%d_%Y%M%D.%s.%p.%t' current controlfile; release channel ch00; release channel ch01; } delete noprompt obsolete ; list backup; crosscheck backup; delete noprompt expired backup; delete noprompt expired copy; delete noprompt expired archivelog all; EOF today=$(date +'%m-%d-%C%y;%H:%M') echo $today ************************************************************************** Create pfile from spfile; cp init<>.ora

need to create same database name.

scp -r FULLDB_testdb_14-05-2016 oracle@192.168.2.142:/home/oracle

scp -r files oracle@192.168.2.142:/home/oracle

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

Target Machine
--------------


chmod -R 777 FULLDB_testdb_14-05-2016/

After making the necessary changes to the PFILE we now will bring the database back up in NOMOUNT mode so we can restore the control files. When

bringing the database up in NOMOUNT mode we will use the PFILE we edited earlier.

need to create necessary folders for control and datafiles.

add this parameter also must db_unique_name=dhana

mkdir -p /u01/app/oracle/admin/dhana/adump
mkdir -p /u01/oradata/dhana
mkdir -p /u01/oradata/dhana/arc

. oraenv

set source database sid now.

. oraenv
ORACLE_SID = [final] ? testdb
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/dbhome_1

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdhana.ora';
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2252824 bytes
Variable Size 180359144 bytes
Database Buffers 125829120 bytes
Redo Buffers 4718592 bytes
SQL>exit


$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 14 00:35:01 2016

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

connected to target database: TESTDB (not mounted)

RMAN> restore controlfile from '/home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959';

Starting restore at 14-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/dhana/control01.ctl
output file name=/u01/oradata/dhana/control02.ctl
Finished restore at 14-MAY-16


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/home/oracle/FULLDB_testdb_14-05-2016';

Example Output:

searching for all files that match the pattern /home/oracle/FULLDB_testdb_14-05-2016

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_33_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_32_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959

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

List of Cataloged Files
=======================
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_33_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_32_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959


RMAN> list backup;


RMAN> run {
2> @rename_datafiles.lst
3> set newname for datafile 4 to '/u01/oradata/dhana/users01.dbf';


4> set newname for datafile 3 to '/u01/oradata/dhana/undotbs01.dbf';


5> set newname for datafile 2 to '/u01/oradata/dhana/sysaux01.dbf';


6> set newname for datafile 1 to '/u01/oradata/dhana/system01.dbf';


7> **end-of-file**
8> restore database;
9> switch datafile all;
10> recover database;
11> }

need to add set newname for tempfile '/u01/oradata/testdb/temp01.dbf' to '/u01/oradata/dhana/temp01.dbf';

SQL> @rename_logfiles.lst;

Database altered.


Database altered.


Database altered.

SQL> alter database open resetlogs;

Database altered.


********************************************************

Change database name using NID

cd $ORACLE_HOME/dbs

$ orapwd file=orapwdhana password=****** entries=5

nid target=sys/admin123 dbname=dhana

sqlplus / as sysdba
SQL> startup nomount;
SQL> ALTER SYSTEM SET db_name=dhana SCOPE=SPFILE; (using pfile change db name manually)
SQL> shut IMMEDIATE
SQL> startup mount;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SELECT name, open_mode FROM v$database;


****************************************************