AMM in Oracle 11g

Below to post is to verify my understanding on how shared memory is managed in AMM. Thanks to Tanel poder Post on AMM internals.

Current setting for MEMORY_TARGET parameters in DB, not all 50GB allocated

SQL> sho parameter memory

NAME TYPE VALUE
———————————— ——————————– —–
hi_shared_memory_address integer 0
memory_max_target big integer 50G
memory_target big integer 50G
shared_memory_address integer 0

SGA parameters set in DB

SQL> sho parameter sga

NAME TYPE VALUE
————————-
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 50G
sga_target big integer 20G

In AMM , oracle manages shared memory as files in shared memory filesystem (/dev/shm)

cd /dev/shm

$ ls -ltr *TEST* |more
-rw-r—– 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131235851_0
-rw-r—– 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131203082_369
Each files is of size 128M

ls -ltr *TEST* | awk ‘$5==134217728’ |wc -l
170

170*128M=21.25 GB

21.25GB shared memory currently allocated to database TEST, these are memory files mapped to server process (size 0 bytes), but its not used, if space needed for pga , these files are freed and memory allocated to pga.

$ ls -ltr *TEST* | awk ‘$5==0’ |more
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_230
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_229
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_228
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_227
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_226
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_225

No of empty files which can freed whenever memory needs to be allocated to pga.

$ ls -ltr *TEST* | awk ‘$5==0’ |wc -l
231

231*128M=28.8GB  ( MEMORY_TARGET-Current SGA Size)

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
——————– ———— ———- ———- ——————- ———- ————- ——— ——— ————
SGA Target 2.2683E+10 2.2012E+10 2.2683E+10 2.1475E+10 5 GROW DEFERRED 02-MAY-16 134217728
SQL> select to_char(2.2683E+10)/1024/1024/1024 from dual;

TO_CHAR(22683000000)/1024/1024/1024
———————————–
21.12519

sga_target current size is 21Gb which matches to the size of all shared memory files created for the DB in /dev/shm.
If /dev/shm not defined or properly configured, then setting AMM at instance will throw error message ORA-00845: MEMORY_TARGET not supported on this system. ASMM can be configured without /dev/shm.

Ref: http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/

 

RMAN Block Recovery ,Skip Corrupted Block

 

Create table under tablespace test2 and check the block# for the first few rows.

SQL> create table oratab.objects2 tablespace test2 as select * from dba_objects;

Table created.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid) from oratab.objects2) where rownum < 7;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
131
132
133
134
135

SQL> select count(*) from oratab.objects2;

COUNT(*)
———-
18520

No corrupted blocks in the tablespace.

SQL> select * from v$database_block_corruption;

no rows selected

Manually corrupt one of the block containing data.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=133 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.0526e-05 s, 162 MB/s

Flush buffer cache as it may have all blocks currently in memory.

SQL> alter system flush buffer_cache;

System altered.

Query the table, it will through error message with the block# of corrupted block.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 133)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

The corrupted block details will be reflected in v$database_block_corruption

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 133 1 0 ALL ZERO

Recover block using RMAN

RMAN> blockrecover datafile 7 block 133;

Starting recover at 21-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Corrupt multiple blocks now.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=134 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.6443e-05 s, 145 MB/s
$ dd of=/u02/oradata/test201.dbf bs=8192 seek=135 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.2078e-05 s, 157 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 134)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

Use RMAN validate to check the corrupted block list.

RMAN> VALIDATE CHECK LOGICAL TABLESPACE TEST2;

Starting validate at 21-APR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u02/oradata/test201.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
7 FAILED 0 12410 12800 30691648
File Name: /u02/oradata/test201.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 247
Index 0 0
Other 2 143

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 134 2 0 ALL ZERO
When there are multiple corrupted blocks, use blockrecover corruption list to recover them.

RMAN> blockrecover corruption list;

Starting recover at 21-APR-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Skipping Corrupted Block using event ‘10231’  or DBMS_REPAIR if it can’t be recovered.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.9682e-05 s, 165 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 131 1 0 ALL ZERO

SQL> ALTER SESSION SET EVENTS
‘10231 TRACE NAME CONTEXT FOREVER, LEVEL 10’; 2

Session altered.

SQL> create table oratab.objects2_recover tablespace test2 as select * from oratab.objects;

Table created.

SQL> SQL> select count(*) from oratab.objects2_recover;

COUNT(*)
———-
18518 — 2 rows decreased.

Corrupted block can be skipped using DBMS_REPAIR package as well

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′);

