Tales From A Lazy Fat DBA

$ prashantdixit/dbs90@ace as sysdba

  • Likes

    • 110,996
  • Archives

  • Categories

  • Cause I Support!!

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Oracle Radio

  • Magic Of Oracle

  • Disclaimer!

    FatDBA or Oracle 'Ant' is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘migration’

Cross Platform Migrations: ‘As Easy As Pie’ in Oracle 12c

Posted by FatDBA on August 22, 2016

The legendary Transportable Tablespace feature was introduced in Oracle 8i to make it convenient to transport a large amount of data between databases. Specially from Oracle10g this useful feature was enhanced with cross-platform support which allowed a tablespace/tablespaces, to be transported between databases deployed on different hardware platforms or between platforms with a different endian formats.

So till 11g the migration activity involves RMAN, EXPDP and IMPDP have to be used to transport tablespace(s) across platforms along with the RMAN CONVERT statement was used. Below are the steps that are required to perform the migration work till 11g.

Step 1: Check Platform Support and File Conversion Requirement
Step 2: Identify Tablespaces to be Transported and Verify Self-containment
Step 3: Check for Problematic Data Types
Step 4: Check for Missing Schemas and Duplicate Tablespace and Object Names
Step 5: Make Tablespaces Read-only in Source Database
Step 6: Extract Metadata from Source Database (We could use either data pump or original export to do this)
Step 7: Copy Files to Target Server and Convert if Necessary (Conversion involves RMAN)
Step 8: Import Metadata into Target Database (This step is sometimes called “plugging in” the tablespaces. Again we can use data pump or original import).
Step 9: Copy Additional Objects to Target Database as Desired

With the introduction of Oracle Database 12c, it includes a very easy and novel way to do the same – That is the ability to transport tablespaces across platforms using just RMAN and RMAN (compressed) backupsets!

Let me show you how we can transport a tablespace from Oracle Linux to Oracle Solaris. Which is an example of a cross platform migration with different ENDIAN formats. Solaris is BIG endian whereas the Linux is a small ENDIAN type OS.

SQL> select banner from v$version;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
PL/SQL Release 12.1.0.2.0 – Production

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– ————————————————– ————–
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little

SQL> SELECT tablespace_name, segment_type, COUNT(*),
2 SUM (bytes) / 1024 / 1024 mb
3 FROM dba_segments
4 WHERE owner = ‘DIXIT’
5 GROUP BY tablespace_name, segment_type
6 ORDER BY 1, 2 DESC;

TABLESPACE_NAME SEGMENT_TYPE COUNT(*) MB
————— ———— ———- ——-
IND1 INDEX 88 1353.4
TAB1 TABLE 41 4079.6
TAB1 LOBSEGMENT 3 0.4
TAB1 LOBINDEX 3 0.2
TAB1 INDEX 53 106.4

Make Tablespaces Read-only in Source Database
With today’s filers and sophisticated storage systems, it is often possible to take a filer “snapshot” or split a mirror in order to get a copy of the data files very quickly. Extracting metadata is also quick. So, on a system with a good storage system, tablespaces may only need to be read-only for a few minutes.
NOTE: In 12c we can use a procedure that keeps the downtime to a minimum with the ‘Incremental Cross-Platform Transportable Tablespaces’. It also uses RMAN transportable backupsets but is a slightly more complicated procedure.

We put the tablespaces into read-only mode with the following statements:

SQL> ALTER TABLESPACE tab1 READ ONLY;
Tablespace altered.

SQL> ALTER TABLESPACE ind1 READ ONLY;
Tablespace altered.

In order to create a TTS backup we have two of the optins available to use either BACKUP FOR TRANSPORT or the BACKUP TO PLATFORM RMAN

Whats the difference between the two ?
Answer: The difference between these two arguments or statements in RMAN is where the datafile conversion will take place. The BACKUP FOR TRANSPORT statement should be used if the datafile conversion is to be performed on the target system and the BACKUP TO PLATFORM statement should be used if the datafile conversion is to be performed on the source system.

For the test purposes we will perform the conversion of datafiles on the source using the BACKUP TO PLATFORM statement specifying the name of the target platform as its argument.
We have to provide some additional information like Where RMAN should place the backupsets. RMAN will create one backupset with the datafile(s) and a second backupset with the metadata dumpset that EXPDP will create in lieu of RMAN. For this test i will create a compressed transportable backupset.

