12c Steps to restore database from full backup in case of DR

This is a an restore exercise for a CDB database with CDB name.

1. Create INITcdb.ora file with content:

db_name='cdb'

2. Startup database in nomount (it will read the init file and will start the instace)

SQL> startup nomount
ORACLE instance started.

Total System Global Area  234881024 bytes
Fixed Size                  3044152 bytes
Variable Size             176164040 bytes
Database Buffers           50331648 bytes
Redo Buffers                5341184 bytes

3.  Restore SPFILE

In situations requiring the recovery of your SPFILE or control file from autobackup, such as disaster recovery when you have lost all database files, you will need to use your DBID, or you will hit following errors:

RMAN> restore spfile from autobackup;

Starting restore at 01-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=174 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:41:26
RMAN-06495: must explicitly specify DBID with SET DBID command

If you do not have a record of the DBID of your database, there are two places you can find it without opening your database.

• The DBID is used in forming the filename for the control file autobackup. Locate that file and check DBID (IIIIIIIIII) from filename c-IIIIIIIIII-YYYYMMDD-QQ.
  
• If you have any text files that preserve the output from an RMAN session, the DBID is displayed by the RMAN client when it starts up and connects to your database.

 RMAN> set dbid=1992057407;

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150701
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150630
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150629
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150628
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150627
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150626
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150625
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:48:53
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> restore spfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACKUP\20 15_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3047720 bytes
Variable Size               1493176024 bytes
Database Buffers             637534208 bytes
Redo Buffers                  13725696 bytes


4. Restore CONTROLFILES



RMAN> restore controlfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACK UP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

Starting restore at 01-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:55:26
ORA-19870: error while restoring backup piece E:\APP\ORACLE12C\FAST_RECOVERY_ARE
A\CDB\AUTOBACKUP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP
ORA-19504: failed to create file "E:\APP\ORACLE12C\ORADATA\CDB\CONTROL01.CTL"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

NOTE: Create necessary folders before restoring controlfiles


RMAN> restore controlfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACK UP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

Starting restore at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\APP\ORACLE12C\ORADATA\CDB\CONTROL01.CTL
output file name=E:\APP\ORACLE12C\FAST_RECOVERY_AREA\CDB\CONTROL02.CTL
Finished restore at 01-JUL-15

RMAN> alter database mount;

Statement processed

5. Check backups catalogued in controlfiles

RMAN> list backup summary;

6. Restore database (folders of datafiles must be created prior restoring)

RMAN> restore database;

7. Recover database

RMAN> recover database;

8. Open database with resetlogs

RMAN> alter database open resetlogs;

12c ORA-63999: data file suffered media failure

After a datafile missing in a PDB, the instance is terminated.
alert.log file:


