Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1)

Datapatch in a Multitenant Environment:

When datapatch is run to invoke any SQL actions in a Multitenant environment, datapatch only applies the changes to the ROOT, SEED and any open PDBs based on the pending actions.  For any PDB that is not currently open, the SQL patch will not be applied.  When such a PDB is reopened, it will be opened in restricted mode as there will be a patch level mismatch between the PDB and the ROOT.

Call DBMS_PDB.CHECK_PLUG_COMPATIBILITY on 12.1.0.1. PDB xml file will return these two violations, which are expected.
  SQL Patch              ERROR
 
SQL patch bug # mismatch: Installed in the PDB but not in the CDB.   Install the SQL patch in the PDB or the CDB

This error can be cleared by running datapatch again and closing and reopening the affected PDB(s)

1)    Invoke datapatch:

% cd $ORACLE_HOME/OPatch
% datapatch

2)    Close and reopen the affected PDB

Connect as SYS
alter pluggable database <PDB Name> close instances =all;
alter pluggable database <PDB Name> open read write instances =all;

3) Check again for plugin violations (by querying pdb_plug_in_violations in cdb$root), there should be none and the pluggable database should be opened in normal mode

RENAME DATABASE HAVING DATAFILES ON ASM – 11GR2 and higher

Here we are renaming the database from “DB001” to “DB002”.This method can be used for the database on lower versions (than 11gR2) also.