PL/SQL procedure successfully completed.

SQL> select table_name,skip_corrupt from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME SKIP_CORRUPT
—————————— ——–
OBJECTS2 ENABLED

Skip corrupt flag can be cleared after exporting the table or using CTAS

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′,flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

SQL> select table_name,SKIP_CORRUPT from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME                               SKIP_CORRUPT
—————————— ——–
OBJECTS2                                             DISABLED

 

ref:Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231 (Doc ID 33405.1)

 

ORA-1017 and ORA-16047 in Dataguard

Issue:

Primary not able to connect to standby database, no RFS process started on standby database.

Alert log on primary has ORA 1017 errors and trace file has ORA-16047

ORA-16047: DGID mismatch between destination setting and target database

Troubleshooting:

Recreated the password file on primary and copied to standby database, but still primary LNS not able to communicate to RFS on standby

select process, status from v$managed_standby;  — RFS not started on DR database

I am able to connect to standby database using sqlplus with the recreated password file.

Solution:

LOG_ARCHIVE_CONFIG parameter is missing from standby database , though password file is fine , primary not able to authenticate the DR DB unique_name as the LOG_ARCHIVE_CONFIG is missing.

alter system set log_archive_config=’DG_CONFIG=(primary,DR)’ scope=both

Above parameter setting resolved the issue.

 

 

 

 

 

Convert Non-CDB to PDB

1) Shut down the non CDB database and startup in read only mode

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 905969768 bytes
Database Buffers 155189248 bytes
Redo Buffers 9650176 bytes
Database mounted.
Database opened.

2) Describe the Non-CDB using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’);
END;
/ 2 3 4 5

PL/SQL procedure successfully completed.

3) Shutdown the non-CDB database

SHUTDOWN IMMEDIATE;SQL>
Database closed.
Database dismounted.
ORACLE instance shut down.

4) Need to check the compatibility of Non-CDB to be converted to Pluggable DB So, connect to the container DB (CDB) and run the following

SQL>
SET SERVEROUTPUT ON
SQL> SQL> DECLARE
2 compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
3 pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’,
4 pdb_name => ‘TESTPDB2’)
5 WHEN TRUE THEN ‘YES’
6 ELSE ‘NO’
END;
7 8 BEGIN
9 DBMS_OUTPUT.PUT_LINE(compatible);
10 END;
/ 11
YES

PL/SQL procedure successfully completed.

5) Create Pluggable database from the XML file, here we are using the same datafiles that were there earlier for the Non CDB DB.

SQL> CREATE PLUGGABLE DATABASE TESTPDB2 using ‘/apps/oracle/home/TEST12c.xml’ nocopy tempfile reuse;

Pluggable database created.

6) Check the status of the created PDB

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

CON_ID NAME OPEN_MODE
———- —————————— ———-
2 PDB$SEED READ ONLY
3 TESTPDB READ WRITE
4 TESTPDB2 MOUNTED
SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NEW
SQL> alter session set container=TESTPDB2;

Session altered.

SQL> show con_name;

CON_NAME
——————————
TESTPDB2

7) Execute the script to update metadata

SQL> !echo $ORACLE_HOME
/apps/oracle/product/12.1.0

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

SQL>
SQL>
SQL> WHENEVER SQLERROR CONTINUE;

8) Open the new PDB database

SQL> alter session set container=TESTPDB2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> alter session SET CONTAINER =CDB$ROOT;

Session altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TESTPDB READ WRITE
TESTPDB2 READ WRITE

3 rows selected.

SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NORMAL

3 rows selected.

RMAN Convert database from Linux to Windows

Check Endian format for the source and target OS versions:

SQL> select * from v$db_transportable_platform;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– ————————————————–
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
5 HP Tru64 UNIX Little
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
13 Linux x86 64-bit Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little

12 rows selected.

SQL> select platform_name from v$database;

PLATFORM_NAME
————————————————————-
Linux x86 64-bit

Preparing to Convert database:

SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
begin
3 4 /* db_ready is ignored, but with SERVEROUTPUT set to ON any
5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db(‘Microsoft Windows IA (32-bit)’,
7 dbms_tdb.skip_none);
8 end;
9 /
Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and
retry.

PL/SQL procedure successfully completed.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> set serveroutput on
SQL> declare
db_ready boolean;
2 3 begin
/* db_ready is ignored, but with SERVEROUTPUT set to ON any
4 5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db(‘Microsoft Windows IA (32-bit)’,
7 dbms_tdb.skip_none);
8 end;
9 /

PL/SQL procedure successfully completed.
Using DBMS_TDB .CHECK_EXTERNAL to Identify External Objects:
SQL> declare
external boolean;
begin
/* value of external is ignored, but with SERVEROUTPUT set to ON
* dbms_tdb.check_external displays report of external objects
* on console */
external := dbms_tdb.check_external;
end; 2 3 4 5 6 7 8
9 /
The following directories exist in the database:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.EXPDP