Wed Jul 01 11:07:43 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ckpt_7972.trc:
ORA-63999: data file suffered media failure
ORA-01110: data file 10: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF'
ORA-01115: IO error reading block from file 10 (block # 1)
ORA-27072: File I/O error
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 11:07:43 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ckpt_7972.trc:
ORA-63999: data file suffered media failure
ORA-01110: data file 10: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF'
ORA-01115: IO error reading block from file 10 (block # 1)
ORA-27072: File I/O error
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 11:07:43 2015
System state dump requested by (instance=1, osid=7972 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_diag_9924_20150701110743.trc
Wed Jul 01 11:07:43 2015
USER (ospid: 7972): terminating the instance due to error 63999

C:\Users\oracle12c>rman target /

RMAN> restore tablespace pdb1:example;

RMAN> recover tablespace pdb1:example;


After the CBD restart, some other tablespaces were reported requiring media recovery:

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 34 needs media recovery
ORA-01110: data file 34: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF'

RMAN> recover pluggable database pdb1;

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

12c ORA-01122: database file 8 failed verification check

This are the steps that I took to recover from an datafile error in a pluggable database.

SQL> alter session set container=pdb1;

Session altered.


SQL> startup
ORA-01122: database file 8 failed verification check
ORA-01110: data file 8: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01.DBF'
ORA-01200: actual file size of 138240 is smaller than correct size of 139520
blocks
 
SQL> select open_mode from v$pdbs;

OPEN_MODE
----------
MOUNTED
 
C:\Users\oracle12c>rman target /

RMAN> validate check logical pluggable database pdb1;

Starting validate at 01-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00034 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF

input datafile file number=00010 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE0
1.DBF
input datafile file number=00008 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01
.DBF
input datafile file number=00007 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSTEM01
.DBF
input datafile file number=00035 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_DEE
PDIVE.DBF
input datafile file number=00033 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_ECM
_DEPOT1.DBF
input datafile file number=00009 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SAMPLE_S
CHEMA_USERS01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:01:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              8136         67840           23430249
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSTEM01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              39856
  Index      0              17041
  Other      0              2807

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    FAILED 0              25182        139520          23429928
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              42004
  Index      0              21220
  Other      206            51114

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    OK     0              481          640             2131817
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SAMPLE_SCHEMA_USERS01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              15
  Index      0              2
  Other      0              142

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   OK     0              152794       166320          22539104
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6849
  Index      0              1209
  Other      0              5468

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
33   OK     0              6108         7680            23427785
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_ECM_DEPOT1.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              578
  Index      0              258
  Other      0              736

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
34   OK     0              335452       454400          23427819
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              54775
  Index      0              21641
  Other      0              42532

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
35   OK     0              25401        25600           17792522
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_DEEPDIVE.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              30
  Index      0              6
  Other      0              163

validate found one or more corrupt blocks
See trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ora_5764.trc for de
tails
Finished validate at 01-JUL-15

In alert.log file:

Wed Jul 01 10:05:54 2015
Hex dump of (file 8, block 171) in trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ora_5764.trc

Corrupt block relative dba: 0x010000ab (file 8, block 171)
Bad header found during validation
Data in bad block:
 type: 1 format: 2 rdba: 0x00000ab0
 last change scn: 0x8044.000004f4 seq: 0x35 flg: 0x56
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x3
 computed block checksum: 0x0

Using DRA (Data Recovery Advisor) to list, advise and repair failure:

C:\Users\oracle12c>rman target /

connected to target database: CDB (DBID=1992057407)

RMAN> list failure ;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
7961       HIGH     OPEN      01-JUL-15     Datafile 8: 'E:\APP\ORACLE12C\ORADAT
A\CDB\PDB1\SYSAUX01.DBF' contains one or more corrupt blocks

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
7961       HIGH     OPEN      01-JUL-15     Datafile 8: 'E:\APP\ORACLE12C\ORADAT
A\CDB\PDB1\SYSAUX01.DBF' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=297 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Recover multiple corrupt blocks in datafile 8
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\hm\reco_3456085453.hm
 
RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\hm\reco_3456085453.hm

contents of repair script:
   # block media recovery for multiple blocks
   recover datafile 8 block 171 to 376;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting recover at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece E:\APP\ORACLE12C\FAST_RECOVERY_ARE
A\CDB\1FCD0926AC764930A9274C71DAB47DDC\BACKUPSET\2015_06_30\O1_MF_NNND0_20160630
_CDB_BKP_LVL_BS4H4RG8_.BKP
channel ORA_DISK_1: piece handle=E:\APP\ORACLE12C\FAST_RECOVERY_AREA\CDB\1FCD092
6AC764930A9274C71DAB47DDC\BACKUPSET\2015_06_30\O1_MF_NNND0_20160630_CDB_BKP_LVL_
BS4H4RG8_.BKP tag=20160630_CDB_BKP_LVL0
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:01:05

starting media recovery

archived log for thread 1 with sequence 1231 is already on disk as file E:\APP\O
RACLE12C\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2015_06_30\O1_MF_1_1231_BS4M4OD5_.ARC

...

archived log for thread 1 with sequence 1279 is already on disk as file E:\APP\O
RACLE12C\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2015_07_01\O1_MF_1_1279_BS746TMK_.ARC

media recovery complete, elapsed time: 00:00:47
Finished recover at 01-JUL-15
repair failure complete
 

Repair action failed. I restored and the recovery the tablespace.

RMAN> alter pluggable database pdb1 close;
RMAN> restore tablespace pdb1:sysaux;
RMAN> recover tablespace pdb1:sysaux;
RMAN> alter pluggable database pdb1 open;
RMAN> validate check logical pluggable database pdb1; -- check that are no corrupted database blocks in SYSAUX tablespace

12c recover from one redolog file lost

SQL> select group#, member, status from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
         3
E:\APP\ORACLE12C\ORADATA\CDB\REDO03.LOG


         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02.LOG


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------

         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
INVALID

         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02A.LOG
INVALID

         3

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
E:\APP\ORACLE12C\ORADATA\CDB\REDO03A.LOG
INVALID


6 rows selected.
 SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 INACTIVE
         3          2 CURRENT
 

Delete one redolog file from group 1 with SO commands.

SQL> alter system switch logfile;

System altered.

1. Verify what logfile is missing

alert.log content:

Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Wed Jul 01 08:53:12 2015
Thread 1 advanced to log sequence 1264 (LGWR switch)
  Current log# 1 seq# 1264 mem# 0: E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 CURRENT
         2          2 INACTIVE
         3          2 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 ACTIVE
         2          2 CURRENT
         3          2 ACTIVE

2. Archive the redo log group’s contents if it's the case (database in ARCHIVELOG mode); if you clear this redo log group before archiving it, you must back up the full database to ensure maximum recoverability of the database in case of the loss of a data file.

SQL> alter system archive log group 1;
alter system archive log group 1
*
ERROR at line 1:
ORA-16013: log 1 sequence# 1264 does not need archiving

3. Make the log group INNACTIVE, with a checkpoint. If you try to CLEAR LOGFILE when the logfile group is ACTIVE or CURRENT, following error will be raised:

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance cdb (thread 1)
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG'
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

Make the log group INNACTIVE, with a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 CURRENT
         3          2 INACTIVE
 

4. Run command CLEAR LOGFILE GROUP to recreate the missing logfile from the group:

SQL> alter database clear logfile group 1;

Database altered.

After the recreation of redo log member, you might see the following error in alert.log:

Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00320: cannot read file header from log 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

In this case you have to recreate the log member:

SQL> alter database drop logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LO
G' ;

Database altered.

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;

Database altered.

Before running the ADD LOGFILE MEMBER command, be sure to delete the dropped logfile member from filesystem with SO commands. If not you will receive following error:

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;
alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG' to
group 1
*
ERROR at line 1:
ORA-00301: error in adding log file 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
- file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

12c - Change PDB global name

12c - PDB to Use Global CDB (ROOT) Temporary Tablespace Functionality is Missing

Oracle 12c documentation:

http://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13556

Storage Limits

The optional STORAGE clause of the CREATE PLUGGABLE DATABASE statement specifies the following limits:

• The amount of storage that can be used by all tablespaces that belong to the PDB
  Use MAXSIZE and a size clause to specify a limit, or set MAXSIZE to UNLIMITED to indicate no limit.
  
• The amount of storage in the default temporary tablespace shared by all PDBs that can be used by sessions connected to the PDB
  Use MAX_SHARED_TEMP_SIZE and a size clause to specify a limit, or set MAX_SHARED_TEMP_SIZE to UNLIMITED to indicate no limit.
  
  
  Although it supposed to be the option to have a PDB with no TEMP tablespace that uses the CDB TEMP tablespace, currently this option is not implemented. 
  
  Available on MOS ID 2004595.1
  

12c bug - PDB spfile parameter showing into v$spparameter with wrong con_id

When changing a parameter in spfile of a pluggable database, the change is visible in V$spparameter view for PDB con_id (5), but with wrong con_id (0). This is a known bug:

Bug 16682595  SPFILE / V$SPPARAMETER problems with PDB

SQL> show con_name

CON_NAME
------------------------------
PDB3 

SQL> alter system set ddl_lock_timeout=30 scope=both;

SQL> select con_id, name, value from v$spparameter where name='ddl_lock_timeout' ;

    CON_ID 
----------
NAME
-----------------------------
VALUE
-----------------------------
         0
ddl_lock_timeout
30

Connected to the PDB we check the value of the parameter and the con_id from v$parameter view: 

SQL> show parameter ddl_lock_timeout;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
ddl_lock_timeout                     integer     30

SQL> select con_id, name, value from v$parameter where name='ddl_lock_timeout';

    CON_ID
----------
NAME
----------------------------
VALUE
----------------------------
         5
ddl_lock_timeout
30

12c - Operation 227 succeeded.

C:\Users\oracle12c>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 27 18:59:36 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>
SQL>
SQL>
SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED
PDB3                           MOUNTED

SQL> alter pluggable database all open;

Operation 227 succeeded.

SQL> alter pluggable database pdb1 save state;

Operation 227 succeeded.

SQL> alter pluggable database pdb3 close;

Operation 227 succeeded.

This issue is generated by the client I used (11.2.0.4) to connect to database (12.1.0.2)

EM Cloud Control 12c - ORA-14400: inserted partition key does not map to any partition

When connecting to EM Cloud Control 12c Console using SYSMAN user:

Authentication failed. If problem persists, contact your system administrator.

Can connect to OMR with user SYSMAN and password

Errors in /u02/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/emoms.log

ORA-14400: inserted partition key does not map to any partition

Failed to login using repository authentication for user: SYSMAN

Ensure that the value of job_queue_processes parameter is set to 1000.

Execute the following queries in repository DB:

SQL> exec mgmt_audit_admin.add_audit_partition;

[oracle@rac2 ~]$ cd $OMS_HOME
[oracle@rac2 oms]$ cd bin
[oracle@rac2 bin]$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 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@rac2 bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 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 EM Cloud Control 12c - Change SYSMAN password

1. Stop OMS

[oracle@rac2 ~]$ cd /u02/app/oracle/middleware/oms/bin/
[oracle@rac2 bin]$ ./emctl stop oms
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
[oracle@rac2 bin]$

2. Change sysman password

[oracle@rac2 ~]$ cd /u02/app/oracle/middleware/oms/bin/
[oracle@rac2 bin]$ ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd manager -new_pwd tiger
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.
[oracle@rac2 bin]$


3. Restart OMS

[oracle@rac2 bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

You want to add a datafile to a tablespace with the maximum size of 32G, but instead you hit following situation:

SQL> ALTER TABLESPACE "T_CI" ADD DATAFILE 'E:\APP\ORACLE\ORADATA\MYDB\DATAFILE06.DBF' SIZE 1G AUTOEXTEND ON NEXT 256M MAX
SIZE 32G;
ALTER TABLESPACE "T_CI" ADD DATAFILE 'E:\APP\ORACLE\ORADATA\MYDB\DATAFILE06.DBF' SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE
32G
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of
range


The maximum size is indeed 32G and we can specify it in MB.

SQL> ALTER TABLESPACE "T_CI" ADD DATAFILE 'E:\APP\ORACLE\ORADATA\MYDB\DATAFILE06.DBF' SIZE 1G AUTOEXTEND ON NEXT 256M MAX
SIZE 32767M;

Tablespace altered.



Also, another solution is to set the maximim size in GB, but smaller than 32GB:

SQL> ALTER TABLESPACE "T_CI" ADD DATAFILE 'E:\APP\ORACLE\ORADATA\MYDB\DATAFILE06.DBF' SIZE 1G AUTOEXTEND ON NEXT 256M MAX
SIZE 30G;

Tablespace altered.

SQL Plus easy connect ORA-12504

[oracle@rac2 ~]$ sqlplus sys@localhost:1521/pdborcl.localdomain as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 10:39:12 2015

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

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name:

This error is generated by the fact that in easy connect string the password must be specified also or if the password is not specified the connect string should enclosed in double quotes.

First case, without password specified:

[oracle@rac2 ~]$ sqlplus sys@\"localhost:1521/pdborcl.localdomain\" as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 10:41:30 2015

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SYS:localhost:1521/pdborcl.localdomain SQL>

In this case the \ is the escape character for double quotes.

Second case, with password in connect string:

[oracle@rac2 ~]$ sqlplus sys/Passw0rd@\"localhost:1521/pdborcl.localdomain\" as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 10:41:56 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SYS:localhost:1521/pdborcl.localdomain SQL>

Disable/enable automatic startup of CRS stack

Disable/enable automatic startup of CRS stack:

-bash-4.1# ./crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.
-bash-4.1# ./crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.

In 11.2.0.3 following file is updated during disable/enable of CRS:

-bash-4.1# more /etc/oracle/scls_scr/rac1/root/ohasdstr
disable









Also is posible to directly modify this file using vi and put disable/enable to disable/enable CRS.

Oracle 11.2.0.4 Internal error (ORA-600[kxfpqsod_qc_sod]) detected

This error seems to be generated by DEFAULT parallel server settings on small tables.
After some investigations with Oracle Support we get the following solution:

Run PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports (Doc ID 146040.1) to extract objects with DEFAULT parallel settings and tables that have different DOP settings from their indexes.


Any table less than 1 GB in size should be with DEGREE=1. You can find all the tables and degree in "DOP" html.

Action Plan
__________

1. Make all tables whose size < 1 GB as : Alter Table <schema.table_name> NOPARALLEL;
2. Keep Tables and corresponding index same degree, i.e. if a TABLE has DEGREE 1, then make its all Index as DEGREE 1 OR if a table has DEGREE 4, keep all its Index as DEGREE 4
3. Do not leave any object with DEGREE DEFAULT. Have a correct integer value. As a practice, Tables > 1 GB < 4 GB DEGREE 2, while Tables > 4 GB keep DEGREE 4

Statement to alter degree:

Tables : Alter table <schema.table_name> PARALLEL (DEGREE N) ; ==========> N is the integer value, 2 or 4 or DEFAULT etc.
Index : Alter index <schema.index_name > PARALLEL (DEGREE N) ;

You can view DEGREE for Tables and Index in DBA_TABLES and DBA_INDEXES respectively. 

Oracle listener logfile truncate

Listener logfile location:

$ORACLE_BASE\diag\tnslsnr\<listener_name>\trace

Because on busy OLTP databases listener logfile can grow very fast is usefull to archive logfile and then create new one.

Steps:

lsnrctl set log_status off
mv listener.log listener_old.log
lsnrctl set log_status on