$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Sun Aug 21 10:49:57 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
connected to target database: tunedb (DBID=1989879787)

RMAN> backup to platform ‘Solaris[tm] OE (64-bit)’ as compressed backupset
2> tablespace xtransport format ‘/tmp/dbfilebackups.bck’
3> datapump format ‘/tmp/infometaexpdp.bck’;
Starting backup at 21-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces…
EXPDP> Starting “SYS”.”TRANSPORT_EXP_TUNEDB_Y7OJ”:
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table “SYS”.”TRANSPORT_EXP_V121_pyAn” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TRANSPORT_EXP_TUNEDB_Y7OJ is:
EXPDP> /u01/app/oracle/product/12.1.0.2.0/db_1/dbs/backup_transporttbs_tunedb_181881.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace XTRANSPORT:
EXPDP> /u01/db/v121/data/V121/datafile/o1_mf_ttftest_dixit_.dbf
EXPDP> Job “SYS”.”TRANSPORT_EXP_TUNEDB_Y7OJ” successfully completed at Sun Aug 21 10:53:55 2016 elapsed 0 00:04:03
Export completed

channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/db/v121/data/V121/datafile/o1_mf_ttftest_dixit_.dbf
channel ORA_DISK_1: starting piece 1 at 21-AUG-16
channel ORA_DISK_1: finished piece 1 at 21-AUG-16
piece handle=/tmp/dbfilebackups.bck tag=TAG201698188T888 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed full datafile backup set
input Data Pump dump file=/u01/db/v121/data/V121/datafile/o1_mf_ttftest_dixit_.dbf
channel ORA_DISK_1: starting piece 1 at 21-AUG-16
channel ORA_DISK_1: finished piece 1 at 21-AUG-16
piece handle=/tmp/infometaexpdp.bck tag=TAG201678777U998 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 21-AUG-16

Recovery Manager complete.

So in short the RMAN has performed below mentioned activities:
– Verify and Identify Tablespaces to be Transported and Verify Self-containment
– Extract Metadata from Source Database using EXPDP.
– RMAN created a compressed backupset which contains the tablespace’s datafile.
– Created a backupset containing the metadata dump.

Now its time to restore the transportable backupset!!

$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Sun Aug 21 12:39:13 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
connected to target database: tunedb (DBID=1989879787)

RMAN> restore from platform ‘Solaris[tm] OE (64-bit)’
2> foreign tablespace IND1, TAB1 to new
3> from backupset ‘/tmp/dbfilebackups.bck’
4> dump file from backupset ‘/tmp/infometaexpdp.bck’;
Starting restore at 21-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring all files in foreign tablespace IND1, TAB1
channel ORA_DISK_1: reading from backup piece /tmp/dbfilebackups.bck
channel ORA_DISK_1: restoring foreign file 9 to /u01/db/tunedb/data/tunedb/datafile/o1_mf_testtransport_ab77hho11_.dbf
channel ORA_DISK_1: foreign piece handle=/tmp/dbfilebackups.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring Data Pump dump file to /u01/app/oracle/product/12.1.0.2.0/db/dbs/o1_mf_ttftest_dixit_.dmp
channel ORA_DISK_1: reading from backup piece /tmp/infometaexpdp.bck
channel ORA_DISK_1: foreign piece handle=/tmp/infometaexpdp.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Performing import of metadata…
IMPDP> Master table “SYS”.”TRANSDIXIT_IMPORT_tunedb_g7aahu” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TRANSDIXIT_IMPORT_tunedb_g7aahu”:
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job “SYS”.”TRANSDIXIT_IMPORT_tunedb_g7aahu” successfully completed at Sun Aug 21 12:42:10 2016 elapsed 0 00:02:03
Import completed

Finished restore at 21-AUG-16

Recovery Manager complete.

Deducing on the basis of RMAN restore logs, its clear that the RMAN completed following steps:
– It restored the foreign tablespace’s datafile from the datafile backupset.
– Along it restores the tablespace metadata from the metadata backupset.
– Import the tablespace metadata using IMPDP.

Hope That Helps
Prashant Dixit

Advertisements

Posted in Advanced | Tagged: , | Leave a Comment »

