Wednesday, January 29, 2020


RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file versio n 12.1.0.0.0 incompatible with ORACLE version 12.0.0.0.0

Issue:
While performing the clone of Oracle Database using ‘Active Duplication’, the clone may fail with below errors,
RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 12.1.0.0.0 incompatible with ORACLE version 12.0.0.0.0


Cause:

Target database might be having lower Compatibility set in parameter file /SPFILE



Connect to sqlplus ‘/ as sysdba’ in target Database and issue the below command to check the compatibility set in Parameter file/SPFILE

SQL> show parameter comp



NAME                                             TYPE                                                    Value

—————-                                ————                                                  —————-

cell_offload_compaction                string                                                     ADAPTIVE

compatible                                      string                                                    12.1.0.0

nls_comp                                        string                                                     BINARY

plsql_v2_compatibility                  boolean                                                 FALSE

Solution
==========
create pfile manually ( initTEST.ora)

Add below lines only


DB_NAME=TEST
*.compatible='12.1.0.0.0'






RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/17/2017 11:02:45
RMAN-05501: aborting duplication of target database
RMAN-05520: database name mismatch, auxiliary instance has PROD, command specified CLONE

Cause: Cause:

I started the duplicate while instance is in nomount and duplicate returned into error after mounting the database due to no backup or found. This duplicate was triggered after connecting to both target and auxiliary instances. During the clone RMAN will begin the clone instance with the target instance name (its PROD in my case) and proceed with the duplicate. When the duplication is successful it will clone the database with the auxiliary instance name. Since the duplicate resulted into error it left the instance with target instance name (PROD).

Fix :

- Just shut abort or immediate the auxiliary instance.
- Start the auxiliary instance (CLONE) in nomount using pfile
- Re-run the duplicate


Tuesday, January 28, 2020



Delete expired archivelogs using RMAN

Below commands will helpful for deleting expired archivelog files through RMAN :
RMAN>list expired archivelog all;
RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all;
Now check the output with below command it should not return any list
RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>

Wednesday, January 22, 2020


RMAN-04014: startup failed: ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated ORA-09291: sksachk: invalid device specified for archive destination

