Wednesday, April 16, 2014

Upgrade your Database to 12c Superfast!

Most DBAs know about the catupgrd.sql script Upgrade Utility to process the upgrade singled threaded. Now there is a faster way to upgrade your Oracle Database to 12c, you can use the catctl.pl the Parallel Upgrade Utility – a perl script, which replaces catupgrd.sql and provides both parallel processing mode and serial mode. This will greatly help reduce downtime for a database upgrade to 12c and I highly recommend you use this option.

The –n option Specifies the number of processes to use for parallel operations (default = 4). The maximum is 8. Set this parameter to 0 to run catctl.pl in serial mode.

The catctl.pl Parallel Upgrade Utility is integrated with DBUA the gui upgrade utility. However, for manual, command line upgrades, you can run catctl.pl with various parameters

If -n 3 is used when invoking catctl.pl, then catupgrd0.log, catupgrd1.log, catupgrd2.log are created during the upgrade process. When confirming if an upgrade ran fine or failed, all of the catupgrd#.log files must be checked. If catctl.pl is rerun, then the previous log files are overwritten unless you specify a different log directory.

To run catctl.pl on Linux from the new Oracle 12c home.


cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 3 -l $ORACLE_HOME/diagnostics catupgrd.sql


Once you start the database in upgrade mode by issuing the following command:
SQL> STARTUP UPGRADE

The UPGRADE keyword prepares the database for the upgrade.

Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catctl.pl script is run. Before running catctl.pl, queries on any other view or the use of PL/SQL returns an error.

If errors appear listing desupported initialization parameters, then make a note of the desupported initialization parameters and continue with the upgrade. Remove the desupported initialization parameters the next time you shut down the database.

Steps to Rename an Oracle Enterprise Manager Repository Database

Steps to rename an OEM repository Database.

This is something you may not have to do very often. I will show you how to rename your Oracle Enterprise Manager 12c repository database.

Take a full RMAN backup with archivelogs.

Shutdown OEM

$ . oraenv
ORACLE_SID = [oem] ? oem
The Oracle base for ORACLE_HOME=/opt/apps/MW/oms is /home/oracle
[oracle@dnvappoem06 ~]$ emctl stop oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...

Change the cluster database parameter to false

SQL> alter system set cluster_database=false scope=spfile sid='*';
System altered.

Backup the spfile
SQL> create pfile='/tmp/initent_oem.ora' from spfile;

File created.

Update the init file /tmp/initent_oem.ora with new parameters for the new sid and db name
Stop the database

/home/oracle>srvctl stop database -d oemsblp

In ASM make the new directories
mkdir +DATA/ent_oem/tempfile
mkdir +DATA/ent_oem/datafile


Startup mount only on one of the instances 

Invoke the utility on the command line, specifying a valid user with the SYSDBA privilege.  You must specify both the DBNAME and SETNAME parameters. The SETNAME parameter tells the DBNEWID utility to only alter the database name. 
  
% /home/oracle>nid TARGET=/  DBNAME=ent_oem SETNAME=YES

DBNEWID: Release 11.2.0.3.0 - Production on Thu Feb 13 20:44:01 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database OEMSBLP (DBID=1189579321)

Connected to server version 11.2.0

Control Files in database:
    +RECO/oemsblp/controlfile/current.261.832085049

Change database name of database OEMSBLP to ENT_OEM? (Y/[N]) => Y

Proceeding with operation
Changing database name from OEMSBLP to ENT_OEM
    Control File +RECO/oemsblp/controlfile/current.261.832085049 - modified
    Datafile +DATA/oemsblp/datafile/system.293.83208499 - wrote new name
    Datafile +DATA/oemsblp/datafile/sysaux.275.83208499 - wrote new name
    Datafile +DATA/oemsblp/datafile/undotbs1.276.83208499 - wrote new name
    Datafile +DATA/oemsblp/datafile/users.290.83208499 - wrote new name
    Datafile +DATA/oemsblp/datafile/undotbs2.292.83208513 - wrote new name
    Datafile +DATA/oemsblp/datafile/mgmt_ecm_depot_ts.287.83232853 - wrote new name
    Datafile +DATA/oemsblp/datafile/mgmt_tablespace.270.83232853 - wrote new name
    Datafile +DATA/oemsblp/datafile/mgmt_ad4j_ts.264.83232853 - wrote new name
    Datafile +DATA/oemsblp/tempfile/temp.291.83208507 - wrote new name
    Control File +RECO/oemsblp/controlfile/current.261.832085049 - wrote new name
    Instance shut down

Database name changed to ENT_OEM.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

/home/oracle>





Create a new init file pointer in the $ORACLE_HOME/dbs on each node

NODE1:
/opt/oracle/product/11203/db_1/dbs>cat initent_oem1.ora
SPFILE='+DATA/ent_oem/spfileent_oem.ora'

NODE2:
/opt/oracle/product/11203/db_1/dbs>cat initent_oem2.ora
SPFILE='+DATA/ent_oem/spfileent_oem.ora'

 Create a new password file on each node from the $ORACLE_HOME/dbs directory.

NODE1:
cp orapwoemsblp1 orapwent_oem1

NODE2:
cp orapwoemsblp1 orapwent_oem2