Database Upgrade (Oracle 10R2 or 11.2.0.1 to Oracle 11gR2(11.2.0.2/3)

Posted by FatDBA on April 19, 2013

Describing pre/post checklist and upgrade process details for upgrading a Oracle 10R2 or 11.2.0.1 to Oracle 11gR2(11.2.0.2/3)

Please check OS version as upgrade is only certified for RH 5.

cat /etc/red*
Red Hat Enterprise Linux Server release 5.4 (Tikanga)

**For 11.2.0.2.3, ensure RHEL is upgraded to 5.5 and above.

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)

**For 11.2.0.2.3, ensure ASM is upgraded to 11.2.0.3.  (Check dir $ORA_CRS_HOME then cd ..  then pwd)

Please ensure that 11.2.0.3.x86_64 is pushed on the box and tnsnames.ora and listener.ora are updated accordingly.

Verify all ET standards
* Index Placement
* DB User profile assignment
Need to install Oracle 11g using runinstaller.

Phase 1: PRE UPGRADE TASKS
Phase 2: UPGRADING THE DATABASE TO ORACLE 11G R2
Phase 3: POST UPGRADE TASKS.

Phase 1 : Pre upgrade tasks

Step 1 : Create the directory
mkdir  preupgrade_bkup

Step 2 : Disable all the below jobs :
– All batch and cron jobs.
– Backup
– Archive removal and rotation
– Application jobs
– Stats collection job
– standby job at standby server and make sure all arch has been applied at DR/STANDBY server side.
Step 3 : Run the gather stats job

a) Gathering System Stats
exec dbms_stats.gather_schema_stats(‘SYS’);

b) Gathering Dictionary Stats
exec dbms_stats.gather_dictionary_stats;

c) Gathering all fixed objects stats (dynamic performance tables)
exec dbms_stats.gather_fixed_objects_stats;

d) Gathering Database Stats
exec dbms_stats.gather_database_stats(options => ‘GATHER AUTO’);

e) Run the stats for all the Application schemas :
exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’OWNER’, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>DBMS_STATS.AUTO_DEGREE, CASCADE=>TRUE, OPTIONS=>’GATHER STALE’);

exec  DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’OWNER’, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>DBMS_STATS.AUTO_DEGREE, CASCADE=>TRUE);

Step 4 : Capture Database link information :

spool create_dblink.info

SELECT
‘create ‘||DECODE(U.NAME,’PUBLIC’,’public ‘)||’database link ‘||CHR(10)
||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.’)|| L.NAME||chr(10)
||’connect to ‘ || L.USERID || ‘ identified by
||L.PASSWORD|| using  || L.host || ‘
||chr(10)||’;’ TEXT

FROM sys.link$ L, sys.user$ U WHERE L.OWNER# = U.USER# ;

spool off

Step 5 : Create Pre-Upgrade BackupSET.
Run the database backup, be it RMAN (full not incremental) or hot backup whatever is configured in the environment.
Step 6: Verify Disk free space for archive mount point, we should have 15-20 gigs of space for arch (command to verify the space : df –h .)
Step 7 : Ensure no files need media recovery:

SQL>  select * from v$recover_file;
no rows selected

*** Make sure currently no backup is running for the database, if yes then end backup using command.

SQL > Alter database end backup;
Database altered.

Step 7 : Verify DB version and installed options :

Ensure the properties for SYSTEM ,SYS user and database component status should be valid and default tablespace should be SYSTEM and SYSAUX respectively.

SQL> select username, default_tablespace from dba_users where username in (‘SYS’,’SYSTEM’);

=============================================================================================
SYS                            SYSTEM
SYSTEM                         SYSTEM

2 rows selected.

Database Components Information
select COMP_NAME, VERSION, status from dba_registry;

Database Patches Information
select ACTION_TIME, NAMESPACE, VERSION, COMMENTS, BUNDLE_SERIES from sys.registry$history Order by ACTION_TIME;

Database DST Information

col VERSION for 999
SELECT * FROM v$timezone_file;

Step 8: Purge the recyclebin :
PURGE DBA_RECYCLEBIN;

Step 9: Recompile invalid objects:
SQL>@?/rdbms/admin/utlrp.sql
Step 10 : Spool file before running upgrade tool :

SQL> spool pre_upgrade_database_details.log
SQL> @? /rdbms/admin/utlu112i.sql
SQL> spool off

