Drop and recreate Temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP_01 TEMPFILE '/u02/oradata/DEV/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_01;

DROP TABLESPACE TEMP including contents and datafiles;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/DEV/temp01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE TEMP_01 including contents and datafiles;

Add extra temp files

================

alter tablespace temp add tempfile '/u02/oradata/DEV/temp02.dbf' size 500m reuse autoextend on next 10m maxsize unlimited;

alter tablespace temp add tempfile '/u02/oradata/DEV/temp03.dbf' size 500m reuse autoextend on next 10m maxsize unlimited;

check the undo size

==============

SELECT SUM(a.bytes)/(1024*1024*1024) as

 UNDO_SIZE  FROM v$datafile a,

v$tablespace b,

dba_tablespaces c 

WHERE c.contents = 'UNDO'   AND c.status = 'ONLINE'   

AND b.name = c.tablespace_name   AND a.ts# = b.ts#;

 

 RMAN Duplicate

Duplicate a Database Using RMAN in Oracle Database 11g Release 2

https://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2

Recovery Manager (RMAN) Database Duplication Enhancements in Oracle Database 12c Release 1 (12.1)

https://oracle-base.com/articles/12c/recovery-manager-rman-database-duplication-enhancements-12cr1

 

Why is my SYSDATE / SYSTIMESTAMP time not the same as my system clock on Unix?

Dates & Calendars - Frequently Asked Questions (Doc ID 227334.1)

telnet/ssh to the Unix box
connect using sqlplus in the telnet session:

1) once through the listener using a tnsnames alias
$sqlplus user/password @ [tnsnames alias]
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

2) once through a "local" ORACLE_SID connection
$env | egrep 'ORACLE_SID'
$sqlplus user/password
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

Check that the time in the banner of sqlplus ( SQL*Plus: Release 10.1.0.4.0 - Production on Wo Jan 11 15:05:46 2006 ) is reflecting the time based on the current TZ set in the Unix (!) session.

For ASM

For 11.2.0.2 and above the TZ entry in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt sets to correct timezone. This enables resources started under the Grid Infrastructure home (listener, databases) using the timezone set in here and so has no requirement anymore to set TZ for DB and listener via setenv. For more details see: How To Change Timezone for 11gR2 Grid Infrastructure ( Document 1209444.1) 1) Check the environment variables set for database and listener in your CRS configuration srvctl getenv database -d <dbname> srvctl getenv nodeapps -n <nodename> -- If you are 11.2, then use 'getenv listener' instead of 'getenv nodeapps': srvctl getenv listener 2) Check the current environment variables used for PMON and Listener

 

This could be due to CredSSP encryption oracle remediation - RDP to Windows 10 pro host

Error

 Following Windows security updates in May 2018, when attempting to RDP to a Windows 10 Pro workstation the following error message is displayed after successfully entering user credentials:

An authentication error occurred. The function requested is not supported.

This could be due to CredSSP encryption oracle remediation

Screenshot

Solution

File rd_patch.reg:

Windows Registry Editor Version 5.00

 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP]

 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP\Parameters]

"AllowEncryptionOracle"=dword:00000002

 

For those who would like something easy to copy / paste into an elevated command prompt:

reg add HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP\Parameters /v AllowEncryptionOracle /t REG_DWORD /d 2 /f

RMAN Duplicate Error While restore RMAN backup of ASM environment to Non ASM environment

 I am trying to restore target database with below command :

rman auxiliary /

run {
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
allocate auxiliary channel ch5 type disk;
allocate auxiliary channel ch6 type disk;
allocate auxiliary channel ch7 type disk;
allocate auxiliary channel ch8 type disk;
duplicate target database to EFGH
BACKUP LOCATION '/bkup/ABCD'
UNTIL TIME "TO_DATE('2016-07-21 10:30:10', 'YYYY-MM-DD HH24:MI:SS')";
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
release channel ch8;
}
exit


Command is failing is with below error :

contents of Memory Script:
{
sql clone "alter system set db_name =
''EFGH'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
}
executing Memory Script

Errors in memory script
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 13658
ORA-06512: at line 1
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-19563: header validation failed for file
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 3939
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 3882
ORA-06512: at line 1
RMAN-05001: auxiliary file name +RECO_MP/ABCD/blcktrfile1.log conflicts with a file used by the target database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/21/2016 00:12:12
RMAN-05501: aborting duplication of target database

This seems to be failing as BCT appears to be enabled as per log, how can I control block change tracking at target side using duplicate command as I am simply opening the database in nomount mode, rest everything else is taking care off by duplicate command.


Regards,
Saurabh

and we said...

This is a known issue. I've copied the MOS note below

=======================
Rman Duplicate fail ORA-19755, Tries Open The Block Change Tracking File of Source DB (Doc ID 1098638.1) To BottomTo Bottom

In this Document
Symptoms
Cause
Solution
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 15-Apr-2014***
SYMPTOMS

When trying to do an rman duplicate with the source database using block change tracking file receiving
errors:


RMAN-03002: failure of Duplicate Db command at 05/11/2010 18:22:47
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/rman/I3VPROD1/archivelog/2010_05_11/o1_mf_1_40265_5ym0xdlc_.arc'
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '/rman/bct/blockchanges.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3



CAUSE

Unpublished Bug 7500916 RMAN DUPLICATE DOESN'T PRESERVE DATABASE LEVEL SUPPLEMENTAL LOGGING DIRECTIVES
Bug 10185641 RMAN'S DB_FILE_NAME_CONVERT OF DUPLICATE NOT APPLIED TO CHANGE TRACKING FILE
Bug 10193846 RMAN DUPLICATE FAILS WITH ORA-19755 WHEN BCT FILE OF PRIMARY IS NOT ACCESSIBLE

