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.