[oracle@rac1~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 00:57:47 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.



SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             364906656 bytes
Database Buffers          163577856 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> show parameter instance_name 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      DB001
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DB001

Bring the database is mount state to run the “NID” utility. Here we are only changing the db name, the parameter “setname” is used.

[oracle@rac1 ~]$ nid

DBNEWID: Release 11.2.0.4.0 - Production on Tue Apr 21 01:00:56 2015

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

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

[oracle@rac1 ~]$ nid target=/ setname=yes dbname=DB002

DBNEWID: Release 11.2.0.4.0 - Production on Tue Apr 21 01:00:56 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database DB001(DBID=1192118653)

Connected to server version 11.2.0.4

Control Files in database:
    +DATA1/db001/controlfile/current.260.749749843

Change database name of database DB001 to DB002? (Y/[N]) => Y

Proceeding with operation
Changing database name from DB001 to DB002
    Control File +DATA1/db001/controlfile/current.260.749749843 - modified
    Datafile +DATA1/db001/datafile/system.256.74974966 - wrote new name
    Datafile +DATA1/db001/datafile/sysaux.257.74974966 - wrote new name
    Datafile +DATA1/db001/datafile/undotbs1.258.74974966 - wrote new name
    Datafile +DATA1/db001/datafile/users.259.74974966 - wrote new name
    Datafile +DATA1/db001/tempfile/temp.264.74974988 - wrote new name
    Control File +DATA1/db001/controlfile/current.260.749749843 - wrote new name
    Instance shut down

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

Alert log shows

*** DBNEWID utility started ***
DBNAME will be changed from DB001 to new DBNAME of DB002
Starting datafile conversion
Datafile conversion complete
Database name changed to DB002.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
*** DBNEWID utility finished succesfully ***

Create a password file for DB002 using orapwd utility.From 11gr1 you run the orapwd without password argument.

[oracle@anand-lab dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwmatrix.ora ignorecase=y entries=3

Enter password for SYS: 
[oracle@anand-lab dbs]$ 

Create a backup spfile for matrix database

[oracle@rac1 dbs]$ cat initDB001.ora
SPFILE='+DATA/db001/spfiledb001.ora'
[oracle@rac1 dbs]$ cat initDB002.ora.spfile
SPFILE='+DATA/db002/spfiledb002.ora'

Modify the /etc/oratab to reflect the new database name

[oracle@rac1 dbs]$ vi /etc/oratab 
[oracle@rac1 dbs]$ cat /etc/oratab | grep DB002
DB002:/oracle/db:N
[oracle@rac1 dbs]$

Setting environment for new database name and SID . This will create the bdump,trace,cdump and required directories in the diagnostic_dest

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [DB001] ? DB002
The Oracle base remains unchanged with value /oracle/db
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 01:22:08 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             331352224 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
SQL> create pfile from spfile;

File created.

SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit

Pfile with the name initDB002.ora is created in the $ORACLE_HOME/dbs folder.

[oracle@rac1 dbs]$ ls -ltr init*
-rw-r--r--. 1 oracle dba 2851 May 15  2009 init.ora
-rw-r-----. 1 oracle dba  828 Apr 20 23:48 initDB001.ora
-rw-r-----. 1 oracle dba  828 Apr 21 00:06 initDB002.ora.spfile
-rw-r-----. 1 oracle dba   38 Apr 21 00:11 initDB002.ora

In the initDB002.ora replace the db001 with db002 –

[oracle@anand-lab dbs]$ more initDB002.ora
DB002.__db_cache_size=452984832
DB002.__java_pool_size=16777216
DB002.__large_pool_size=83886080
DB002.__oracle_base='/oracle'#ORACLE_BASE set from environment
DB002.__pga_aggregate_target=520093696
DB002.__sga_target=754974720
DB002.__shared_io_pool_size=0
DB002.__shared_pool_size=184549376
DB002.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/DB002/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA1/DB002/controlfile/current.263.877260989'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_name='DB002'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB002XDB)'
*.local_listener='LN002'
*.memory_target=1261436928
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='DB002','SN002'
*.undo_tablespace='UNDOTBS1'

Create the adump folder on the specific location as per the pfile and start the instance to create the spfile

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 01:22:08 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             327157920 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
SQL> create spfile='+DATA1/db002/spfiledb002.ora' from pfile;

File created.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit

Once the spfile file is created change the initDB002.ora.spfile which point to the new ASM spfile to initDB002.ora

Use the controlfile in “+DATA1/db001” to create a new controlfile in “+DATA1/db002”

[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 21 01:31:13 2015

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1345376 bytes
Variable Size                327157920 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5832704 bytes

RMAN> restore controlfile from '+DATA/db001/controlfile/current.260.749749843';

Starting restore at 20-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA1/db002/controlfile/current.268.754234199
Finished restore at 20-APR-15
RMAN> shutdown
Oracle instance shut down
RMAN> exit

From 11gR2 there is no need to update the spfile using alter system set control_files command to the new output file name.

Start the instance with the spfile

oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 01:22:08 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             327157920 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
SQL> alter database mount;

Database altered.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA1/db002/controlfile/current.268.754234199

Check the parameter db_recovery_file_dest_size if not set, do set it. If already set check for sufficient space.

SQL> alter system set db_recovery_file_dest_size = rG;

System altered.

SQL>  alter system set db_recovery_file_dest='+DATA1';

System altered.

SQL> 

[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 21 01:36:50 2015

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

connected to target database: DB002 (DBID=1192118653)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB002

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA1/db001/datafile/system.268.877566173
2    520      SYSAUX               ***     +DATA1/db001/datafile/sysaux.269.877566407
3    55       UNDOTBS1             ***     +DATA1/db001/datafile/undotbs1.270.877566553
4    5        USERS                ***     +DATA1/db001/datafile/users.272.877566575

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    100      TEMP                 32767       +DATA1/db001/tempfile/temp.274.877567015

RMAN> 

RMAN> backup as copy database;

This will create the output file in +DATA1 diskgorup as my db_recovery_file_dest is set to +DATA02. Once down you can list the copy of the database using

RMAN> list copy of database;

Then switch the datafile to copy - 

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA1/db002/datafile/system.259.754249711"
datafile 2 switched to datafile copy "+DATA1/db002/datafile/sysaux.260.754249783"
datafile 3 switched to datafile copy "+DATA1/db002/datafile/undotbs1.261.754249833"
datafile 4 switched to datafile copy "+DATA1/db002/datafile/users.265.754249879"
datafile 5 switched to datafile copy "+DATA1/db002/datafile/example.262.754249859"
datafile 6 switched to datafile copy "+DATA1/db002/datafile/test.263.754249875"

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB002

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA1/db002/datafile/system.268.877566173
2    520      SYSAUX               ***     +DATA1/db002/datafile/sysaux.269.877566407
3    55       UNDOTBS1             ***     +DATA1/db002/datafile/undotbs1.270.877566553
4    5        USERS                ***     +DATA1/db002/datafile/users.272.877566575

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    100      TEMP                 32767       +DATA1/db001/tempfile/temp.274.877567015

RMAN> 

From the RMAN command , open the database

RMAN> alter database open;

database opened

RMAN> sql'alter tablespace temp add tempfile';

sql statement: alter tablespace temp add tempfile

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB002

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA1/db002/datafile/system.268.877566173
2    520      SYSAUX               ***     +DATA1/db002/datafile/sysaux.269.877566407
3    55       UNDOTBS1             ***     +DATA1/db002/datafile/undotbs1.270.877566553
4    5        USERS                ***     +DATA1/db002/datafile/users.272.877566575

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 32767       +DATA1/db001/tempfile/temp.274.877567015
2    100      TEMP                 32767       +DATA1/db002/tempfile/temp.272.754250315


RMAN> sql "alter database tempfile ''+DATA1/db001/tempfile/temp.274.877567015'' drop";

sql statement: alter database tempfile ''+DATA/orcl/tempfile/temp.274.877567015'' drop

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB002

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA1/db002/datafile/system.268.877566173
2    520      SYSAUX               ***     +DATA1/db002/datafile/sysaux.269.877566407
3    55       UNDOTBS1             ***     +DATA1/db002/datafile/undotbs1.270.877566553
4    5        USERS                ***     +DATA1/db002/datafile/users.272.877566575

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    100      TEMP                 32767       +DATA1/db002/tempfile/temp.274.877567015

RMAN> 

Remember the redo logfiles would still be in the “+DATA1/db001” folder so , drop and create the new redo logfile groups using the alter database drop/add logfile group command.

SQL> select name from v$database;

NAME
---------
DB002

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB002

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA1/db002/onlinelog/group_3.258.877568201
+DATA1/db002/onlinelog/group_2.257.877568979
+DATA1/db002/onlinelog/group_1.261.877567919


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA1/db002/datafile/system.268.877566173
+DATA1/db002/datafile/sysaux.269.877566407
+DATA1/db002/datafile/undotbs1.270.877566553
+DATA1/db002/datafile/users.272.877566575

 

Once, everything is done and checked, remove the datafile,controlfile,tempfile,parameter files from the DB001folder in the diskgoup.

ORA-12543: TNS:destination host unreachable

[oracle@rac2 bin]$tnsping oradb001
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 30-Nov-2012 01:52:34
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
/oracle/11gdb/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb001)))

TNS-12543: TNS:destination host unreachable

Diagnosis process:

-------------------

Please use the oerr utility to get the more information on this error.

oracle@rac2 $ oerr ora 12543

12543, 00000, "TNS:destination host unreachable"

*Cause: Contact can not be made with remote party.

*Action: Make sure the network driver is functioning and the network is up.

Solution:
---------
Reason : Firewall was enabled on both the servers(rac1 & rac2).
-----------------------------------------------------------------

I disabled the firewall on both the servers.You can disable firewall by GUI and command line also.

root@rac1 # service iptables status
                     service iptables stop
                     chkconfig iptables off

Oracle 11g listener causing failed tcp connection attempts on a non-RAC system

Environment: Oracle database 11.2.0.3, Oracle Linux 6.2

I was debugging a possible network problem on a Oracle Linux server with a fresh installation of Oracle 11gR2, when I noticed the following:

$ netstat -s

Tcp:
1178980 active connections openings
273496 passive connection openings
911657 failed connection attempts

The number of “failed connection attempts” increased by one for every 2 seconds. So, something was clearly trying to connect to something else and this failed for some reason. I didn’t find how I could further debug this problem with netstat. However, I had free access to the server, so I tried to shut down the Oracle services one by one to see if this would stop the growing number of failed connection attempts. When I stopped the Oracle listener service, it did…

Listener logging was on and there were a lot of warnings in the listener.log file:

WARNING: Subscription for node down event still pending

I did a search on My Oracle Support and I found more information in the MOS document with ID 372959.1. Apparently, the listener service tries to contact a RAC service, but, since this is a non-RAC installation, the connection fails. The solution is to add SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener name>=OFF to yourlistener.ora file, and then restart the listener. This should remove the warnings and stop the failed connection attempts.

This is how the listener.ora file now looks like for my listener with name “LISTENER”:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools.   LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-tst.mydomain.com)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))     )   )   ADR_BASE_LISTENER = /u01/app/oracle   # see metalink note 372959.1 SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF