Wednesday, 21 June 2017

NFO: Read: Unable to connect to local ASM Instance. The following error occured: ORA-12705: Cannot access NLS data files or invalid environment specified

NFO: Read: Unable to connect to local ASM Instance. The following error occured: ORA-12705: Cannot access NLS data files or invalid environment specified

Check the environment variable is showing correct path:

echo $ORA_NLS10

It should show $CRS_HOME/nls/data , if its showing another path you will get this error.

export correct path as below:

export ORA_NLS10=$CRS_HOME/nls/data

if you need to set permanently, we can set correct path in .bash_profile


Monday, 12 June 2017

How to Reinstall Oracle Grid Infrastructure Without Disturbing RDBMS Installation (Doc ID 1276975.1)

How to Reinstall Oracle Grid Infrastructure Without Disturbing RDBMS Installation (Doc ID 1276975.1)

Friday, 9 June 2017

GG integrated method unidirectional example

                                                            GG integrated method example
**********************************************************************************
Set the following parameters in DB pfile/spfile on both source and target;

alter system set enable_goldengate_replication=TRUE scope=both;
alter system set recyclebin=off scope=spfile;



If not put the database in archivelog mode.
shutdown immediate;

startup mount

alter database archivelog;

alter database open;

Enable force logging and supplemental logging in source

alter database force logging;

alter database add supplemental log data;


The force logging mode captures transactions even if nologging is specified for some operations by the end users or applications. The supplemantal log data is necessary so that GoldenGate is able to extract committed transactions out of the redo logs.

The next step is to create a GoldenGate admin user both databases: source and target


Create goldengate user on both source and target side:

src:

SQL> create user goldengate identified by oracle;

User created.

SQL> grant dba to goldengate;

Grant succeeded.

trg:

SQL> create user goldengate identified by oracle;

User created.

SQL> grant dba to goldengate;

Grant succeeded.


create a wallet and store the credentials

GGSCI (localhost.localdomain) 1> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (localhost.localdomain) 2> add credentialstore

Credential store created in ./dircrd/.

GGSCI (localhost.localdomain) 3> alter credentialstore add user goldengate@src password oracle alias gg_user

Credential store in ./dircrd/ altered.

GGSCI (localhost.localdomain) 5> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: gg_user
  Userid: goldengate@src


Create the wallet on target side also like above.

Add supplemental log data to the tables we want to replicate

GGSCI (localhost.localdomain) 2> dblogin userid goldengate@src
Password:
Successfully logged into database.

GGSCI (localhost.localdomain as goldengate@src) 3> add trandata goldengate.srtest

2017-06-08 04:08:33  WARNING OGG-06439  No unique key is defined for table SRTEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table GOLDENGATE.SRTEST.
TRANDATA for scheduling columns has been added on table 'GOLDENGATE.SRTEST'.
TRANDATA for instantiation CSN has been added on table 'GOLDENGATE.SRTEST'.


Before we do any additional configuration make sure the GoldenGate manager is started on both hosts:

SRC

GGSCI (localhost.localdomain as goldengate@src) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

TGR


GGSCI (localhost.localdomain as goldengate@src) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


Now that the managers are configured we can continue with configuring the capture/extract process:

GGSCI (localhost.localdomain) 7> add extract extint,integrated tranlog,begin now
EXTRACT (Integrated) added.

GGSCI (localhost.localdomain) 8> add exttrail /gg/12c/src/dirdat/itrail/in,extract extint
EXTTRAIL added.

GGSCI (localhost.localdomain) 1> edit params extint

extract extint
useridalias gg_user
exttrail /gg/12c/src/dirdat/itrail/in
table goldengate.srtest;


For the integrated capture mode to work we now must register the capture process with the database:

GGSCI (localhost.localdomain) 9> dblogin useridalias gg_user
Successfully logged into database.

GGSCI (localhost.localdomain as goldengate@src) 10> register extract extint database

2017-06-08 04:59:46  INFO    OGG-02003  Extract EXTINT successfully registered with database at SCN 1658828.

Pump process
GGSCI (localhost.localdomain as goldengate@src) 11> edit params pump

extract pump
useridalias gg_user
rmthost 192.168.92.130,mgrport 7810
rmttrail /gg/12c/trg/dirdat/rtrail/rt
passthru
table goldengate.srtest;

GGSCI (localhost.localdomain as goldengate@src) 12> add extract pump,exttrailsource /gg/12c/src/dirdat/itrail/in begin now
EXTRACT added.
GGSCI (localhost.localdomain as goldengate@src) 14> add rmttrail /gg/12c/trg/dirdat/rtail/rt extract pump
RMTTRAIL added.
Now  we can start the extract:
GGSCI (localhost.localdomain) 2> start extract extint
Sending START request to MANAGER ...
EXTRACT EXTINT starting
GGSCI (localhost.localdomain) 12> start pump

Sending START request to MANAGER ...
EXTRACT PUMP starting


GGSCI (localhost.localdomain) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTINT      00:00:10      00:00:08
EXTRACT     RUNNING     PUMP        00:12:44      00:00:04

