Wednesday, February 24, 2021


Add /modify Datafile in ASM


https://orahow.com/add-datafile-in-oracle/

http://www.oracle-wiki.net/startdocsusefuloraclecommands

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

Query to Check File_name, initial size and maxsize

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

select FILE_NAME,FILE_ID,AUTOEXTENSIBLE,bytes/1024/1024/1024,STATUS,MAXBYTES/1024/1024/1024 from dba_data_files
where TABLESPACE_NAME='&TABLESPACE_NAME';



To check the location of existing datafiles
================================

SQL> show parameter db_create_file_dest;

SQL> select file_name from dba_data_files;

SQL> select file_name,bytes/1024/1024 mb
           from dba_data_files
           where tablespace_name like 'SYSTEM%'
           order by file_name;


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

Add Datafile
=============

ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '+DATA' SIZE 16M AUTOEXTEND ON MAXSIZE 31G;

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

To enable datafile autoextend and set the Maxsize
======================================

ALTER DATABASE datafile '+DATA/SSLCDB/AF17DB97F89A0156E053AC1002963695/DATAFILE/system.301.1050883993' autoextend ON maxsize 31G;

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

Resize Datafiles
=============

alter database datafile '+DATA/SSLCDB/AF17DB97F89A0156E053AC1002963695/DATAFILE/system.301.1050883993' resize 20G;






Wednesday, February 17, 2021

 

INS-20802: Oracle Net Configuration Assistant failed error


Oracle client 12.1.0.2 on it. The Oracle client installation used to fail at the last stage with this error named INS-20802.


















installing Microsoft Visual C++ 2010 Redistributable Package (x86) and this solved the problem



Sunday, January 10, 2021

 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#;

Saturday, January 2, 2021

 

 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

Thursday, December 24, 2020

 

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


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

Wednesday, December 23, 2020

 

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

Thursday, December 17, 2020


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 Recovery Catalog About Recovery Catalog RMAN recovery catalog Is another database which is out of your normal databases or which is o...