Congifurar Oracle 11gEX

*** Mensajes al instalar Oracle 11g
Destination Folder: C:\oraclexe\
Oracle Home: C:\oraclexe\app\oracle\product\11.2.0\server\
Oracle Base:C:\oraclexe\
Port for 'Oracle Database Listener': 1521
Port for 'Oracle Services for Microsoft Transaction Server': 2030
Port for 'Oracle HTTP Listener': 8080

*** obtenidos del registry ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server
***                 ORACLE_SID=XE

Se creará un usuario "develop" y schema  "DEVELOP" para hacer desarrollo

*** creacion de usuario y schema
https://stackoverflow.com/questions/18403125/how-to-create-a-new-schema-new-user-in-oracle-11g


create user
SQL> create user develop identified by betopass;

verifying

SQL> select username from dba_users;

USERNAME
------------------------------
DEVELOP
SYS
SYSTEM
ANONYMOUS
APEX_PUBLIC_USER
FLOWS_FILES
APEX_040000
OUTLN
DIP
ORACLE_OCM
XS$NULL

USERNAME
------------------------------
MDSYS
CTXSYS
DBSNMP
XDB
APPQOSSYS
HR

17 rows selected.

If you try to login as develop now you would get an error:

ERROR:
ORA-01045: user JOHNY lacks CREATE SESSION privilege; logon denied

SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> grant create session to develop;

Grant succeeded.

To get rid of the user you can drop it:

SQL> drop user johny;

Show already created tablespaces:

SQL> select tablespace_name from dba_tablespaces;
Create tablespace:

SQL> create tablespace johny_tabspace
  2  datafile 'johny_tabspace.dat'
  3  size 10M autoextend on;

Create temporary tablespace (Temporaty tablespace is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure.):

SQL> create temporary tablespace johny_tabspace_temp
  2  tempfile 'johny_tabspace_temp.dat'
  3  size 5M autoextend on;

Create de user develop

SQL> create user develop identified by betopass
  2  default tablespace develop_tabspace
  3  temporary tablespace develop_tabspace_temp;

User created.

Grant some privileges

SQL> grant create session to develop;

Grant succeeded.

SQL> grant create table to develop;

Grant succeeded.

SQL> grant unlimited tablespace to develop;

Grant succeeded.

SQL> grant create sequence to develop;


------------------------------------------------------
---- scripts resumidos y adaptados para usuario develop
create tablespace develop_tabspace
datafile 'develop_tabspace.dat'
size 10M autoextend on;

create temporaty tablespace develop_tabspace_temp
tempfile 'develop_tabspace_temp.dat'
size 5M autoextend on;

create user develop identified by betopass
default tablespace develop_tabspace
temporary tablespace develop_tabspace_temp;

grant create session to develop;
grant create table to develop;
grant unlimited tablespace to develop;
grant create sequence to develop;


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


login as develop and check what privileges he has

SQL> connect
Enter user-name: develop
Enter password:
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE

SQL>
SQL> create table dev_table
  2  (
  3  id int not null,
  4  text varchar2(1000),
  5  primary key (id)
  6  );

Table created.

SQL> insert into dev_table (id, text) values (1,'esto es un dato de prueba');

1 row created.

SQL> select * from dev_table;

        ID
----------
TEXT
--------------------------------------------------------------------------------
         1
esto es un dato de prueba

To get DDL data you can use DBMS_METADATA package that "provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.". (with help from http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm)

For table:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
Result:
 CREATE TABLE "JOHNY"."JOHNY_TABLE"
   (    "ID" NUMBER(*,0) NOT NULL ENABLE,
        "TEXT" VARCHAR2(1000),
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"

For index:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
Result:

  CREATE UNIQUE INDEX "JOHNY"."SYS_C0013353" ON "JOHNY"."JOHNY_TABLE" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"
More information:

DDL

http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_1001.htm
DBMS_METADATA

http://www.dba-oracle.com/t_1_dbms_metadata.htm
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS026
http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm#ADMIN11562
Schema objects

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm
Differences between schema and user

https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
Difference between a user and a schema in Oracle?
Privileges

http://docs.oracle.com/cd/E11882_01/timesten.112/e21642/privileges.htm#TTSQL338
Creating user/schema

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm
http://www.techonthenet.com/oracle/schemas/create_schema.php
Creating tablespace

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734
SQL Plus commands

http://ss64.com/ora/syntax-sqlplus.html


***** ERRORS ***********

---------------------------------
> sqlplus /nolog

SQL> connect / as sysdba

Connected.

SQL> shutdown abort

ORACLE Instance shut down.

SQL> startup nomount

ORACLE Instance started

SQL> alter database mount;

SQL> alter database open;

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

"ORA-00205: error in identifying controlfile"

What is the best way to re-create control file in the correct format and store it in the right place?

Answer:

The oerr utility show this for the ORA-00205 error:

ORA-00205: error in identifying control file, check alert log for more info

Cause: The system could not find a control file of the specified name and size.

Action: Check that ALL control files are online and that they are the same files that the system created at cold start time.

Your database must be shutdown first. (And probably it is not working right now.)

First of all, create pfile from spfile by issuing this command:

SQL> create pfile from spfile;

Then look under the directory of $ORACLE_HOME\database. You will find a newly created pfile. (Its name is init<SID>.ora) Edit newly created pfile to correct controlfile locations. Then issue the command below:

SQL> create spfile from pfile;

After that, database will see controlfiles. But if you also have changed datafile locations; you have to rename datafiles in mount mode.

If you have lost only one of many control files, the procedure is different.  It is a standard practice to have at least two control file (normally three), so you need to identify the surviving control files and replace the missing one with one of the others.

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



You are installing on an unsupported operating system and something could go wrong.

The file initSID.ora, where SID is the instance name (XE in your case), is the initialization parameter file (pfile) which Oracle uses when instance starts to load its runtime settings specified by database administrator.

For some reason the installation process was not successful, and you end up with the default pfile named init.ora.

On startup Oracle instance reads initialization parameters from the following files in the order of appearance

    spfileSID.ora
    spfile.ora
    initSID.ora

where the first two being binary counterparts of pfiles and are called server parameter files (spfiles), because they should reside on the server side in contrast to pfiles which can be on any side.

You can copy init.ora to initXE.ora and set appropriate parameters just to check if your instance starts.

However you would better try reinstalling your database checking the messages shown in the output during installation to see if something goes wrong or using supported OS.

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

---- SOLVED ---

SQL> connect / as sysdba
Connected.

SQL> show parameter contr

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      C:\ORACLEXE\APP\ORACLE\ORADATA
                                                 \XE\CONTROL.DBF
control_management_pack_access       string      NONE
SQL>

SQL> create pfile='C:\Users\Bext\Documents\JPAtutorial\pfile.ora' from spfile;

File created.

----- The pfile.ora -------------------
xe.__db_cache_size=327155712
xe.__java_pool_size=4194304
xe.__large_pool_size=12582912
xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
xe.__pga_aggregate_target=432013312
xe.__sga_target=641728512
xe.__shared_io_pool_size=100663296
xe.__shared_pool_size=184549376
xe.__streams_pool_size=0
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
*.compatible='11.2.0.0.0'
*.control_files='C:\oraclexe\app\oracle\oradata\XE\control.dbf'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\fast_recovery_area'
*.diagnostic_dest='C:\oraclexe\app\oracle\.'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.memory_target=1024M
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
-----End of pfile.ora ----------------------

SQL> startup pfile=C:\Users\Bext\Documents\JPAtutorial\pfile.ora;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260048 bytes
Variable Size             633340848 bytes
Database Buffers          427819008 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SQL>

SQL> create spfile from pfile='C:\Users\Bext\Documents\JPAtutorial\pfile.ora';

File created.

*** SQL utiles
SQL> select username from dba_users;
SQL> select tablespace_name from dba_tablespaces;
SQL> create tablespace develop_tabspace
  2  datafile 'develop_tabspace.dat'
  3  size 10M autoextend on;
SQL> drop tablespace develop_tablespace_temp;
SQL> select file_name, tablespace_name from dba_temp_files;
SQL> select tablespace_name from V$temp_space_header;
SQL> alter database tempfile 'C:\oraclexe\app\oracle\product\11.2.0\server\database\develop_tabspace_temp.dat' drop including datafiles;


Arrancar servicios oracle rápido

   start -> run -> services.msc
 
seleccionar el servicio Oracle y arrancarlos.

Configurar Power Designer

  Cuando se intenta hacer un reverse ingeeniering via JDBC. se reporta "Could not initialize JavaVM!"

Resuelto:

Source:
http://sybase.public.powerdesigner.general.narkive.com/xsK2vFdO/error-could-not-initialize-javavm

Extract:
Recently I had problem with running database reverse engineering via
JDBC. PowerDesigner reported "Could not initialize JavaVM!" problem.
OS: Windows7 64bit
PowerDesigner version: 12.5, 15.3

Solution: It appears that PowerDesigner requires 32bit JRE!!!
1) In menu Tools > General Options > Variables set variable JAVA to location of your java.exe (e.g. c:\java\jre6\bin\java.exe)
2) Add path to file jvm.dll to you PATH system variable (e.g. c:\java
\jre6\bin\client)
3) Create system variable CLASSPATH (if not exists) and add JDBC driver
for your database with full path into it (e.g. c:\drivers\jdbc\ojdbc14.jar)