replicat process must be configured on the the target system:
GGSCI (localhost.localdomain as goldengate@src) 6> edit params replic

replicat replic
useridalias ggtrg_user
ASSUMETARGETDEFS
MAP goldengate.srtest,TARGET goldengate.trtest;

As with the capture process on the source database the apply process on the target needs to get registered with the database:

GGSCI (localhost.localdomain as goldengate@src) 7> dblogin useridalias gg_user
 Successfully logged into database.
GGSCI (localhost.localdomain as goldengate@src) 8> add replicat replic integrated exttrail /gg/12c/trg/dirdat/rtrail/rt
REPLICAT (Integrated) added.
GGSCI (localhost.localdomain as goldengate@trg) 12>  register replicat replic database;
2017-06-08 08:50:17  INFO    OGG-02528  REPLICAT REPLIC successfully registered with database as inbound server OGG$REPLIC.
GGSCI (localhost.localdomain as goldengate@src) 9> start replicat replic

Sending START request to MANAGER ...
REPLICAT REPLIC starting


GGSCI (localhost.localdomain as goldengate@src) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STARTING    REPLIC      00:00:00      00:01:27






Monday, 5 June 2017

oracle database stand alone Upgradation from 11.2.0.4 to 12.1.0.2

                                              Upgradation from 11.2.0.4 to 12.1.0.2
*************************************************************************************
First we need to download the 12.1.0.2 RDBMS software from metalink and install in seperate home.
Run the new preupgrade check script preupgrd.sql, in  11.2.0.4 environment – it will generate 3 files:
SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in ORCL...
***************************************************************************
    ************************************************************
                   ====>> ERRORS FOUND for ORCL <<====
 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.
           You MUST resolve the above errors prior to upgrade
      ************************************************************
      ************************************************************
               ====>> PRE-UPGRADE RESULTS for ORCL <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
 /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
 /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
 /u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
      ************************************************************
***************************************************************************
Pre-Upgrade Checks in ORCL Completed.
***************************************************************************
***************************************************************************
***************************************************************************
Verify the preupgrade.log and make necessary changes
$ cd  /u01/app/oracle/cfgtoollogs/UPGR/preupgrade/preupgrade.log
[oracle@localhost preupgrade]$ cat preupgrade.log
Oracle Database Pre-Upgrade Information Tool 02-08-2017 06:22:36
Script Version: 12.1.0.2.0 Build: 006
**********************************************************************
   Database Name:  ORCL
  Container Name:  Not Applicable in Pre-12.1 database
    Container ID:  Not Applicable in Pre-12.1 database
         Version:  11.2.0.4.0
      Compatible:  11.2.0.4.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V14
**********************************************************************
                           [Update parameters]
         [Update Oracle Database 11.2.0.4.0 init.ora or spfile]
--> If Target Oracle is 32-bit, refer here for Update Parameters:
WARNING: --> "processes" needs to be increased to at least 300
--> If Target Oracle is 64-bit, refer here for Update Parameters:
WARNING: --> "processes" needs to be increased to at least 300
**********************************************************************
**********************************************************************
                          [Renamed Parameters]
                     [No Renamed Parameters in use]
**********************************************************************
**********************************************************************
                    [Obsolete/Deprecated Parameters]
             [No Obsolete or Desupported Parameters in use]
**********************************************************************
                            [Component List]
**********************************************************************
--> Oracle Catalog Views                   [upgrade]  VALID
--> Oracle Packages and Types              [upgrade]  VALID
--> JServer JAVA Virtual Machine           [upgrade]  VALID
--> Oracle XDK for Java                    [upgrade]  VALID
--> Oracle Workspace Manager               [upgrade]  VALID
--> OLAP Analytic Workspace                [upgrade]  VALID
--> Oracle Enterprise Manager Repository   [upgrade]  VALID
--> Oracle Text                            [upgrade]  VALID
--> Oracle XML Database                    [upgrade]  VALID
--> Oracle Java Packages                   [upgrade]  VALID
--> Oracle Multimedia                      [upgrade]  VALID
--> Oracle Spatial                         [upgrade]  VALID
--> Expression Filter                      [upgrade]  VALID
--> Rule Manager                           [upgrade]  VALID
--> Oracle Application Express             [upgrade]  VALID
--> Oracle OLAP API                        [upgrade]  VALID
**********************************************************************
                              [Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
     minimum required size: 1245 MB
--> SYSAUX tablespace is adequate for the upgrade.
     minimum required size: 1420 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
     minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
     minimum required size: 60 MB
                      [No adjustments recommended]
**********************************************************************
**********************************************************************
                          [Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Process Count may be too low
     Database has a maximum process count of 150 which is lower than the
     default value of 300 for this release.
     You should update your processes value prior to the upgrade
     to a value of at least 300.
     For example:
        ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
     or update your init.ora file.
WARNING: --> Enterprise Manager Database Control repository found in the database
     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole
   - Connect to the Database using the SYS account AS SYSDBA:
   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not
     be able to follow the progress of the script.
INFORMATION: --> OLAP Catalog(AMD) exists in database
     Starting with Oracle Database 12c, OLAP Catalog component is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/olap/admin/catnoamd.sql script before or
     after the upgrade.
INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 11.2.0.4.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.
INFORMATION: --> There are existing Oracle components that will NOT be
     upgraded by the database upgrade script.  Typically, such components
     have their own upgrade scripts, are deprecated, or obsolete.
     Those components are:  OLAP Catalog,OWB
INFORMATION: --> Oracle Application Express (APEX) can be
     manually upgraded prior to database upgrade
     APEX is currently at version 3.2.1.00.12 and will need to be
     upgraded to APEX version 4.2.5 in the new release.
     Note 1: To reduce database upgrade time, APEX can be manually
             upgraded outside of and prior to database upgrade.
     Note 2: See MOS Note 1088970.1 for information on APEX
             installation upgrades.
**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************
                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^


**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************
                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
                   ************  Summary  ************

 0 ERRORS exist in your database.
 2 WARNINGS that Oracle suggests are addressed to improve database performance.
 4 INFORMATIONAL messages that should be reviewed prior to your upgrade.
 After your database is upgraded and open in normal mode you must run
 rdbms/admin/catuppst.sql which executes several required tasks and completes
 the upgrade process.
 You should follow that with the execution of rdbms/admin/utlrp.sql, and a
 comparison of invalid objects before and after the upgrade using
 rdbms/admin/utluiobj.sql
 If needed you may want to upgrade your timezone data using the process
 described in My Oracle Support note 1509653.1
                   ***********************************

The 11.2.0.4 database has the OLAP Catalog AMD component installed, and this component is no longer included in Oracle Database starting with Oracle Database 12c.

Remove the OLAP Catalog AMD component using the script from the 12.1.0.2 Oracle Home:

SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/olap/admin/catnoamd.sql



Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
PL/SQL procedure successfully completed.


1 row deleted.

SQL> commit;

If The preupgrade log includes a message about moving audit data from system.aud$ to sys.aud$ because Oracle Label Security is installed.Move the AUD$ table now using the olspreupgrade.sql script from the Oracle Database 12c home from SYSTEM to SYS:

SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/olspreupgrade.sql

Function created.

No errors.

Function created.

No errors.

Function created.

No errors.
****THIS SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE
CONFIGURED*****

PL/SQL procedure successfully completed.

No errors.
****THIS SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE
CONFIGURED*****

PL/SQL procedure successfully completed.

No errors.

Gather dictionary stats prior to the upgrade:
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Execute the preupgrade_fixups.sql – it was created by preupgrd.sql in directory /u01/app/oracle/cfgtoollogs/orcl/preupgrade

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

Pre-Upgrade Fixup Script Generated on 2017-02-08 06:22:34  Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container ORCL

**********************************************************************
Check Tag:     DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary:   Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low

     Database has a maximum process count of 150 which is lower than the
     default value of 300 for this release.
     You should update your processes value prior to the upgrade
     to a value of at least 300.
     For example:
        ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
     or update your init.ora file.
**********************************************************************


**********************************************************************
Check Tag:     EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary:   Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database

     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole

   - Connect to the Database using the SYS account AS SYSDBA:

   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not
     be able to follow the progress of the script.
**********************************************************************


**********************************************************************
Check Tag:     AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary:   Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP Catalog component is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/olap/admin/catnoamd.sql script before or
     after the upgrade.
**********************************************************************


**********************************************************************
Check Tag:     APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary:   Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Oracle Application Express (APEX) can be
     manually upgraded prior to database upgrade

     APEX is currently at version 3.2.1.00.12 and will need to be
     upgraded to APEX version 4.2.5 in the new release.
     Note 1: To reduce database upgrade time, APEX can be manually
             upgraded outside of and prior to database upgrade.
     Note 2: See MOS Note 1088970.1 for information on APEX
             installation upgrades.
**********************************************************************


**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

 4 fixup routines generated INFORMATIONAL messages that should be reviewed.

**************** Pre-Upgrade Fixup Script Complete *********************

PL/SQL procedure successfully completed.

SQL >shutdown immediate
Copy Pfile,password file to the new Oracle Home 12c.
Open the database in upgrade mode
SQL> startup upgrade
Then run the Upgrade script
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
Startup the database and recompile everything:

SQL> startup

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

Execute the postupgrade_fixups.sql:

SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql


Post Upgrade Fixup Script Generated on 2017-02-08 06:22:34  Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups...

**********************************************************************
Check Tag:     OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 12.1.0.2.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************


**********************************************************************
Check Tag:     NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary:   This fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action.
If you want to upgrade those other components, you must do so manually.
**********************************************************************


**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

 2 fixup routines generated INFORMATIONAL messages that should be reviewed.

*************** Post Upgrade Fixup Script Complete ********************

PL/SQL procedure successfully completed.

Adjust Time Zone settings – you may look into the scripts taken from MOS Note: 1509653.1 before executing them:

SQL>@/home/oracle/DST/DST_prepare.sql
SQl>@/home/oracle/DST/DST_adjust.sql
SQL>exit;

And database Upgrade done successfully.