Step 10: Stop the agent and listener :

emctl stop agent
lsnrctl stop <listener-name>

Step 11: Bring down the DB :
SQL>shutdown immediate;

Keeping in mind if the DB is an RAC change the CLUSTER_DATABASE=false
SQL> alter system set cluster_database = false scope = spfile;

Phase 2: Upgrading to Oracle Database 11g Release 2

[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance

Step 1 : Create pfile (change compatible to 11.2.0.3) and start the instance using new pfile :
SQL > startup nomount;

Step 2: Create passwordfile
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password={password} entries =80 force=y

Step 3 :  Upgrade the DB, upgrd.sql will take around 30 minutes to complete
SQL> spool $ET_INSTANCE_ROOT/bkup/upgrade/upgrade_to_11203.log
SQL> startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql

SQL> startup

*** Can check the spool file for any errors …

Phase III: Post-Install Oracle Database 11gR2

Step 1: Verify Upgrade Status:

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

Oracle Database 11.2 Post-Upgrade Status Tool 01-28-2011 10:45:32 .
Component Status Version HH:MM:SS .
Oracle Server . VALID 11.2.0.3.0 00:10:43
Gathering Statistics . 00:00:40 Total Upgrade Time: 00:12:03

PL/SQL procedure successfully completed.

Steps 2: Need to verify the components after the upgrade :

SQL >select COMP_NAME, VERSION, status from dba_registry;
SQL>select ACTION_TIME, NAMESPACE, VERSION, COMMENTS, BUNDLE_SERIES from sys.registry$history Order by ACTION_TIME;
SQL>SELECT * FROM v$timezone_file;

Step 3: Need to upgrade the timezone to 14

SQL>conn / as sysdba
SQL>shutdown immediate;
SQL>startup upgrade;

SQL> purge dba_recyclebin;
DBA Recyclebin purged.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> col value format a10
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’  ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
—————————— ———-
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       7
DST_UPGRADE_STATE              Upgrade

3 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where    UPGRADE_IN_PROGRESS=’YES’;
no rows selected

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE —————————— ———- DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE 3 rows selected.

SQL> select * from v$timezone_file;

FILENAME                VERSION
——————– ———-
timezlrg_14.dat              14

Step 4: Verify invalid objects and compile them :
SQL>@?/rdbms/admin/utlrp.sql

Step 4: Start the database, agent, listner cleanly:

SQL> shutdown immediate
SQL> startup mount
SQL> alter system set cluster_database=TRUE scope=spfile; <— Only required for RAC

SQL> startup (If RAC, do it on both nodes)
lsnrctl start <listener-name>

Step 5: Run the gather statistics job again :
Step 6: Enable all the jobs which we commented before upgrade activity
Step 7 : Verify the database parameters.

Posted in Advanced | Tagged: , | Leave a Comment »

Database Migration vs Upgrade ?

Posted by FatDBA on February 25, 2013

While talking with one of the new DBA found him uncomfortable & perplexed when started discussing Database Migration and Upgrade activities. Well i’ve seen that there are so many confusions revolves in any DBA’s mind who is very new to the Administration.

Alright, so today i would like to distinguish between the two activities which could be easily considered as one of the major activity of any DBA.

Okay.

Terms like Migration and Upgrade are used to convert an existing database to a new software release. One of the major difference that i’ve discovered is,

In case of Upgrade, you shut down the database, start it up with the new software release, and then execute provided SQL and PL/SQL scripts to update Oracle’s internal dictionary tables and other objects. Oracle also provides ‘downgrade’ scripts which can be run to revert back all changes that happened to the database during the Upgrade process.
In broader terms Upgrade is a new release for existing system.

Example: DB Version change from Oracle 7.3.3 to Oracle 7.4.4 is an Upgrade example. e.g. 8.1.6->8.1.7 or 8.1.7.0->8.1.7.4.

Migration:
In some cases Migration may describe the process of moving data from a non-Oracle database into an Oracle database. A Migration is generally displacing a database from —

* From one destionation/location to new one.
* One RDBMS to another (Hetrogenous), example: Sybase to Oracle.
* One major release to another (Homogenous), example: Oracle 9i to Oracle 10g.
Example: Going from Oracle 9i to Oracle 10g is a Migration example. e.g. 7->8, 8->9.

Posted in Advanced | Tagged: , | 1 Comment »

Database Version Details

Posted by FatDBA on July 14, 2012

Below provided image is descriptive enough to explain the details of a Database Version.

Notes: Texts on the top of the number defined the name of the process/activity and below are the standard names. Last number in the range is shands for “Platform Specific Release Number” eg. the last zero out of 10.2.0.1.0

Posted in Basics | Tagged: , | 2 Comments »

‘ORA-12547: TNS:lost contact’ after OPATCH_PLATFORM_ID Reset in sqlplus.

Posted by FatDBA on July 5, 2012

Resolution:

1. De-install the deployed patch first  —

[oracle@prashant 8974084]$ opatch rollback -id 8974084   (Click Y/Yes when it asked you to select out of Yes/No).

Once the patch uninstalled unset the OPATCH_PLATFORM_ID

example:     [oracle@prashant 8974084]$ unset OPATCH_PLATFORM_ID

2. Stop all middle wares included e.g. ENTERPRISE MANAGER, LISTENER and shutdown the database.

3. Revert back the PLATFORM_ID back to it’s old value —

[oracle@prashant 8974084]$ export OPATCH_PLATFORM_ID=46   (46 was the old value of the machine)

4. Recheck all Network files located in $ORACLE_HOME/dbs     like listener.ora, tnsnames.ora and tnsnames.ora they might corrupted as well. Make sure you have the correct Host Name, Post Number, Service number defined in  tnsnames.ora. Check sqlnet,ora and verify if it has the EZCONNECT , TNSNAMES.

5. reboot your machine.

6.  Start listener and to give the database a try to start. Most probably  chances are there that you’ll receive LOCAL_LISTENER definition failure message during the start.

In that case you have to check your parameter file and perform requisite changes to the local listener definitions (*.LOCAL_LISTENER=’ ‘)

7. Re try to start the database and you’ll be able to start DB this time.

Feedbacks: If failed to start the DB box even after performing these steps and that will also motivate me to write about and research  🙂

Posted in Advanced | Tagged: , , | Leave a Comment »

OraDose… (05-07-2012)

Posted by FatDBA on July 5, 2012

If getting error regarding Platform ID during prerequisite checks when applying any patch on database.

Below is the error code:

[oracle@prashant 8974084]$ opatch apply
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-07-05_12-07-37PM.log

Patch history file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch ‘8974084’ to OH ‘/u01/app/oracle/product/11.2.0/db_1’

Running prerequisite checks…
Prerequisite check “CheckPatchApplicableOnCurrentPlatform” failed.
The details are:
Patch ( 8974084 ) is not applicable on current platform.
Platform ID needed is : 46
Platform IDs supported by patch are: 226
ApplySession failed during prerequisite checks: Prerequisite check “CheckPatchApplicableOnCurrentPlatform” failed.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 74

Resolution:

Reset Platform ID using OPATCH_PLATFORM_ID to desired ID.

(Note: Changing Platform ID sometimes affects machines networks and you might loose connections – TNS Error – No Connection

Please perform said steps on your own risk)

example: [oracle@prashant 8974084]$ export OPATCH_PLATFORM_ID=226

Let’s re-run the patch after we are done with the ID resets —

[oracle@prashant 8974084]$ opatch apply
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-07-05_12-19-49PM.log

Patch history file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch ‘8974084’ to OH ‘/u01/app/oracle/product/11.2.0/db_1’

Running prerequisite checks…

Using user-defined value, OPatch will treat this system as platform ID ‘226’.

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.2.0/db_1’)

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch ‘8974084’ for restore. This might take a while…
Backing up files affected by the patch ‘8974084’ for rollback. This might take a while…

Patching component oracle.rdbms, 11.2.0.1.0…
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbo.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbs.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbz.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcl.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbi.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbr.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjbl.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjbm.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjbr.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjfc.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kmgs.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kmgsb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/ksu.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libgeneric11.a”  with “lib/libgeneric11.a/skgvm.o”
Running make for target ioracle
ApplySession adding interim patch ‘8974084’ to inventory

Verifying the update…
Inventory check OK: Patch ID 8974084 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8974084 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.

—— WE ARE DONE ——

Posted in Advanced | Tagged: , , | Leave a Comment »

 
%d bloggers like this: