MAX_STRING_SIZE Parameter In Oracle 12c:

Parameter "MAX_STRING_SIZE" is introduced in Oracle 12c.This controls the maximum size of string size in Oracle database.Parameter value either can be set to  STANDARD or EXTENDED
Default value is STANDARD.

MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2.

MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .

We can change the parameter value from STANDARD to EXTENDED but not possible from EXTENDED to STANDARD.


If MAX_STRING_SIZE value set to STANDARD and  try to set the length of column more than 4000,will return ORA-00910.

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD


SQL> create table country ( state_name VARCHAR2(7000));
create table country ( state_name VARCHAR2(7000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

This is how we can convert MAX_STRING_SIZE to EXTENDED:

1. Start database in upgrade mode:

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

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

SQL>STARTUP UPGRADE

ORACLE instance started.
Total System Global Area 900000 bytes
Fixed Size 666 bytes
Variable Size 271790 bytes
Database Buffers 271790 bytes
Redo Buffers 4140 bytes
Database mounted.
Database opened.

2. Change the value to EXTENDED

SQL>  alter system set MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;

System altered.

SQL> show parameter MAX_STRING_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

3.Run script utl32k.sql :

@?/rdbms/admin/utl32k.sql

4.Restart the database:

shutdown immediate;
startup
SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

Know Issues:

1. If you try to restart the database without running utl32k.sql script, got below error.

Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete
Process ID: 18669
Session ID: 401 Serial number: 16419

So start the database again in UPGRADE mode and execute utlk32.sql script.

2. If you try to set the value to EXTENDED , when database is not in UPGRADE mode.


SQL> alter system set MAX_STRING_SIZE=EXTENDED;
alter system set MAX_STRING_SIZE=EXTENDED
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

before running this alter statement, need to start database in UPGRADE mode.

ADOP Phase: abort,Restart and Abandon

adop defaults to abandon=no restart=yes if not specified,

To restart from beginning,  use restart=no abandon=yes

adop phase=apply patches=11111,11111 abandon=yes          (new patch)

To restart a patch you need to use restart=yes abandon=no .

adop phase=apply patches=11111 restart=yes [abandon=no is implied and not required]

eg. adop restart=no abandon=yes  phase=apply
If unable to restart, check for these two AD_ADOP_SESSIONS and AD_ADOP_SESSION_PATCHES tables and analyze the o/p:
---------------------------------------------------------------------------------------

column id format 99
column nn format a10
column nt format a6
select adop_session_id id, prepare_status pr, apply_status ap,
finalize_status fi, cutover_status cu, cleanup_status cl, abort_status ab, status st, node_name nn, node_type nt
from ad_adop_sessions
order by adop_session_id desc;


column id format 99
column bn format a12
column pr format 99999999
column afs format a40
column pfs format a40
column cs format a9
column nn format a10
column ed format a28
column drv format a28
column pt format a35

select adop_session_id id, bug_number bn, patchrun_id pr, status st,
node_name nn, cast(end_date as timestamp) ed, driver_file_name drv, patch_top  pt
from ad_adop_session_patches
order by end_date desc;

To diagnostic more for the root cause why patch application failed, check for adpatch log for the failed patch 1111.
cd  $APPL_TOP_NE/../log/adop/<session id>


If we wants to start from a fresh page and considering the state of these tables, making a manual modification to the  AD_ADOP_SESSIONS table just so it would not hold up ADOP execution due to an  incomplete hotpatch session.

update ad_adop_sessions set status='C' where adop_session_id=<from above o/p>;

restart adop and retest.

=================================

Aborting an Online Patching Cycle:

If a patching cycle is failing and the issue cannot be resolved quickly, it is possible to abort the patching cycle and return to normal runtime operation. The patch edition will be dropped. You can abandon a patching cycle (without applying any patches) by running the command:

$ adop phase=abort

Important: This abort command can only be used before successful completion of the cutover phase. After cutover, the system is running on the new edition, and abort is no longer possible for that patching cycle. Aborting a patching cycle will drop the patch edition, but you must then run the cleanup and fs_clone phases before starting a new patching cycle. The cleanup must be a full cleanup.

For example:

$ adop phase=prepare

$ adop phase=apply patches=123456

[Patch application encounters problems and you want to abort]

$ adop phase=abort

$ adop phase=cleanup cleanup_mode=full

$ adop phase=fs_clone

Optionally, you can combine the abort and cleanup commands as follows:

$ $ adop phase=abort,cleanup cleanup_mode=full

Note: You cannot abort application of a patch applied in hotpatch mode or downtime mode.

ADOP apply phase options ABANDON and RESTART

Restarting a failed worker :

When adop is using parallel processing and an error occurs, the job fails. Review the main adop log file and the adworkxxx.log file to determine the source of the error, resolve the issues and continue. Restart adop using the adctrl command.

Restarting adop :

If you have shut down the workers, or if adop quits while performing processing actions, it saves all the actions completed up to that point in restart files. Investigate and resolve the problem that caused the failure, then restart adop.

restart options,

restart=(yes|no)  [default: no]

Use restart=yes to resume the previous failed apply command from where processing terminated.  If an apply command fails, check the log files for further information.  If the problem can be corrected, you can then restart the apply command where it left off using the restart parameter.

Example - Restart a apply command

adop phase=apply patches=123456 restart=yes

When restarting a failed apply it is important to use the same parameters as the failed command, with only the addition of the restart=yes parameter.

abandon=(yes|no)  [default: no]

Use abandon=yes to abandon the previous failed apply command and start a new apply command.  Note that any changes made to the system by the failed command will remain in effect. The abandon flag is most useful when applying a replacement patch for the failing patch.

Example - Abandon previous apply command and apply replacement

adop phase=apply patches=223456 abandon=yes

If a patch fails to apply and there is no replacement patch, you may also abort the complete online patching cycle. 

You have several options when restarting (or abandoning) application of individual patches, as follows :

• If you want to restart a failed patch from where it left off, you only need to specify restart=yes on the command line:

adop phase=apply patches=1234 restart=yes

• If you want to restart a failed patch from the very beginning, you need to specify abandon=yes on the command line:

adop phase=apply patches=1234 abandon=yes

• If you want to ignore a previously failed patch and apply a different one instead, you need to specify the new patch number and abandon=yes on the command line:

adop phase=apply patches=5678 abandon=yes

================================================================================

ADOP Useful Options 12.2

To restart worker where failed, while applying patch using "adop" in 12.2

wait_on_failed_job=(yes|no)  [default: no]

        Controls whether adop apply command exits when all workers have

        failed.  Instead of exiting, you can force adop to wait, and use

        the "adctrl" to retry failed jobs.

adop phase=apply patches=123456 wait_on_failed_job=yes

NB: This works only for Worker jobs, Not for objects compilation fails, like forms, pll's.

For these you have to use "flags=autoskip"

adop phase=apply patches=123456 flags=autoskip

 skipsyncerror=(yes|no)  [default: no]

        Specifies whether to ignore errors that may occur during incremental

        file system synchronization.  This might happen if you applied

        a patch in the previous patching cycle that had errors but decided

        to continue with the cutover.  When the patch is synchronized on

        the next patching cycle, the apply errors may occur again, but

        can be ignored.

        Example:

            adop phase=prepare skipsyncerror=yes

Aborting an online patching cycle:

        If an online patching cycle encounters problems that cannot be

        fixed immediately you can abort the patching cycle and return

        to normal runtime operation.

        Example - Aborting a failed online patching cycle:

            adop phase=prepare

            adop phase=apply patches=123456

            ### serious unfixable error reported

            adop phase=abort

            adop phase=cleanup cleanup_mode=full

            adop phase=fs_clone

        The abort command drops the database patch edition and

        returns the system to normal runtime state.  Immediately

        following abort, you must also run a full cleanup and

        fs_clone operation to fully remove effects of the failed

        online patching cycle.

adop cutover hang

If an adop cutover hangs or a server has crash or reboot issues in the middle of the adop cutover phase, execute the following steps to fix the issue and then proceed with the patch process.

SOLUTION

Make sure no services or processes are running from the PATCH file system.

Ensure that the Weblogic Admin Server and Node Manager are running on the run file system. Execute the following commands to check the status:

 $ adadminsrvctl.sh status

 $ adnodemgrctl.sh status

Execute the following commands:

 $ adop phase=abort

 $ adop phase=cleanup cleanup_mode=full

 $ adop phase=fs_clone force=yes

Run an empty adop cycle to make sure there is no issue in the adop cutover by executing the following command:

 $adop phase=prepare, finalize, cutover, cleanup cleanup_mode=full

Start a fresh adop prepare and apply patches.

After the apply step, complete the rest of the adop phases including finalize, cutover, and cleanup.

Trying to resume a failed cutover session giving Invalid Credentials in Oracle Apps R12.2

If you attempt to resume a failed session after cutover exits with cutover_status=3, I receive an 'Invalid Credentials' error.

To check the status you can use the query as earlier posted.

This will be because the database patch edition has already been promoted to be the new run edition. To resume and complete cutover successfully, run the command:

$ adop phase=cutover action=nodb

http://soban-dba.blogspot.com/2017/04/adop-options-availble-in-r122.html

Upgrade Oracle 11gR2 (11.2.0.4) Grid Infrastructure to Oracle 12cR2 (12.2.0.1):

Grid Infrastructure Management Repository (GIMR),In 12.1.0.1 we had the option of installing the GIMR database – MGMTDB. But in 12.1.0.2 it is mandatory and the MGMTDB database is automatically created as part of the upgrade or initial installation process of 12.10.2 Grid Infrastructure.The GIMR primarily stores historical Cluster Health Monitor metric data. It runs as a container database on a single node of the RAC cluster.datafiles for the MGMTDB database are created on the same ASM disk group which holds the OCR and Voting Disk,at least 4 GB of free space in that ASM disk group – or an error INS-43100 will be returned as shown in the figure below.


1.To check GI active version and upgrade state:

crsctl query crs activeversion
crsctl query crs releaseversion
crsctl query crs softwareversion
crsctl query crs activeversion -f


2.Collect the crs status by running below commands[ grid owner]

/u01/oracle/11.2.0/grid/crsctl check crs
/u01/oracle/11.2.0/grid/crsctl stat res -t
/u01/oracle/11.2.0/grid/crsctl stat res -p
/u01/oracle/11.2.0/grid/oifcfg iflist -p -n
/u01/oracle/11.2.0/grid/oifcfg getif
/u01/oracle/11.2.0/grid/ocrcheck

3.Take OCR backup manually before GI upgrade. This could be used in downgrading the GI from 12.2.0.1 to 11.2.0.4 later on if required.

./ocrconfig -export /softdump/BACKUP/pre_upgrade_backup/ocr_file


4.Download the Oracle Grid Infrastructure image file , create the Grid home directory, and extract the image files in this Grid home directory.

$ mkdir -p /u01/oracle/12.2.0/grid
$ chown oracle:oinstall /u01/oracle/12.2.0/grid
$ cd /u01/oracle/12.2.0/grid
$ unzip -q linuxx64_12201_grid_home.zip

5.Run the cluvfy script for precheck and fix prerequisites missing.To verify all the prerequisites for the 12c GI upgrade are in place.
Verify the prerequisites with “runcluvfy”
runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u01/oracle/11.2.0/grid -dest_crshome /u01/oracle/12.2.0/grid -dest_version 12.2.0.1.0 -fixup -verbose

6.Stop the database/services running in grid

srvctl stop database -d PROD

7.Take backup of Grid binary:

tar -cvf grid_home_preupg.tar $GRID_HOME

8.Now start the upgrade using runInstaller.

GI Management repository is a new feature introduced with 12c. When this is selected with the GI installation a database is created (named -MGMTDB) and it's data files are stored in the same disk group where the OCR and vote disks are stored.

9.Run the scripts on the local node:[ from root]
root #/crsapp/app/oracle/product/grid12c/rootupgrade.sh

10.Now run the script on other node.