create spfile='+DATA/ent_oem/spfileent_oem.ora' from pfile='/tmp/initent_oem.ora' ;

Set the new ORACLE_SID
Verify the parameters are updated correctly to the new name, note the instance_name and db_name and other parameters.
SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      ent_oem
db_unique_name                       string      ent_oem
global_names                         boolean     FALSE
instance_name                        string      ent_oem1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      ent_oem
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ent_oem/spfileent_oem.ora
                                                 


Change the cluster_database parameter back to true
SQL> alter system set cluster_database=true scope=spfile sid='*';

DBNEWID utility makes the changes in the controlfile and datafile headers. This utility is not RAC aware. So it will not update the OCR when the database is renamed. Hence, the user needs to invoke SRVCTL to remove and again add the database information to OCR:

Before the Rename
srvctl stop database -d
srvctl remove instance -d oemsblp -i oemsblp1
srvctl remove instance -d oemsblp -i oemsblp2
 (repeat above command for each instance as needed)
srvctl remove database -d oemsblp

And after the Rename
srvctl add database -d ent_oem -o /opt/oracle/product/11203/db_1
/opt/oracle/product/11203/db_1/dbs>srvctl add instance -d ent_oem -i ent_oem1 -n dnvdboem01
/opt/oracle/product/11203/db_1/dbs>srvctl add instance -d ent_oem -i ent_oem2 -n dnvdboem02

(repeat above command for each instance as needed)

Start the database
srvctl start database –d ent_oem


Save a backup copy of the controlfile of the database.
alter database backup controlfile to trace as '/tmp/ent_oem.trc';

Change the cluster_mode back to false

alter system set cluster_database=false scope=spfile sid='*';




Shutdown the database and restart in mount mode.


Copy and rename the datafiles to the new directory location via RMAN while connected to the $ORACLE_SID
copy datafile '+DATA/oemsblp/datafile/system.293.832084993' to '+data';
copy datafile '+DATA/oemsblp/datafile/sysaux.275.832084993' to '+data';
copy datafile '+DATA/oemsblp/datafile/undotbs1.276.832084993' to '+data';
copy datafile '+DATA/oemsblp/datafile/users.290.832084993' to '+data';
copy datafile '+DATA/oemsblp/datafile/undotbs2.292.832085133' to '+data';
copy datafile '+DATA/oemsblp/datafile/mgmt_ecm_depot_ts.287.832328533' to '+data';
copy datafile '+DATA/oemsblp/datafile/mgmt_tablespace.270.832328533' to '+data';
copy datafile '+DATA/oemsblp/datafile/mgmt_ad4j_ts.264.832328533' to '+data';



ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 1000M;

Run the following controlfile script with the new directory locations

STARTUP NOMOUNT
CREATE CONTROLFILE reuse DATABASE "ENT_OEM" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '+RECO/ent_oem/onlinelog/group_1.262.832085053'  SIZE 500M BLOCKSIZE 512,
  GROUP 2 '+RECO/ent_oem/onlinelog/group_2.263.832085057'  SIZE 500M BLOCKSIZE 512,
  GROUP 3 '+RECO/ent_oem/onlinelog/group_3.265.832085239'  SIZE 500M BLOCKSIZE 512,
  GROUP 4 '+RECO/ent_oem/onlinelog/group_4.266.832085247'  SIZE 500M BLOCKSIZE 512,
  GROUP 5 '+RECO/ent_oem/onlinelog/group_5.264.832085061'  SIZE 500M BLOCKSIZE 512,
  GROUP 6 '+RECO/ent_oem/onlinelog/group_6.267.832085253'  SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/ent_oem/datafile/system.293.832084993',
  '+DATA/ent_oem/datafile/sysaux.275.832084993',
  '+DATA/ent_oem/datafile/undotbs1.276.832084993',
  '+DATA/ent_oem/datafile/users.290.832084993',
  '+DATA/ent_oem/datafile/undotbs2.292.832085133',
  '+DATA/ent_oem/datafile/mgmt_ecm_depot_ts.287.832328533',
  '+DATA/ent_oem/datafile/mgmt_tablespace.270.832328533',
  '+DATA/ent_oem/datafile/mgmt_ad4j_ts.264.832328533'
CHARACTER SET AL32UTF8
;

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 1000M;

On the OMS server run the following command to change the OMS repository database name

[oracle@dnvappoem06 ~]$ emctl config oms -store_repos_details -repos_conndesc '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=doem1dbrac-scan)(PORT=1521)))(LOAD_BALANCE=ON)
> (CONNECT_DATA=(SERVICE_NAME=ent_oem)))' -repos_user sysman
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Repository User's Password :

Successfully updated datasources and stored repository details in Credential Store.
If there are multiple OMSs in this environment, run this store_repos_details command on all of them.
And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
[oracle@dnvappoem06 log]$ emctl config oms -list_repos_details
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=doem1dbrac-scan)(PORT=1521)))(LOAD_BALANCE=ON)
(CONNECT_DATA=(SERVICE_NAME=ent_oem)))
Repository User : sysman

[oracle@dnvappoem06 ~]$
[oracle@dnvappoem06 ~]$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

[oracle@dnvappoem06 ~]$ emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

[oracle@dnvappoem06 ~]$ emctl status oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
WebTier is Up

Oracle Management Server is Up