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

Oracle 11.2 DBconsole: ERROR TargetManager: TIMEOUT when compute dynamic properties for target

Errors in emagent.trc

2014-09-27 11:15:08,109 Thread-11772 WARN  command: Job Subsystem Timeout set at 600 seconds
2014-09-27 11:15:08,125 Thread-11772 WARN  upload: Upload manager has no Failure script: disabled
2014-09-27 11:15:08,140 Thread-11772 WARN  metadata: Metric VirtualHosts does not have any data columns
2014-09-27 11:15:08,140 Thread-11772 WARN  metadata: Metric collectSnapshot does not have any data columns
2014-09-27 11:15:08,187 Thread-11772 WARN  metadata: Metric hung_system_traces does not have any data columns
2014-09-27 11:15:08,249 Thread-6148 WARN  TargetManager: Query returned 0 rows (only one expected) for the dynamic property from_cluster
2014-09-27 11:17:08,263 Thread-10144 ERROR TargetManager: TIMEOUT when compute dynamic properties for target db11g
2014-09-27 11:17:08,263 Thread-10144 ERROR TargetManager: nmeetm.c : Target db11g has a failed critical dynamic property
2014-09-27 11:17:08,263 Thread-10144 ERROR TargetManager: Target Manager: Target {db11g, oracle_database} is broken: dynamic properties can not be calculated in time.

Solution:

Update emd.properties file:

# changed from default 120
dynamicPropsComputeTimeout_oracle_database=240

TNS-12637: Packet received failed (high logon time)

DB version Oracle Database 11.2.0.4

Error in alert.log

Fatal NI connect error 12637, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.4.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.4.0 - Production
  Time: 27-SEP-2014 10:23:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12637
    
TNS-12637: Packet received failed
    ns secondary err code: 12532
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (10820) as a result of ORA-609
Sat Sep 27 10:28:09 2014

Error in $ORACLE_HOME\host12345_nsm\sysman\log\emagent.trc

2014-09-27 09:25:28,546 Thread-7180 ERROR fetchlets.oslinetok: Process stdout = starting...0 
starting 1...
starting 2...
got dbversion..started...
 dbversion is 11dbversion is not 9
2014-09-27 09:25:28,546 Thread-7180 ERROR fetchlets.oslinetok: Process stderr = em_error=Could not connect to dbsnmp/(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host12345)(Port=1521))(CONNECT_DATA=(SID=nsm)))
            : ORA-12637: Packet received failed (DBD ERROR: OCIServerAttach)

Solution:

Try to update sqlnet.ora file

DEFAULT_SDU_SIZE=2048