En mi caso:

Path=C:\oraclexe\app\oracle\product\11.2.0\server\bin;C:\ProgramData\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Brackets\command;C:\Program Files\apache-maven-3.5.0\bin;C:\Program Files\Java\jdk1.7.0_79\bin;C:\Program Files\MySQL\MySQL Utilities 1.6\;C:\Program Files\Git\cmd;C:\Program Files\Heroku\bin;C:\Program Files\Docker Toolbox
CLASSPATH=C:\Program Files (x86)\MySQL\Connector.J 5.1\mysql-connector-java-5.1.41-bin.jar

y Listo!

Configurar jdbcConnectionPool MySQL Glassfish

En una version anterior a glassfish 4.1.1 no me permitia modificar/configurar conection pool de jdbc, en su administrador web del glassfish, simplemente tronaba, se soluciona haciendo el trabajo desde linea de comandos en el asadmin de glassfish


Glassfish Server
glassfish manual and javaee7tutorial manual

== start server ===
asadmin start-domain --verbose

glassfish server port number: default 8080
administrator server's port number: default 4848
administrator user name and password: username admin no password

output also go to the server log:
domain-dir/logs/server.log

== stop server ==
asadmin stop-domain domain1

== start admin console ==
http://localhost:4848/.

== start/stop the java DB Server ==
asadmin start-database
asadmin stop-database

http://www.oracle.com/technetwork/java/javadb/overview/index.html.

== glassfish Create resources administratively ===
asadmin add-resources glassfish-resources.xml




Configuracion de Recursos DataSource, ConnectionPoolDataSource

asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property User=[db_username]:Port=3306:Password=[db_password]:Url="jdbc:mysql://[localhost or ip]:3306/[db_name]" [pool_name]

example

./asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property User=admin:Port=3306:Password=admin:Url="jdbc:mysql://127.0.0.1:3306/\test" test_pool

on practice

./asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property User=root:Port=3306:Password=wsp:Url="jdbc:mysql://localhost:3306/\wsp" test_pool

asadmin> create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource
Enter the value for the jdbc_connection_pool_id operand> test_pool
JDBC connection pool test_pool created successfully.

asadmin> create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property Url="jdbc:mysql://localhost:3306/wsp"
Enter the value for the jdbc_connection_pool_id operand> JPAconnPoolMysql
remote failure: Invalid property syntax, missing property value: mysql
Invalid property syntax, missing property value: mysql

asadmin> create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property Url="jdbc\:mysql\://localhost\:3306/wsp"


EXITO

asadmin> create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.ConnectionPoolDataSource --property Url="jdbc\:mysql\://localhost\:3306/wsp" JPAconnectionPool
Command create-jdbc-connection-pool executed successfully.

Despues desde el Glassfish manager se modifica el ConnectionPool Resource se agregan properties User=root y Password=algoalgo

asadmin> create-jdbc-resource --connectionpoolid JPAconnectionPool jdbc/jpa
JDBC resource jdbc/jpa created successfully.
Command create-jdbc-resource executed successfully.
asadmin>

** Creacion para JPA Cases **
asadmin> create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.ConnectionPoolDataSource --property Url="jdbc\:mysql\://localhost\:3306/JPACases" JPAconnPoolCases

asadmin> create-jdbc-resource --connectionpoolid JPAconnPoolCases jdbc/jpaCases

=================================================================Installing Maven for Netbeans build samples

MAVEN_HOME=C:\Program Files\apache-maven-3.5.0
C:\ProgramData\Oracle\Java\javapath;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Brackets\command;%MAVEN_HOME%\bin;%JAVA_HOME%\bin;C:\Program Files\MySQL\MySQL Utilities 1.6\

Java Persistence API (JPA) con EclipseLink y Eclipse IDE

   Abordamos una análisis de como implementa JPA la librería EclipseLink open source, como desdobla las anotaciones en java en sus entidades hacia una base de datos en su ORM (Object- Relational Model), se hace en el IDE Eclipse ya que es natural su integración.

http://www.eclipse.org/eclipselink/