While performing RMAN refresh to 12.2.0.1.0 from 12.1.0.2.0, below error was encountered:
Recovery Manager: Release 12.2.0.1.0 – Production on Tue Nov 14 11:45:26 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.connected to auxiliary database: DEVDB (not mounted)RMAN> run{DUPLICATE TARGET DATABASE TO 'DEVDB'SPFILEparameter_value_convert ('PRODB','DEVDB')set db_file_name_convert='D:\APP\ORACLE\ORADATA\PRODB\DATAFILE\','D:\APP\ORACLE\ORADATA\DEVDB\DATAFILE\'set log_file_name_convert='D:\APP\ORACLE\ORADATA\PRODB\DATAFILE\','D:\APP\ORACLE\ORADATA\DEVDB\DATAFILE\'set audit_file_dest='D:\app\Oracle\admin\DEVDB\adump'set core_dump_dest='D:\app\Oracle\admin\DEVDB\cdump'set control_files='D:\APP\ORACLE\ORADATA\DEVDB\CONTROLFILE\O1_MF_F06L807F_.CTL','D:\APP\ORACLE\ORADATA\DEVDB\CONTROLFILE\O2_MF_F06L807F_.CTL','D:\APP\ORACLE\ORADATA\DEVDB\CONTROLFILE\O3_MF_F06L807F_.CTL'set db_name='DEVDB'BACKUP LOCATION 'D:\app\Oracle\Backup\RMAN'NOFILENAMECHECK; } Starting Duplicate Db at 14-NOV-17------- Oracle instance shut down connected to auxiliary database (not started)RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of Duplicate Db command at 11/14/2017 11:46:16RMAN-05501: aborting duplication of target databaseRMAN-03015: error occurred in stored script Memory ScriptRMAN-04014: startup failed: ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translatedORA-09291: sksachk: invalid device specified for archive destinationOSD-04018: Unable to access the specified directory or device.O/S-Error: (OS 3) The system cannot find the path specified. Recovery Manager complete.
run
{
DUPLICATE TARGET DATABASE TO 'DEVDB'
SPFILE
parameter_value_convert ('PRODB','DEVDB')
set db_file_name_convert='D:\APP\ORACLE\ORADATA\PRODB\DATAFILE\','D:\APP\ORACLE\ORADATA\DEVDB\DATAFILE\'
set log_file_name_convert='D:\APP\ORACLE\ORADATA\PRODB\DATAFILE\','D:\APP\ORACLE\ORADATA\DEVDB\DATAFILE\'
set audit_file_dest='D:\app\Oracle\admin\DEVDB\adump'
# set core_dump_dest='D:\app\Oracle\admin\DEVDB\cdump'
set log_archive_dest_1='location=D:\app\Oracle\fast_recovery_area\DEVDB\DEVDB\ARCHIVELOG'
set control_files='D:\APP\ORACLE\ORADATA\DEVDB\CONTROLFILE\O1_MF_F06L807F_.CTL','D:\APP\ORACLE\FAST_RECOVERY_AREA\DEVDB\DEVDB\CONTROLFILE\O1_MF_F06L808F_.CTL','D:\APP\ORACLE\ORADATA\DEVDB\CONTROLFILE\O3_MF_F06L807F_.CTL'
set db_name='DEVDB'
BACKUP LOCATION 'D:\app\Oracle\Backup\RMAN'
NOFILENAMECHECK;
}

Solution:
Amend set log_archive_dest_1=’location=D:\app\Oracle\fast_recovery_area\DEVDB\DEVDB\ARCHIVELOG’

Tuesday, January 21, 2020






APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 10-Dec-2015***

GOAL

Rman 11g release 2 provides us the flexibility of creating the rman duplicate without connecting to the target database and recovery catalog.
All rman needs here is the location of the backup pieces which is accessible and rman reads the backup pieces and restores the spfile,controlfile,datafiles and archivelog files to perform the duplicate operation.

An example for this kind of duplicate operation is:

RMAN> DUPLICATE DATABASE TO <auxiliary DB name>
                UNTIL TIME "TO_DATE('29-MAY-2010 14:16:42','DD-MON-YYYYHH24:MI:SS')"
                SPFILE
                 set control_files='D:\<aux path>\c1.ctl'
                 set db_file_name_convert='D:<target path>','D:<aux path>'
                 set log_file_name_convert='D:<target path>','D:<aux path>'
               BACKUP LOCATION 'D:\<location of backup on disk>';


# Here use of the BACKUP LOCATION clause identifies the type of duplication as having no target connection, no recovery catalog and being backup-based.

This is  a New Feature in 11gR2
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/wnbradv.htm#BRADV021
Oracle® Database Backup and Recovery User's Guide
11g Release 2 (11.2)
.
 What's New in Backup and Recovery?

SOLUTION


Process involved in this type of duplicate database

++ Rman restores the spfile from the backup pieces located in the mentioned location.

++ Once the spfile is restored, rman sets appropriate values to the parameters mentioned the duplicate command. For example:

db_name
control_files
db_file_name_convert
log_file_name_convert

etc ...

Once done, rman restarts the instance to no mount so that the changes can take effect.

++ Rman now changes the value of the parameter db_name to the target database name to achieve the restore of the controlfile. The restored controlfile will have the db_name as of the target database name and since we cannot have a different db_name in the spfile and in the controlfile, rman will have to set the parameter db_name to the target database name and perform the restore of the datafiles and controlfiles.

Also here if the duplicate is happening on the same machine, then 2 controlfiles with the same db_name cannot be mounted. In order to achieve this the auxiliary instance will have the parameter db_unique_name set to a unique value. Rman takes care of this sets the db_uniqiue_name to the database name specified for the auxiliary database.

++ After the above operation,controlfile is restored from the backup piece to the location provided for the parameter control_files.

++ Now Rman restores the datafiles to the locations specified by the parameter db_file_name_convert.

++ Recovery of the datafiles are performed.

++ Once the recovery is completed, rman shuts down the database to reset the value of db_name to the value provided for the auxiliary database.

++ Once this is done, the database is taken to no mount phase and the controlfile is recreated to change the database name and the id.This is followed by the database getting opened with resetlogs.


The steps involved in creating the duplicate database are as follows

1) Take a backup of the spfile, controlfile, database, and archivelogs as follows:

RMAN> backup spfile ;
RMAN> backup database include current controlfile plus archivelog ;

2) Making the backup pieces available for duplicate operation.

++ If the duplicate is going to happen on different server, move the backup pieces to a new server using commands like ftp,scp etc.
++ If the duplicate is going to happen on the same server as target, then you can either retain them in the same location where the backup was done or copy it to a required location.

3) Create a password file for the auxiliary instance.