PL/SQL procedure successfully completed.

RMAN convert from Source platform:

RMAN> CONVERT DATABASE NEW DATABASE ‘PDMTMP2’
2> transport script ‘/ofaroot/MISC/transportscript’
3> to platform ‘Microsoft Windows IA (32-bit)’
db_file_name_convert ‘/oradata/’ ‘/ofaroot/MISC/’
;4> 5>

Starting conversion at source at 23-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.EXPDP found in the database
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/oradata/users01.dbf
converted datafile=/ofaroot/MISC/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:15
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/oradata/system.dbf
converted datafile=/ofaroot/MISC/system.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/oradata/sysaux.dbf
converted datafile=/ofaroot/MISC/sysaux.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/oradata/undotbs1.dbf
converted datafile=/ofaroot/MISC/undotbs1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Edit init.ora file /apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /ofaroot/MISC/transportscript on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-DEC-15
Transport script output:

[oracle@VM1]$ cat transportscript
— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
CREATE CONTROLFILE REUSE SET DATABASE “PDMTMP2” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-778_T-1_A-847714242_03qpgua4’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-779_T-1_A-847714242_04qpgua4’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-777_T-1_A-847714242_05qpgua4’ SIZE 100M BLOCKSIZE 512
DATAFILE
‘/ofaroot/MISC/system.dbf’,
‘/ofaroot/MISC/sysaux.dbf’,
‘/ofaroot/MISC/undotbs1.dbf’,
‘/ofaroot/MISC/users01.dbf’
CHARACTER SET AL32UTF8
;

— Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/apps/oracle/product/11.2.0.3/dbs/data_D-PDMTMP2_I-2935319874_TS-TEMP_FNO-1_06qpgua4′
SIZE 524288000 AUTOEXTEND OFF;
— End of tempfile additions.

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
— The following step will recompile all PL/SQL modules.
— It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
RMAN convert on TARGET Platform:

RMAN> CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT ‘/ofaroot/MISC/convertscript.rman’
2> 3> TRANSPORT SCRIPT ‘/ofaroot/MISC/transportscripttarget.sql’
4> new database ‘PDMTMP1’
5> FORMAT ‘/ofaroot/MISC/%U’;

Starting conversion at source at 23-DEC-15
using channel ORA_DISK_1

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.EXPDP found in the database
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00004 name=/oradata/users01.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00001 name=/oradata/system.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00002 name=/oradata/sysaux.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00003 name=/oradata/undotbs1.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
Edit init.ora file /ofaroot/MISC/init_00qpgv1f_1_0.ora. This PFILE will be used to create the database on the target platform
Run RMAN script /ofaroot/MISC/convertscript.rman on target platform to convert datafiles
Run SQL script /ofaroot/MISC/transportscripttarget.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-DEC-15
[oracle@VM1]$ cat convertscript.rman
STARTUP NOMOUNT PFILE = ‘/ofaroot/MISC/init_00qpgv1f_1_0.ora’;
RUN {
CONVERT
FROM PLATFORM ‘Linux x86 64-bit’
PARALLELISM 1
DATAFILE ‘/oradata/users01.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-USERS_FNO-4_05qpgv1f’
DATAFILE ‘/oradata/system.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-SYSTEM_FNO-1_06qpgv1f’
DATAFILE ‘/oradata/sysaux.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-SYSAUX_FNO-2_07qpgv1f’
DATAFILE ‘/oradata/undotbs1.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-UNDOTBS1_FNO-3_08qpgv1f’
; }

Ref: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/dbxptrn.htm#CHDEICED

 

ORA- 28040: No matching Authentication protocol after 12c Upgrade

Issue:

Web application was not working, when connecting through ODBC application team is getting error ORA-28040 .

What Was changed:

Oracle database upgraded from oracle 11.2.0.2 to 12.1.0.2

Resolution/Workaround:

SQLNET.ALLOWED_LOGON_VERSION default is changed to 11 from 8 in earlier versions.  We need to modify the parameter to value 9 or less. SQLNET.ALLOWED_LOGON_VERSION is depreciated in 12c, SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT replaced this parameter.

Ref: https://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF2016