Unpublished Bug 11744544 Set newname for database does not apply to block change tracking file
fixed in 12.1
Description: Block change tracking recreation might fail if ot duplicating to OMF newnames
or db_file_name_convert can not be applied to it.



Bug 18371441 : RMAN DUPLICATE FAILS TO CREATE BCT FILE

SOLUTION


You are encountering one of several bugs. The best option is to apply the fix for unpublished Bug: 11744544 Set newname for database does not apply to block change tracking file

Unpublished Bug 11744544 Set newname for database does not apply to block change tracking file. fixed in 12.1
Description: Block change tracking recreation might fail if ot duplicating to OMF newnames
or db_file_name_convert can not be applied to it.

There are patches available for this bug for several platforms. With the patch, you can use new syntax:

SET NEWNAME FOR BLOCK CHANGE TRACKING FILE TO '...filename...';

to explicitly set the location of the block change tracking file

For example:

run
{
SET NEWNAME FOR BLOCK CHANGE TRACKING FILE TO '/oracle/oradata/TEST/block_change.bct';
...
duplicate database to TEST..;

}

OR

run
{
SET NEWNAME FOR BLOCK CHANGE TRACKING FILE TO '+DATA';
...
duplicate database to TEST..;

}





Unpublished Bug 7500916

This is fixed in the oracle patchset 11.2.0.2 as part of Bug 7500916.
The workaround is to disable change tracking before duplicate.

Bug 10185641

Workaround is to set DB_FILE_NAME_CONVERT in the SET-clause of duplicate, instead of using DB_FILE_NAME_CONVERT setting in an init.ora or spfile.
Example :
RMAN> duplicate database v112 to duplv112
backup location '/u01/oradata/v112/reco_area/V112/backupset/2010_10_09'
spfile parameter_value_convert ('v112', 'duplv112')
set db_file_name_convert = 'v112', 'duplv112'
noresume;
This bug is fixed in Product Version 12.1


Bug 10193846

Workaround is to create a dummy file in the location where the error ORA-19755 is signalled.
A workaround for all of the bugs would be to disable block change tracking on the target database before
duplicating.

In the case of using an UNTIL clause, disabling of BCT will not work unless the selected UNTIL time or SCN is AFTER the time of disabling block change tracking.

Bug 10193846 - RMAN duplicate fails with ORA-19755 when BCT file is not accessible (Doc ID 10193846.8)
This issue is fixed in:
12.1.0.1 (Base Release)
11.2.0.3 (Server Patch Set)
11.2.0.2.3 Database Patch Set Update
11.2.0.2 Bundle Patch 7 for Exadata Database
11.2.0.1 Bundle Patch 12 for Exadata Database
11.2.0.2 Patch 6 on Windows Platforms


(MAY-2014) Related open issue Bug 18371441 - RMAN DUPLICATE FAILS TO CREATE BCT FILE
============================

 

RMAN DUPLICATE ERRORS WITH RMAN-5565 - SPFILE NO FOUND (Doc ID 874202.1)

---

APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.2 [Release 11.2] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Information in this document applies to any platform. SYMPTOMS An attempt to perform a duplicate using 11GR2 feature of not connecting to a catalog or target can result in the following error: DUPLICATE DATABASE TO dupdb UNTIL TIME "TO_DATE('06/23/2009 14:45:00', 'MM/DD/YYYY HH24:MI:SS')" SPFILE BACKUP LOCATION '/recovery_area' NOFILENAMECHECK; Starting Duplicate Db at 2009/06/23 15:52:31 RMAN-571: =========================================================== RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-571: =========================================================== RMAN-3002: failure of Duplicate Db command at 06/23/2009 15:52:31 RMAN-5565: SPFILE backup created before TO_DATE('06/23/2009 14:45:00', 'MM/DD/YYYY HH24:MI:SS') not found in /recovery_area OR RMAN-03002: failure of Duplicate Db command RMAN-05579: CONTROLFILE backup not found in '/recovery_area' CAUSE The problem is caused due to Unpublished BUG 8626638. The backup location is directory off the root path. BACKUP LOCATION '/recovery_area' It is only specific to directory from the root mount point. SOLUTION There are two potential workarounds: Append a backslash at the end of the path ( i.e BACKUP LOCATION '/recovery_area/') Select a location to store the files which has not be created directly off the root mount point.

 

 Check The status of the cold backup before Perform Clone by creating control file method

=================================================================

select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE,fhafs,FHDBI,fhdbn from x$kcvfh order by fhsta;

FILENUMBER STATUS SCN SEQUENCE FHAFS FHDBI FHDBN
---------- ---------- ---------------- ---------- ---------------- ---------- ---------
6 4 2401335881 1 0 3300273167 PROD
2 4 2401335881 1 0 3300273167 PROD
3 4 2401335881 1 0 3300273167 PROD
4 4 2401335881 1 0 3300273167 PROD
5 4 2401335881 1 0 3300273167 PROD
11 4 2401335881 1 0 3300273167 PROD
7 4 2401335881 1 0 3300273167 PROD
8 4 2401335881 1 0 3300273167 PROD
9 4 2401335881 1 0 3300273167 PROD
10 4 2401335881 1 0 3300273167 PROD
1 8196 2401335881 1 0 3300273167 PROD

11 rows selected.
================================================================================================================================================================

See the status column, its 8194 (for system datafile) and 4 for non system datafiles,
the value 8196 and 4 denoted, online fuzzy, means that the database was opened, when the datafiles were copied,
if it was a cold backup, in that case the status column must be 8192 and 0 (Zero)