For unix copy the password file from the target database to $ORACLE_HOME/dbs and rename it.
For windows copy the password file from the target database to %ORACLE_HOME/database and rename it.

4) Create a initialization parameter (initTEST.ora) for the auxiliary instance with only one parameter DB_NAME.

DB_NAME=dup11r2

5) Now start the auxiliary instance to no mount mode.

Unix Example
============
Just set the environment variables and start the instance.

% export ORACLE_SID=<auxiliary ORACLE_SID>
% export ORACLE_HOME=/<oracle_home path>
% export PATH=$ORACLE_HOME/bin:$PATH
% sqlplus "/as sysdba"
SQL > startup nomount



Windows Example
===============
Create a service and then set the necessary environment variables and start the instance.

% oradim -new -sid <auxiliary ORACLE_SID>
set ORACLE_SID=auxiliary ORACLE_SID>
set ORACLE_HOME=D:\<oracle home path>\
set PATH=D:\<oracle home>\bin;%PATH%
% sqlplus "/as sysdba"
SQL > startup nomount

6) Connect to the auxiliary instance from RMAN and perform the rman duplicate as follows:


EXAMPLE
=======
% rman auxiliary /

RMAN > DUPLICATE DATABASE TO dup11r2
UNTIL TIME "TO_DATE('29-MAY-2010 14:16:42','DD-MON-YYYY HH24:MI:SS')"
SPFILE
set control_files='D:\<auxiliary path>\c1.ctl'
set db_file_name_convert='D:\<target path>','d:\<auxiliary path>'
set log_file_name_convert='D:\<target path>','d:\<auxiliary path>'
BACKUP LOCATION 'D:\<location of backup on disk>'
;

Unix
======
run
{
duplicate database to "TEST"
UNTIL TIME "TO_DATE('21-JAN-2020 12:30:00','DD-MON-YYYY HH24:MI:SS')"
SPFILE
        set control_files='/u02/oradata/TEST/control01.ctl','/u02/oradata/TEST/control02.ctl','/u02/oradata/TEST/control03.ctl'
        set LOG_FILE_NAME_CONVERT='/u02/oradata/PROD/','/u02/oradata/TEST/'
        set db_file_name_convert='/u02/oradata/PROD/','/u02/oradata/TEST/'
        set LOCAL_LISTENER=''
        set audit_file_dest='/u01/app/oracle/admin/TEST/adump'
        set log_archive_dest_1='location=/u02/oradata/fast_recovery_area/TEST/archivelog'
        BACKUP LOCATION '/u02/rman/backup_files';
}



Friday, January 17, 2020


dbt-06103 the port 1521 is already in use



CHANGES

 New database install on OL 7.3. (running on an OVM virtual machine)

CAUSE

the hostname is not reachable:
[root@db122 ~]# ping db122
ping: db122: Name or service not known
From 12.2 DB installation guide:
You can install Oracle Database on a non-networked computer. If the computer, such as a laptop, is configured for DHCP and you plan to connect the computer to the network after the Oracle Database installation, then use the ping command on the computer on which you want to install the database to check if the computer can connect to itself. Perform this step by first using only the host name and then using the fully qualified name, which should be in the /etc/hosts file.

SOLUTION

Qualify the short hostname into the /etc/hosts:
i.e modify
X.X.X.X db122.<domain>
to:
X.X.X.X db122.<domain> db122


Thursday, January 16, 2020


[oracle@db ~]$ dbca
DISPLAY not set.
Set DISPLAY environment variable, then re-run.


[oracle@db dbhome_1]$ DISPLAY=:1.0 ; export DISPLAY
[oracle@db dbhome_1]$ xhost +
xhost:  unable to open display ":1.0"


[oracle@db ~]$ ssh -XY oracle@192.168.0.33

The authenticity of host '192.168.0.33 (192.168.0.33)' can't be established.
ECDSA key fingerprint is SHA256:DJqwvDyW3jgM6AvHwZcXdCMGH3rs+O7WqwnaPCLpw2E.
ECDSA key fingerprint is MD5:6f:1e:09:c1:3c:c6:b1:7c:ac:3b:0e:61:2b:21:77:36.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.33' (ECDSA) to the list of known hosts.
oracle@192.168.0.33's password: 
Last login: Fri Jan 17 12:10:39 2020 from db.lalteer.com

[oracle@db ~]$ dbca


















 RMAN Recovery Catalog About Recovery Catalog RMAN recovery catalog Is another database which is out of your normal databases or which is o...