<a name="top"></a>2022.07.14, 2020.09.14
***
**[Top](#top)**.....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)
***
https://community.oracle.com/tech/developers/discussion/4491549/oracle-form-6i-with-oracle-database-19c
### Developer6i with patch 9 working just fine with OracleXE21c
```
On fresh installation
1) Install oracleXE-21c (I Installed it on Windows 10-64 bit)
2) Install Developer6i and you can install patch 19 or lower (I installed patch 9)
3) configure tnsnames.ora in Developer6i (C:\Developer6i\NET80\ADMIN\tnsnames.ora) as same as on tnsnames.ora in OracleXE(C:\oracle\product\21c\homes\OraDB21Home1\network\admin\tnsnames.ora)
4) connect to database AS SYSDBA and run the following commands
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;
5) Make changes on sqlnet.ora which belong to the database (C:\oracle\product\21c\homes\OraDB21Home1\network\admin\sqlnet.ora) and add the following
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION=8
6) on sqlnet.ora which belong to Developer6i (C:\Developer6i\NET80\ADMIN\SQLNET.ORA) add the following
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Important Note: if you import data from backup then once completed you may need to alter user account again to make Forms Builder able to connect with OracleXE 21c. Hint: I was trying many times to connect after done all settings above and it was failing stating ORA-01017: invalid username/password; logon denied so I alter user
alter USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
```
## 1. Oracle DB 18c instalac intro
J:
### cd J:\app\oraclexe\product\18.0.0\dbhomeXE\bin = [oracle_home]\bin
J:\asg\ORACLEXE\app\oracle\product\11.2.0\server\bin
sqlplus / as sysdba or sqlplus.exe sys/MYPSW@//SSPC2:1521/XE as sysdba
@users - my script, see below
quit or **ALTER SESSION SET container=XEPDB1;**
sqlplus.exe sys/MYPSW@//SSPC2:1521/XEPDB1 as sysdba
@users
quit or ALTER SESSION SET container= CDB$ROOT;
**On PC where 18c XE is installed (if listener port is default = 1521)**
11g xe: Sqlplus hr/hr@xe or ora7
Sqlplus HR/HR@XEPDB1 or Sqlplus hr/hr@xepdb1 **XEPDB1 and ora7 are synonims (plugable DB name)**
conn / as sysdba is same as conn system/MYPSW@sspc2:1521/XEPDB1 as SYSDBA (both conn to SYS user)
Sqlplus HR/HR@sspc2/XEPDB1 - on some other intranet PC
Sqlplus HR/HR@sspc2:1521/XEPDB1
sqlplus system@dbhost.example.com:1521/XEPDB1 - on internet PC
sqlplus /nolog
conn / as sysdba - connects even if **Net Services DB listener lsnrctl** is not running
shutdown abort
startup
lsnrctl allows you to connect to DB over TCP/IP from the same machine or other machines on the network.
```
```
# Exp / Imp Data between DB XE 11.2 and XE 18c
## Exp / Imp Data for non-APEX Users using data pump utilities
Eg if APEX was not used in your 11.2 XE DB.
>https://oracle-base.com/articles/10g/oracle-data-pump-10g also valid for > 10g
>Unlike the original exp and imp utilities all data pump ".dmp" and ".log" files are created on the Oracle server, not the client machine.
>Data pump utils DB 20.1c expdp/impdp (~same since 10g) - Up to 40x faster than Exp/Imp Data. Very high-speed movement of data and metadata from one DB to another for a complete DB or subsets of a DB. In addition to basic import and export functionality **data pump provides a PL/SQL API** and support for external tables. **Data pump like export** main purpose is **data transfer** not recovery, they are not backup tools because :
>1. are not incremental
>2. lose data (everything since the last time you ran an export).
### cd C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin = [oracle_home]\bin
**sqlplus "/ as sysdba"**
>11.2.0.2.0 (last 11g XE)
```
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from dual;
```
>displays : Size in GB 1,81570435
select name, log_mode from v$database;
>displays : **XE, NOARCHIVELOG**
SELECT comp_id, version, status FROM dba_registry;
```
COMP_ID VERSION STATUS
------------------------------ ------------------------------ -----------
APEX 4.0.2.00.09 VALID
XDB 11.2.0.2.0 VALID
CONTEXT 11.2.0.2.0 VALID
CATALOG 11.2.0.2.0 VALID
CATPROC 11.2.0.2.0 VALID
```
<br /><br /><br /><a name="expdp_11gXE"></a>
***
[Top](#top).....**[expdp 11g XE](#expdp_11gXE)**......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)
***
# expdp - Exporting Data from 11g XE DB
Steps:
1. As DBA Win user create directory **J:\apl\expdp_db and share it with Everyone read/write**
on server (not on client system) **for exp_schema DB directory object which is pointer to OS dir.**
2. **Create DB directory object exp_schema**
```
sqlplus / AS SYSDBA
CREATE DIRECTORY exp_schema AS 'J:\apl\expdp_db';
GRANT READ, WRITE ON DIRECTORY exp_schema TO HR;
GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;
```
>Why should we grant DATAPUMP_EXP_FULL_DATABASE role (and DATAPUMP_IMP_FULL_DATABASE) to user (eg hr or to SYSTEM) :
>1. Specify a single schema other than your own or a **list of schema names**
>2. Export additional non-schema object information for each specified schema so that schemas can be re-created at import time : user definitions themselves and all associated system and role grants, user password history...
>This means that the **users which dont have this role can only export own schema**.
>3. Export Schema (user) HR and MERCEDES from 11.2 XE database to dump folder - **from Win CLI as admin** :
3. See below how to create forgoten file in forgoten dir xsl : C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl
forgoten file in not forgoten dir xsl : J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl\kupostdt.xsl (install 11g not xe...)
4. From Win CLI as admin :
cd C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin
### Works : conn hr/hr@SSPC2/xe
### expdp hr/hr@xe DIRECTORY= exp_schema DUMPFILE=exp_schm_hr.dmp LOGFILE=hr_lg.log SCHEMAS=hr
expdp hr/hr@SSPC2/xe DIRECTORY= exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_lg.log SCHEMAS=mercedes
or
expdp hr/hr@xe DIRECTORY=exp_schema DUMPFILE=exp_schm_hr_mercedes.dmp LOGFILE=hr_mercedes_lg.log SCHEMAS=hr,mercedes
Last parameter is "SCHEMAS" - tells data pump that : we want to perform a schema export ee we want to **run data pump expdp utility in schema mode**.
This error was happened because i change character set to WE8MSWIN1252, but when i changed back into default XE : AL32UTF8, expdp can running well (https://willyristanto.blogspot.com/2015/01/error-expdp-ora-39213-ora-31609.html).
Export: Release 11.2.0.2.0 - Production on Sub Ruj 12 13:09:05 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39006: internal error
**ORA-39213: Metadata processing is not available**
Connect AS SYSDBA and** exec dbms_metadata_util.load_stylesheets** to reload stylesheets.
For more information please check **Oracle Doc ID 402242.1**
```
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); NULL=unlimited
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON
DECLARE
cnst_ARE_STYLESHEETS_LOADED CONSTANT VARCHAR2(50) :=
CASE dbms_metadata_util.ARE_STYLESHEETS_LOADED
WHEN TRUE THEN 'STYLESHEETS LOADED'
ELSE 'STYLESHEETS NOT LOADED'
END;
BEGIN
--DBMS_OUTPUT.PUT_LINE(dbms_metadata_util.ARE_STYLESHEETS_LOADED);
DBMS_OUTPUT.PUT_LINE(cnst_ARE_STYLESHEETS_LOADED);
END;
/
SET SERVEROUTPUT OFF
```
Displays :
META:13:51:05.324: **bad stylesheet: kucolumn**
STYLESHEETS NOT LOADED
PL/SQL procedure successfully completed.
**exec dbms_metadata_util.load_stylesheets**
Displays :
ERROR at line 1:
**ORA-31609: error loading file "kucolumn.xsl" from file system directory**
"C:\oraclexe\app\oracle\product\11.2.0\server/rdbms/xml/xsl" **$ORACLE_HOME/rdbms/xml/xsl**
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2397
**J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl** copy to same XE dir and **add one xsl more** :
```
<?xml version="1.0"?>
<!--
NAME
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl
DESCRIPTION
XSLT stylesheet for XML => DDL conversion of ku$_post_data_table_t ADTs
NOTES
Do NOT modify this file under any circumstance. Copy the file
if you wish to use this stylesheet with an external XML/XSL parser
MODIFIED MM/DD/YY
sdavidso 06/08/09 - new stylesheet - for minimize records_per_bock
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!-- Import required scripts -->
<xsl:import href="kucommon.xsl"/>
<!-- Top-level parameters -->
<xsl:param name="PRETTY">1</xsl:param>
<xsl:param name="SQLTERMINATOR">1</xsl:param>
<!-- params for parse -->
<xsl:param name="PRS_DDL">0</xsl:param>
<xsl:param name="PRS_DELIM">\{]`</xsl:param>
<xsl:param name="PRS_VERB">0</xsl:param>
<xsl:param name="PRS_OBJECT_TYPE">0</xsl:param>
<xsl:param name="PRS_SCHEMA">0</xsl:param>
<xsl:param name="PRS_NAME">0</xsl:param>
<xsl:param name="PRS_GRANTEE">0</xsl:param>
<xsl:param name="PRS_GRANTOR">0</xsl:param>
<xsl:param name="PRS_BASE_OBJECT_TYPE">0</xsl:param>
<xsl:param name="PRS_BASE_OBJECT_SCHEMA">0</xsl:param>
<xsl:param name="PRS_BASE_OBJECT_NAME">0</xsl:param>
<xsl:template match="POST_DATA_TABLE_T">
<!-- *******************************************************************
Template: POST_DATA_TABLE_T - template for table attributes
which can only be set after table data is loaded.
******************************************************************** -->
<xsl:call-template name="DoParse">
<xsl:with-param name="Verb">ALTER</xsl:with-param>
<xsl:with-param name="ObjectType">POST_DATA_TABLE</xsl:with-param>
<xsl:with-param name="SchemaNode" select="SCHEMA_OBJ/OWNER_NAME"/>
<xsl:with-param name="NameNode" select="SCHEMA_OBJ/NAME"/>
</xsl:call-template>
<xsl:if test="(SPARE1 mod 65536)>=32768">
<xsl:text> ALTER TABLE </xsl:text>
<xsl:apply-templates select="SCHEMA_OBJ"/>
<xsl:text>MINIMIZE RECORDS_PER_BLOCK</xsl:text>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
```
### SQL> exec dbms_metadata_util.load_stylesheets
PL/SQL procedure successfully completed.
PS C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin> expdp hr/hr@xe DIRECTORY= exp_schema DUMPFILE=exp_schm_hr.dmp LOGFILE=hr_lg.log SCHEMAS=hr
ORA-39070: Unable to open the log file.
If we are on a Windows machine, make sure that both listener and DB have been started with same username.
```
declare
fle utl_file.file_type;
begin
fle := utl_file.fopen ('exp_schema', 'test.txt', 'w');
utl_file.put_line(fle, 'test');
utl_file.fclose(fle);
end;
/
```
ORA-29280: invalid directory path
Oracle cant write to that directory at all, probably because of Windows file permissions. Check which Windows user(s) the Oracle services are running as, and change the folder permissions to allow them write access.
You can also disable the logfile completely by using NOLOGFILE=Y, but you'll have to monitor the impdp output on your console, because it won't get saved anywhere else. THEN err :
ORA-31641: unable to create dump file "J:\apl\expdp_db\exp_schm_hr.dmp"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
See other possible err : https://kmpdba.wordpress.com/2012/05/02/ora-39213-metadata-processing-is-not-available/
There is a 2011 discussion about this at https://community.oracle.com/thread/2278841. It says you have to copy the directory $ORACLE_HOME/rdbms/xml/xsl from a working installation. **No xsl dir under dml in my 11g XE dirs**, same 11g on Oracle linux
/u01/app/oracle/product/11.2.0/xe/rdbms/xml/.
/etc/hosts file looks like this :
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
<br /><br /><br />
**Deinstall DB XE 11.2 if installation of 18c XE is planned on same system.** See Deinstalling DB XE Software in DB XE Installation Guide 11g Release 2 (11.2) for Microsoft Windows.
**Install DB 18c XE**
<br /><br /><br /><a name="impdp_18cXE"></a>
***
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......**[impdp 18c XE](#impdp_18cXE)**.....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)
***
# Importing data to (18c) XE DB (or to 11g )
1. See **expdp - Exporting Data from 11g XE DB :** cre J:\apl\expdp_db (or \dump_folder) OS dir
2. forgoten file in forgoten dir xsl : C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl (**11g XE deinstalled to install 11c XE**)
forgoten file in not forgoten dir xsl : J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl\kupostdt.xsl
3.
```
sqlplus / AS SYSDBA
--not for 11g :
ALTER SESSION SET CONTAINER=XEPDB1;
--
--Create dir. object exp_schema and grant READ and WRITE privileges on it to the HR or SYSTEM user.
CREATE DIRECTORY exp_schema AS 'J:\apl\expdp_db';
GRANT READ, WRITE ON DIRECTORY exp_schema TO HR;
GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;
GRANT DATAPUMP_IMP_FULL_DATABASE TO HR;
```
4. Import data to 18c XE (or to 11g) database from dump folder :
cd J:\app\ss\product\11.2.0\dbhome_1\bin
#### impdp hr/hr@localhost:1522/orcl DIRECTORY=exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_imp_lg.log EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"
impdp system/system_password@localhost:listnerport/xepdb1 **full=Y EXCLUDE=**SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=exp_schema dumpfile=DB11G.dmp logfile=impdpDB11G.log
was exported so :
expdp hr/hr@SSPC2/xe DIRECTORY= exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_lg.log SCHEMAS=mercedes
" See **J:\apl\expdp_db\mercedes_imp_lg.log**
5. conn mercedes/m1@localhost:1522/orcl
You can ignore the following errors:
1. ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error
2. ORA-31685: Object type USER:"SYS" failed due to insufficient privileges
3. ORA-39083: Object type PROCACT_SYSTEM failed to create with error
4. ORA-01917: user or role 'APEX_040000' does not exist
5. ORA-31684 "already exists" errors
<br /><br /><br />
## Data pump imp/exp PL/SQL API
select * from dba_datapump_jobs; --to monitor current jobs.
Along with the data pump utilities Oracle provide an PL/SQL API. The following is an example of how this API can be used to perform a **schema export**.
```
DECLARE
l_dp_handle NUMBER;
BEGIN
-- Open an schema export job.
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'SCOTT_EXPORT',
version => 'LATEST');
-- Specify the dump file name and directory object name.
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.dmp',
directory => 'TEST_DIR');
-- Specify the log file name and directory object name.
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'expdpSCOTT.log',
directory => 'TEST_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
-- Specify the schema to be exported.
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= ''SCOTT''');
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
```
Once the job has started the status can be checked using `system@db10g> select * from dba_datapump_jobs;`
The following is an example of how this API can be used to perform a **schema import with a schema remap operation**.
```
DECLARE
l_dp_handle NUMBER;
BEGIN
-- Open an schema import job.
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'SCOTT_IMPORT',
version => 'LATEST');
-- Specify the dump file name and directory object name.
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.dmp',
directory => 'TEST_DIR');
-- Specify the log file name and directory object name.
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'impdpSCOTT.log',
directory => 'TEST_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
-- Perform a REMAP_SCHEMA from SCOTT to SCOTT2.
DBMS_DATAPUMP.metadata_remap(
handle => l_dp_handle,
name => 'REMAP_SCHEMA',
old_value => 'SCOTT',
value => 'SCOTT2');
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
```
<br /><br /><br /><a name="deinst_11gXE"></a>
***
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....**[Deinstall 11g XE](#deinst_11gXE)**
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)
***
# Deinstall DB XE (11.2)
if intend inst. some other ver. XE on same PC
Export data files (Schemas) before you deinstall DB XE software and database.
Caution: When you deinstall Oracle Database XE, all components, including data files, the database, and the software, are removed.
### Deinstalling method Using User Interface
Use **Revo (Add or Remove Programs)** to deinstall Oracle Database XE:
1. In Windows Control Panel, select Add or Remove Programs.
2. Select Oracle Database 18c Express Edition -> Click Change/Remove or Uninstall.
Alternatively, you can use following msiexec command to perform the deinstall:
msiexec /x {C220B7FD-3095-47FC-A0C0-AE49DE6E320A}
### Deinstalling method Using Silent Mode
If the downloaded software is available, you can use setup.exe to deinstall Oracle Database XE.
For example, if you downloaded and extracted the files to a directory xe_temp ,
enter the following command to deinstall Oracle Database XE:
`setup.exe /s /x /v"/qb /Lv c:\test1.log"`
If you do not have the downloaded software ( setup.exe ) you can deinstall using:
msiexec /qn /x {C220B7FD-3095-47FC-A0C0-AE49DE6E320A}
You can replace /qn with /qb to display the progress bar.
Note:
1. Silent deinstall runs asynchronously; you can check the log file for the completion status.
2. After deinstall is successfully completed, the following message is available in the log file:
MSI (s) (8C:C4) [Timestamp] : Windows Installer removed the product. Product Name: Oracle Database 18c Express Edition. Product Version: 18.4.0.0.0. Product Language: [Current Language ID]. Manufacturer: Oracle Corporation. Removal success or error status: 0.
<br /><br /><br />
## Unplug / Plug - Migrating Data and Apps in and out of your XE DB
Export tables and data from one XE installation and import them into another.
Oracle recommends you use 11g Release 2 and higher to export your data.
Migrating fromDB XE 18c to Oracle Database **Enterprise** Edition 18c is supported via the **unplug, plug scenario only !**. You can unplug a PDB from XE 18c DB and plug it into another Enterpise Edition 18c DB of the **same release**.
Migrating from XE 18c to any of the other 18c is not supported.
Upgrade from XE 11.2 to XE 18c is not supported.
### Procedure to Unplug a PDB from 18c XE and Plug into 18c EE
A Pluggable DB (PDB) created in XE 18c can be migrated to another Multitenant Container DB (CDB) Enterprise Edition (EE) 18c.
The standard plug compatibility requirements apply. See **Oracle Multitenant Administrators Guide** for more info about general prerequisites for PDB
creation.
For example, you want to unplug your xepdb1 out of the XE 18c XE CDB and plug it in another 18c EE CDB.
1. Use SQLPlus to close the PDBs before they can be unplugged.
>Note: The pdb database may not have been opened, so you may receive an error that the PDB is already closed.
sqlplus / as sysdba
ALTER PLUGGABLE DATABASE xepdb1 CLOSE IMMEDIATE;
2. Unplug closed PDB and specify path and name of XML file :
ALTER PLUGGABLE DATABASE xepdb1 UNPLUG INTO '\tmp\xepdb1.xml';
3. Drop the closed PDB and keep the data files :
DROP PLUGGABLE DATABASE xepdb1 KEEP DATAFILES;
4. Log in to your EE environment. The xepdb1 metadata file should be available from that system.
5. Run DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB.
```
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '\tmp\xepdb1.xml',
pdb_name => 'XEPDB1')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
```
6. If the PDB is compatible with the CDB, you can plug xepdb1 into the 18c EE database.
Use data files of the unplugged PDB to plug the PDB into another CDB without any copy.
CREATE PLUGGABLE DATABASE newxepdb USING '\tmp\xepdb1.xml' NOCOPY TEMPFILE REUSE;
7. Verify the status and open mode of the plugged PDB.
SELECT pdb_name, STATUS FROM cdb_pdbs WHERE pdb_name='NEWXEPDB';
SELECT OPEN_MODE FROM V$PDBS WHERE NAME='NEWXEPDB';
8. If PDB is in MOUNTED state, then open the PDB.
ALTER PLUGGABLE DATABASE newxepdb OPEN;
9. This example assumes:
1. The XML file accurately describes the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause or SOURCE_FILE_DIRECTORY clause is not required.
2. The files are in the correct location. Therefore, NOCOPY is included.
3. Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
4. A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required.
10. Run catalog, catproc and utrlp on the plugged in NEWXEPDB.
sqlplus / as sysdba
**SHOW PDBS;**
```
CON_ID CON_NAME OPEN MODE RESTRICTED
------ ---------------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 NEWXEPDB READ WRITE YES
--
ALTER SESSION SET CONTAINER=newxepdb;
--
SPOOL catalog.log
@%ORACLE_HOME%\rdbms\admin\catalog
SPOOL OFF;
--
SPOOL catproc.log
@%ORACLE_HOME%\rdbms\admin\catproc
SPOOL OFF;
--
SPOOL utlrp.log
@%ORACLE_HOME%\rdbms\admin\utlrp
SPOOL OFF;
--
SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN COMP_NAME HEADING COMP_NAME FORMAT A30
COLUMN STATUS HEADING paSTATUSssword FORMAT A15
COLUMN VERSION HEADING VERSION FORMAT A30
SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY;
--
COMP_NAME STATUS VERSION
------------------------------ --------------- ------------------------------
Oracle Application Express VALID 4.0.2.00.09
Oracle XML Database VALID 11.2.0.2.0
Oracle Text VALID 11.2.0.2.0
Oracle Database Catalog Views VALID 11.2.0.2.0
Oracle Database Packages and T VALID 11.2.0.2.0
ypes
--
COMP_NAME STATUS VERSION
----------------------------------- ------------ ------------
Oracle Database Catalog Views VALID 18.0.0.0.0
Oracle Database Packages and Types VALID 18.0.0.0.0
Oracle Real Application Clusters OPTION OFF 18.0.0.0.0
JServer JAVA Virtual Machine VALID 18.0.0.0.0
Oracle XDK VALID 18.0.0.0.0
Oracle Database Java Packages VALID 18.0.0.0.0
OLAP Analytic Workspace VALID 18.0.0.0.0
Oracle XML Database VALID 18.0.0.0.0
Oracle Workspace Manager VALID 18.0.0.0.0
Oracle Text VALID 18.0.0.0.0
Oracle Multimedia VALID 18.0.0.0.0
Spatial VALID 18.0.0.0.0
Oracle OLAP API VALID 18.0.0.0.0
Oracle Label Security VALID 18.0.0.0.0
Oracle Database Vault VALID 18.0.0.0.0
```
SELECT OWNER, OBJECT_NAME FROM DBA_INVALID_OBJECTS WHERE STATUS = 'INVALID';
no rows selected
Note:
1. See Plugging an Unplugged Pluggable Database for more information about plugging in an unplugged pluggable database.
2. See Oracle Multitenant Administrator?s Guide for more information about plugging in an unplugged PDB.
### Backup DB 11g XE - 3 ways
To protect against data loss.
ARCHIVELOG mode disabled - main benefit of **consistent backup** is that **DB does not require recovery after it is restored** - - you can open database immediately. All committed changes are written to the datafiles during shutdown, so the **datafiles are in a transaction-consistent state** .
1. In **SQLdeveloper** in the **View menu select DBA to get access to the dba tools like RMAN and DATAPUMP**. Very nice tool. Don't forget that the backup files are** written by Ora DB server processes**. This means that the **backups are written on DB server, not on your client**. Also, currently it **only generates a backup script. You should run the generated script using RMAN**.
2. An other way to run and configure the backup is by using **dbconsole or grid control**.
3. **datapump** - to transfer data as it was during export. Might be usefull for test sets that have to be refreshed with a specific version of the data. Not really usefull if you want to recover **production transactions to last moment** before disaster stroke.
### ??? Win icon -> type "database" -> Backup database
Warning: Log archiving **(ARCHIVELOG mode) is currently disabled**. If you restore the database from this backup, any transactions that take place between this backup and the next backup will be lost. It is recommended that you enable ARCHIVELOG mode before proceeding so that all transactions can be recovered upon restore. See the section
'Enabling ARCHIVELOG Mode...' in the online help for instructions. Backup with log archiving disabled will shut down and restart DB. Are you sure [Y/N]? Y
Backup in progress...
Recovery Manager: Release 11.2.0.2.0 - Production on Pet Ruj 11 16:30:17 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2913134267)
RMAN> set echo on;
2> shutdown immediate;
3> startup mount;
4> configure controlfile autobackup format for device type disk clear;
5> configure retention policy to redundancy 2;
6> configure controlfile autobackup on;
7> sql "create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SPFILE2INIT.ORA'' from spfile";
8> backup as backupset device type disk database;
9> configure controlfile autobackup off;
10> alter database open;
11> delete noprompt obsolete;
12>
echo set on
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
...
sql statement: create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SPFILE2INIT.ORA'' from spfile
**Starting backup at 11.SEPT.20**
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
```
input datafile file number=00002 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
input datafile file number=00003 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
channel ORA_DISK_1: starting piece 1 at 11.09.20
channel ORA_DISK_1: finished piece 1 at 11.09.20
piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\BACKUPSET\2020_09_11\O1_MF_NNNDF_TAG20200911T163048_HOQ2JRZ2_.BKP tag=TAG20200911T163048 comment=NONE
```
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 11.09.20
**Starting Control File and SPFILE Autobackup at 11.09.20 **
piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2020_09_11\O1_MF_S_1050856233_HOQ2KLBG_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 11.09.20
...
database opened
no obsolete backups found
Recovery Manager complete.
**Backup of the database succeeded. **
Log file is at C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\OXE_BACKUP_CURRENT.LOG.
Press any key to continue . . .
### C:\oraclexe\app\oracle\fast_recovery_area\XE
Datafile location : C:\oraclexe\app\oracle\oradata\XE
Control file location :
https://dba.stackexchange.com/questions/179989/oracle-11g-rman-restore-with-another-database-name-from-backup-in-windows-platfo
Copy full backups, archivelogs, and autobackup folders **from old server's fast recovery area**. Note that you only need to go back to the most recent full backup, no need to get weeks worth of files.
J:\apl\possys6\instlcija\instalac\1_instal_F6i_R6i\1_f6i\STEP05_11g32_64bit
<br /><br /><br /><a name="inst_18cXE"></a>
***
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....**[Install 18c XE](#inst_18cXE)**.....[ Links](#links)
***
# INST / DEINST DB 18c XE
release 18.0.0.0.0 ver 18.4.0.0.0 from OracleXE184_Win64.zip
J:
cd J:\app\ss\product\18.0.0\dbhomeXE\bin
sqlplus /nolog or
sqlplus / as sysdba
ALTER SESSION SET container=XEPDB1;
**ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;**
grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;
grant CONNECT,DBA,RESOURCE to MERCEDES IDENTIFIED BY M1;
**conn HR/HR@XEPDB1 -- case sensitive !!**
**conn MERCEDES/M1@XEPDB1**
**select tname from tab; or SELECT table_name FROM user_tables**
exit
or
@users
quit or ALTER SESSION SET container=CDB$ROOT;
conn HR/HR@localhost:1521/XEPDB1
See **COUNTRIES ORA-01410: invalid ROWID** in this txt.
## Importing data to (18c) XE DB (or to 11g )
1. See **expdp - Exporting Data from 11g XE DB :** cre J:\apl\expdp_db (or \dump_folder) OS dir
2. forgoten file in forgoten dir xsl : C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl (**11g XE deinstalled to install 11c XE**)
forgoten file in not forgoten dir xsl : J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl\kupostdt.xsl
J:\app\ss\product\18.0.0\dbhomeXE\rdbms\xml\xsl
3.
```
sqlplus / AS SYSDBA
--not for 11g :
ALTER SESSION SET CONTAINER=XEPDB1;
@users
--
--Create dir. object exp_schema and grant READ and WRITE privileges on it to the HR or SYSTEM user.
CREATE DIRECTORY exp_schema AS 'J:\apl\expdp_db';
GRANT READ, WRITE ON DIRECTORY exp_schema TO HR;
GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;
GRANT DATAPUMP_IMP_FULL_DATABASE TO HR;
exit
```
4. Import data to 18c XE (or to 11g) database from dump folder :
cd J:\app\ss\product\18.0.0\dbhomeXE\bin (J:\app\ss\product\11.2.0\dbhome_1\bin)
#### impdp HR/HR@//SSPC2:1521/XEPDB1 DIRECTORY=exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_imp_lg.log EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"
impdp system/system_password@localhost:listnerport/xepdb1 **full=Y EXCLUDE=**SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=exp_schema dumpfile=DB11G.dmp logfile=impdpDB11G.log
was exported so :
expdp hr/hr@SSPC2/xe DIRECTORY= exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_lg.log SCHEMAS=mercedes
"See **J:\apl\expdp_db\mercedes_imp18c_lg.log**
5. conn mercedes/m1@localhost:1521/XEPDB1
**select tname from tab; or SELECT table_name FROM user_tables**
You can ignore the following errors:
1. ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error
2. ORA-31685: Object type USER:"SYS" failed due to insufficient privileges
3. ORA-39083: Object type PROCACT_SYSTEM failed to create with error
4. ORA-01917: user or role 'APEX_040000' does not exist
5. ORA-31684 "already exists" errors
In Win CLI with admin rights we may restart listener : lsnrctl reload
lsnrctl start
#### PS J:\app\ss\product\18.0.0\dbhomeXE\bin> lsnrctl status
```
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 14-SEP-2020 23:06:10
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sspc2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 - Production
Start Date 14-SEP-2020 20:18:05
Uptime 0 days 2 hr. 48 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File J:\app\ss\product\18.0.0\dbhomeXE\network\admin\listener.ora
Listener Log File J:\app\ss\product\18.0.0\diag\tnslsnr\sspc2\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sspc2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=J:\APP\SS\PRODUCT\18.0.0\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "703df018f8fa4675abacf4445a795702" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
PS J:\app\ss\product\18.0.0\dbhomeXE\bin>
```
### Optionally update tnsnames.ora file
in s/w base dir/network/admin/tnsnames.ora file to add an connection alias for xepdb1 as below, for me that directory is :
`J:\app\ss\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora`
### tnsnames.ora - XE and ora7 are not same in 18c (are same in 11g)
```
# tnsnames.ora Network Configuration File: J:\app\ss\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#
#name of Container DB (privileged users like sys)
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
#name of default pluggable DB created during install. (all sample schemas, APEX apps...) :
XEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xepdb1)
)
)
#
ora7 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xepdb1)
)
)
#
LISTENER_XE =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
#
#
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
```
In Win **CLI with admin rights** restart listener : **Lsnrctl reload**
lsnrctl status
lsnrctl start
conn system/MYPSW
**NO NEED :** ALTER USER system IDENTIFIED BY MYPSW ACCOUNT UNLOCK
ALTER USER system IDENTIFIED BY MYPSW
# On Windows 10 pro 64 bit Ver : 10.0.19041
### L:\2_knjige\oracle\2020_oracle-database_19\2019_18c_XE_instalac_guide-ms_win.pdf
>Tutorial (also on local PC) : https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/
https://www.aflorestorres.com/2019/03/install-oracle-database-18c-express-and.html
https://dbahelp2018.wordpress.com/install-oracle-18c-xe-on-windows-10/
>Big articles https://dbvisit.com/blog/oracle-database-18c-express-edition-getting-started-part-4 Linux, docker
>If you don?t already have an account you will need to create one to download the s/w:
>https://profile.oracle.com/myprofile/account/create-account.jspx
Download : OracleXE184_Win64.zip
**https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html**
and unzip in `J:\apl\possys6\instlcija\ora18c\OracleXE184_Win64`
**Take ownership of OracleXE184_Win64 folder**
1. expdp schemas from 11g XE, **Deinstall 11g XE (615 MB)** , check environment variables - Win key and type env - **delete any ORACLE_HOME env. var.**
2. **run as admin** `J:\apl\possys6\instlcija\ora18c\OracleXE184_Win64\setup.exe`
1. DESTIN_DIR : `J:\app\ss\product\18.0.0\`
2. ORACLE_HOME : DESTIN_DIR\\dbhomeXE : `J:\app\ss\product\18.0.0\dbhomeXE`
3. ORACLE_BASE : DESTIN_DIR
3. When prompted, set database password to use for SYS, SYSTEM and PDBAADMIN accounts
alter user username identified by password;)
4. Verify and remember the Oracle Home and other locations. Note that the spaces are not allowed in directory names.
5. Next **hit Install** to get it going
Windows defender will alert for java firewall rule, I've changed this to **'Private networks**, such as my home or work network'
### 'Note details for our XE DB:
Server: localhost
Port: 1521
Multitenant container DB : localhost:1521 **Container DB will be called XE**
Pluggable DB : XEPDB1 localhost:1521/XEPDB1
Enterprise Express URL : https://localhost:5500/em
Click Finish, to close installer
<br /><br />
## Check DB in GUI Enterprise Manager Express
https://localhost:5500/em
User Name: **sys**
Password: ...
Container name: **Leave blank** (Enter a PDB name if you want to access a specific PDB or leave it blank to access the root container. )
as sysdba: tick box
### Making Oracle Database EM Express Available to Remote Clients
After you install Oracle Database XE, EM Express is only available from the local server, it cannot be accessed remotely.
To make EM Express available to remote clients, start SQLPlus, log in as SYSTEM , and execute following procedure. From CLI run following commands. In the commands, replace "oracle_home" with path to your Oracle Home; see 18c XE Installation Guide Table 5-1 for more details about Oracle Home.
```
J:
cd J:\app\oraclexe\product\18.0.0\dbhomeXE\bin
sqlplus system or sqlplus /nolog sqlplus / as sysdba
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
```
## Test statements, possible strange errors if not take ownership
Specified value of MEMORY_TARGET is too small, needs to be at least 2048 M
SHOW PARAMETER PFILE;
SHOW PARAMETER MEMORY_TARGET
ALTER SYSTEM SET MEMORY_TARGET='2048 M' SCOPE=spfile;
SHUTDOWN --**ORA-01034 is simple error, it means that your DB is down! ** !!!!!!!!!!!!!!!!!!!!!!!!!!!
NOT POSSIBLE : STARTUP
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
You can *edit* pfile you created to correct errors you have. Then once your instance is started, you can create an spfile out of that pfile.
J:
cd J:\app\ss\product\18.0.0\dbhomeXE\bin
sqlplus /nolog or
sqlplus / as sysdba
--outputs : Connected to an idle instance.
**create pfile = 'c:\temp\init.ora' from spfile; **
--Then you edit file see **\*.memory_target=2048 M** and use that to start
**startup pfile=c:\temp\init.ora**
--once your database is open
**create spfile from pfile='c:\temp\init.ora';**
### C:\temp\INIT.ORA
ORA-32006: SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated
```
xe.__data_transfer_cache_size=0
xe.__db_cache_size=1107296256
xe.__inmemory_ext_roarea=0
xe.__inmemory_ext_rwarea=0
xe.__java_pool_size=16777216
xe.__large_pool_size=33554432
xe.__oracle_base='J:\app\oraclexe\product\18.0.0'#ORACLE_BASE set from environment
xe.__pga_aggregate_target=536870912
xe.__sga_target=1610612736
xe.__shared_io_pool_size=83886080
xe.__shared_pool_size=352321536
xe.__streams_pool_size=0
*.audit_file_dest='J:\app\oraclexe\product\18.0.0\admin\XE\adump'
*.audit_trail='db'
*.compatible='18.0.0'
*.control_files='J:\app\oraclexe\product\18.0.0\oradata\XE\control01.ctl','J:\app\oraclexe\product\18.0.0\oradata\XE\control02.ctl'
*.db_block_size=8192
*.db_name='XE'
*.diagnostic_dest='J:\app\oraclexe\product\18.0.0'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_XE'
*.memory_target=2048 M
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=1536m
*.undo_tablespace='UNDOTBS1'
```
### ...dbhomeXE/dbs/init.ora
```
# J:\app\oraclexe\product\18.0.0\dbhomeXE\dbs\init.ora
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time) J:/app/oraclexe/product/18.0.0
#db_name='ORCL'
db_name='XE'
#memory_target=1G
memory_target=2048 M
processes = 150
#audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_file_dest='J:/app/oraclexe/product/18.0.0/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='J:/app/oraclexe/product/18.0.0/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='J:/app/oraclexe/product/18.0.0'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
```
set oracle_sid=XE =DATABASE NAME
sqlplus /nolog
conn sys/SYSPSW as sysdba
shutdown abort
startup
<br /><br /><br /><br />
```
select sys_context( 'userenv', 'current_schema' ) from dual;
prints SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------
SYS
conn hr/hr@xepdb1
select sys_context( 'userenv', 'current_schema' ) from dual;
prints SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------
HR
```
You cannot change your identity in a **definers rights procedure**, it is fixed AT COMPILE time by definition.
### How can I in procedure query system to change and return current schema I am in?
https://asktom.oracle.com/pls/asktom/asktom.search?tag=how-to-get-current-schema
start J:\app\oraclexe\product\18.0.0\dbhomeXE\demo\schema\human_resources\hr_main.sql
After DB 18c install. 35 rows selected then 36 :
PDBADMIN 101 2020-08-25 00:44:00
SCOTT, HR and other sample schemas are in PDB and not in CDB. So, you need to login as sysdba into PDB :
**sqlplus SYS/password@XEPDB1 AS SYSDBA**
show con_name
sho user
show pdbs
**ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;**
grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;
**conn HR/HR@XEPDB1**
**select tname from tab;**
exit
**ALTER USER system IDENTIFIED BY MYPSW ACCOUNT UNLOCK**
**ALTER USER system IDENTIFIED BY MYPSW**
sqlplus scott/tiger@pdborcl
SQL> sho user;
USER is "SCOTT"
SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN username HEADING username FORMAT A30
COLUMN password HEADING password FORMAT A15
select username, password from dba_users order by username ;
http://www.rebellionrider.com/how-to-unlock-hr-user-in-oracle-database-18c-by-manish-sharma/ By Manish Sharma - January 5, 2019
Unlocking hr (or any) schema in DB 18c :
1. Finding out container ID of pluggable DB which contains our schema
--Conn to container DB CBD$ROOT :
**Sqlplus / as sysdba**
--Query v$PDBS dynamic performance view to see **container names and IDs of all pluggable DBs** created inside root container :
SELECT name, con_id FROM v$pdbs;
```
NAME CON_ID (container ID)
------------------ ------------------------------
PDB$SEED 2 seed pluggable DB
**XEPDB1** 3 **name of default pluggable DB** created during installation (ORCLPDB) - **has all our sample (test) schemas**.
```
PDB$SEED is the seed pluggable DB & a system supplied template which container DB uses for creating user defined pluggable DBs.
2. Finding out **service name of pluggable DB** - usually, pluggable DB and its service shares the same name, but check it:
SELECT name **Service_Name** FROM v$active_services WHERE con_id =3; --displays **xepdb1**
3. Creating an entry in TNSNAMES.ora network configuration file inside "Network\\admin" folder inside our DB_HOME :
J:\app\oraclexe\product\18.0.0\dbhomeXE\network\admin
PS C:\WINDOWS\system32> echo %dbhomeXE%
%dbhomeXE%
```
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 25.8.2020. 0:16 sample
-a---- 25.8.2020. 0:19 658 listener.ora
-a---- 25.8.2020. 0:19 464 sqlnet.ora
-a---- 25.8.2020. 0:19 263 sqlnet20082512AM1922.bak
-a---- 25.8.2020. 0:38 625 tnsnames.ora <======
```
Using our pluggable database name and its corresponding service we need to create an entry for our listener - using NETCA utility or we can do it manually (is faster).
### tnsnames.ora - see XE and ora7 are not same in 18c (are same in 11g)
```
#J:\app\oraclexe\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora
#tnsnames.ora Network Configuration File:
#Generated by Oracle configuration tools.
#name of Container DB (privileged users like sys)
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
#name of default pluggable DB created during install. (all sample schemas, APEX apps...) :
XEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xepdb1)
)
)
ora7 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xepdb1)
)
)
LISTENER_XE =
(ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
```
In Win **CLI with admin rights** restart listener : **Lsnrctl reload**
lsnrctl status
lsnrctl start
4. Switching from Container DB XE CDB$ROOT to Pluggable DB XEPDB1
High privileged users like sys are placed inside a container DB and all sample schemas are in default pluggable DB, which means in order to unlock them we need to switch to XEPDB1.
**ALTER SESSION SET container=XEPDB1;**
5. Unlocking HR user
**SELECT name, open_mode FROM v$pdbs;**
```
NAME OPEN_MODE
---------------------------- -------------------
XEPDB1 READ WRITE
```
If open mode is **mounted** - means DB isn't open. To open your pluggable DB :
ALTER PLUGGABLE DATABASE open;
Once you've done all the above steps and configured your DB properly then :
**ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;**
grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;
exit
Sqlplus HR/HR@localhost:1521/XEPDB1
show con_name
--(CDB$ROOT) XEPDB1
sho user
--(SYS) USER is "HR"
### COUNTRIES ORA-01410: invalid ROWID
```
conn hr/hr@ora7
set linesize 150
select COUNTRIES.*, rowidtochar(ROWID) as ROWID_ from COUNTRIES;
--err: ORA-01410: invalid ROWID
drop table t;
create table t as (select COUNTRIES.* from COUNTRIES);
select t.*, rowidtochar(ROWID) as ROWID_ from t;
--treba drop tablice zbog ORGANIZATION INDEX, pa ovo ne ide:
--alter table LOCATIONS disable constraint LOC_C_ID_FK;
-- delete COUNTRIES;
alter table LOCATIONS drop constraint LOC_C_ID_FK;
drop table COUNTRIES;
CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
--ORGANIZATION INDEX <-- zbog ovoga javi ORA-01410: invalid ROWID:
;
ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;
insert into COUNTRIES (select t.* from t);
--alter table LOCATIONS enable constraint LOC_C_ID_FK;
alter table LOCATIONS add CONSTRAINT "LOC_C_ID_FK"
FOREIGN KEY ("COUNTRY_ID") REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ;
--commit;
select COUNTRIES.*, rowidtochar(ROWID) as ROWID_ from COUNTRIES;
prompt kad tablica nije ORGANIZATION INDEX gornji select radi !!
```
## Not working, Why error ?
Sqlplus HR/HR@//sspc2:1521/XEPDB1
conn HR/HR@//sspc2:1521/XEPDB1
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
### sqlnet.ora SQLNET.ALLOWED_LOGON_VERSION=11
```
#J:\app\oraclexe\product\18.0.0\dbhomeXE\NETWORK\ADMIN\sqlnet.ora
#sqlnet.ora Network Configuration File:
#Generated by Oracle configuration tools.
#This file is actually generated by netca. But if customers choose to
#install "Software Only", this file wont exist and without the native
#authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
#From 12cR2 and beyond oracle enforced higher authentication level
#Two options:
#1. RESET PASSWORD of these accounts (password_versions) value should be updated, and make sure your application CLIENT IS COMPATIBLE WITH 18c !
#2. LOWER AUTHENTICATION by setting following parameter in sqlnet.ora file for example : **SQLNET.ALLOWED_LOGON_VERSION=11**
```
SQLNET.ALLOWED_LOGON_VERSION=11
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
### From 12cR2 and beyond oracle enforced higher authentication level
```
SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN username HEADING username FORMAT A30
COLUMN PASSWORD_VERSIONS HEADING PASSWORD_VERSIONS FORMAT A15
select USERNAME, PASSWORD_VERSIONS from dba_users order by USERNAME ;
username PASSWORD_VERSIO
------------------------------ ---------------
...
HR 11G 12C
...
```
Two options:
1. reset password of these accounts (password_versions) value should be updated, and make sure your application client is compatible with 18c !
2. lower authentication by setting following parameter in sqlnet.ora file for example : **SQLNET.ALLOWED_LOGON_VERSION=11**
cd [oracle_home]\bin
### lsnrctl status
Shows values of a number of important parameters:
1. **port** the listener listens on
2. **list of services** registered with the listener. Default services created by Oracle Database XE are **XE and XEPDB1** . XE service connects you to the root container of the database ( CDB$ROOT ) and the XEPDB1 service connects you to default pluggable database ( XEPDB1 ) created at the installation time. For each new pluggable DB (PDB) in DB, there will be a **new default service created with the same name as the PDB**. Note: If DB XE instance is shut down, lsnrctl status command does not show any services you can connect to.
3. **port EM Express** listens on
4. name of the **configuration** file used by the listener
5. name of the **log** file
### Easy Connect strings
1. Multitenant container database: host[:port] **SSPC2:1521**
2. Pluggable database: host[:port]/service_name **SSPC2:1521/XEPDB1**
Specifying port is optional when the listener is setup with default port 1521 . You must specify port number if you use another port.
If you are connecting from a remote computer, you need to provide **hostname eg SSPC2** (where XE is installed) instead of localhost.
Eg you can connect to** root container of DB** from a client computer with SQLPlus using following commands:
cd [oracle_home]\bin
sqlplus system@dbhost.example.com:1521
You can connect **to default pluggable database XEPDB1** using following commands:
cd [oracle_home]\bin
sqlplus system@dbhost.example.com:1521/XEPDB1
Replace dbhost.example.com with your DB host name. If needed, replace 1521 with port number the listener listens on. You can replace XEPDB1 with name of another PDB you want to connect to.
To shorten connect strings or avoid hardcoding host name and port in app. code and DBA scripts, you can define an **alias for connect string** in
configuration file [oracle_home]\network\admin\tnsnames.ora on DB clients. See Configuring Local Naming Method for more details.
See Also:
1. 2 Day DBA for more information about **listener and network configuration**
2. Oracle Database Development Guide for more information about **connection strategies** for database applications
3. Ora. DB Net Services Admins Guide and Ora. DB Net Services Reference for more information about **Net Services** and their configuration.
4. Oracle Multitenant Administrator?s Guide for more information about **containers** and connecting to a container in a CDB.
By default, when Ora. DB service is started, container DB is started and opened, but any **pluggable DBs must be opened before use** - manually or set to automatically open.
### Starting Up / Shutting Down Using Win. Services
Start menu, type services.msc, OracleServiceXE service in Services window, Right-click service name.
OracleOraDB18Home[n]TNSListener , where [n] is a number chosen by Oracle Database XE installer based on other Oracle homes previously installed on the host.
### Starting Up and Shutting Down Using SQL Plus
cd [oracle_home]\bin
sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE
or
SQL> STARTUP
SQL> **ALTER PLUGGABLE DATABASE ALL OPEN;**
**Pluggable DBs can be set to automatically open when container DB opens** by connecting to the container database via SQL Plus (as above), and issuing following SQL:
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL SAVE STATE;
<br /><br /><br />
**select name, open_mode, cdb from v$database ;**
```
NAME OPEN_MODE CDB
--------- -------------------- ---
XE READ WRITE YES
```
### show pdbs
was but no more err SP2-0382: The SHOW PDBS command is not available (on 18c XE)
**ipconfig**
```
Ethernet adapter Ethernet:
Connection-specific DNS Suffix . :
IPv4 Address. . . . . . . . . . . : 192.168.5.12
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.5.1
Ethernet adapter Ethernet 3:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::a099:c564:36e6:bf89%12
IPv4 Address. . . . . . . . . . . : 192.168.56.1 (Oracle Linux on Virtual Box)
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :
sqlplus system/MYPSW@192.168.5.12:1521/XEPDB1
Connected.
or
conn system/MYPSW@localhost:1521/XEPDB1
Connected.
or
```
**conn system/MYPSW@sspc2:1521/XEPDB1**
**CONN SYS as SYSDBA**
**ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE SCOPE=BOTH;**
SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
```
show con_name
-- XEPDB1
sho user
-- USER is "HR"
ALTER USER hr identified by hr account unlock;
grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;
conn HR/HR@localhost:1521/XEPDB1
Connected. - case sensitive if we do not SEC_CASE_SENSITIVE_LOGON = FALSE !!
select tname from tab;
```
<br /><br />
### SQLdeveloper 19.2.1
https://www.oracle.com/downloads/
Click **new connection**:
Name: **XEPDB1_sys**
Username: **sys**
Password: MYPSW
Save Password: check box
Role: **SYSDBA**
Hostname: localhost
Port: 1521
Service name: **xepdb1**
Then click test, should see : Status Success
Click Save -> Click Connect
You are now connected to 18c XE db.
<br /><br />
### Can optionally update tnsnames.ora file
in s/w base dir/network/admin/tnsnames.ora file to add an connection alias for xepdb1 as below, for me that directory is :
`J:\app\ss\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora`
XEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ACER-HOME-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XEPDB1)
)
)
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=('J:\app\oraclexe\product\18.0.0\dbhomeXE\oradata\XE\PDBSEED\',J:\app\oraclexe\product\18.0.0\dbhomeXE\oradata\XE\PDB2\');
'
https://smarttechways.com/ https://smarttechways.com/2019/03/05/ora-16000-database-or-pluggable-database-open-for-read-only-acc
https://smarttechways.com/2020/08/30/set-the-oracle-home-environment-variable-in-windows-platform/comment-page-1/#comment-16845
<br /><br />
## Uninstall Oracle 18c XE and delete database (destructive) ? CAUTION
(Will need a reboot at the end to complete, but can reboot later if needed)
As per documentation: https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/deinstalling-oracle-database-xe.html#GUID-DC36940C-D201-457B-A3D5-D6E2BB800120
Use Add or Remove Programs to deinstall Oracle Database XE:
In the Windows Control Panel, select Add or Remove Programs.
Select Oracle Database 18c Express Edition.
Click Change/Remove or Uninstall.
Follow the prompts to deinstall Oracle Database Express Edition. After few minutes, the window will close and the deinstallation will be complete.
### Deinstalling
If you want to save your data files but remove the Oracle Database XE software and database, then first export the data before you deinstall.
Caution:
When you deinstall Oracle Database XE, all components, including data files, the database, and the software, are removed. Because the deinstallation process removes all files from the directory in which Oracle Database XE is installed, back up any files from the directory (if needed) before you deinstall. The database will no longer be operational after deinstallation. You can deinstall Oracle Database XE using one of the following methods:
1. Deinstalling Using User Interface
2. Deinstalling Using Silent Mode
### Deinstalling Using User Interface
Use Add or Remove Programs to deinstall Oracle Database XE:
1. In the Windows Control Panel, select Add or Remove Programs.
2. Select Oracle Database 18c Express Edition.
3. Click Change/Remove or Uninstall.
4. Follow the prompts to deinstall Oracle Database Express Edition. After few minutes, the window will close and the deinstallation will be complete. Alternatively, you can use following msiexec command to perform the deinstall: msiexec /x {C220B7FD-3095-47FC-A0C0-AE49DE6E320A}
12.11.2 Deinstalling Using Silent Mode
If the downloaded software is available, you can use setup.exe to deinstall Oracle Database XE. For example, if you downloaded and extracted the files to a directory called xe_temp , enter the following command to deinstall Oracle Database XE:
c:\xe_temp> setup.exe /s /x /v"/qn /Lv c:\test1.log"
If you do not have the downloaded software ( setup.exe ) you can deinstall using: msiexec /qn /x {C220B7FD-3095-47FC-A0C0-AE49DE6E320A}
You can replace /qn with /qb to display the progress bar.
Note:
1. Silent deinstall runs asynchronously; you can check the log file for the completion status.
2. After the deinstall is successfully completed, the following message is available in the log file:
MSI (s) (8C:C4) [Timestamp]: Windows Installer removed the product. Product Name: Oracle Database
18c Express Edition. Product Version: 18.4.0.0.0. Product
Language: [Current Language ID]. Manufacturer: Oracle
Corporation. Removal success or error status: 0.
-----
## Globalization Support
Oracle Database XE is configured by default to be able to process character data in all supported languages simultaneously:
1. The database is created with the Unicode AL32UTF8 character set. AL32UTF8 is the recommended database character set suitable for storing data in practically any language. Multiple languages can be mixed even in a single character value. While not a recommended option, you can create the database with another supported database character set by calling setup.exe from the Command Prompt as follows:
setup.exe /v"CHAR_SET=\<character_set>"
where \<character_set> is the desired database character set. Supported database character sets are listed in tables A-4 and A-6 in Appendix A of the Database Globalization Support Guide. Character sets from Table A-4 are preferred over character sets from Table A-6 because of more comprehensive character repertoires.
2. The Oracle Database Express Edition (XE) supports the same globalization features that Oracle Database Enterprise Edition (EE) provides.
### 13.1 Setting Language and Locale Preferences for Client
Connections
Configure client applications connecting to an Oracle Database according to your locale preferences and your I/O device character set. You must configure client applications connecting to an Oracle Database according to your locale preferences and your I/O device character set. If your applications do not have their own specific methods to configure locale preferences, then the method you use to configure an Oracle database client connection depends on the access API you use to connect to the database. Check your application documentation, before you configure locale preferences for your applications.
For applications that connect to Oracle Databases using Oracle Call Interface (OCI) use NLS_LANG and other client settings with names that start with NLS\_ to set the locale conventions and client character set for Oracle Database sessions. It is important that you set the character set part of the NLS_LANG value properly. The character set you set must correspond to the character set used by your I/O devices, which in case of Microsoft Windows is either the ANSI Code Page (for GUI applications), such as WE8MSWIN1252, or the OEM Code Page (for Console mode applications), such as US8PC437. By doing this, the OCI API is notified about the character set of data that it receives from the application. OCI can then convert this data correctly to and from the database character set.
NLS_LANG and the other NLS settings can be specified either as environment variables or as Windows Registry settings. Environment variable values take precedence over Registry values. Oracle Universal Installer sets a default value for the NLS_LANG setting in Registry when it creates a new Oracle home. The NLS_LANG value is based on the language of the Windows user interface, which is the language of Windows menu items and dialog box labels.
Caution:
Failure to set the client character set correctly can cause data loss. Java applications that connect to Oracle Databases by using Oracle JDBC do not use NLS_LANG. Instead, Oracle JDBC maps the default locale of the Java VM in which the application runs to the Oracle Database language and territory settings. Oracle JDBC then configures the connected database session using these settings. Because Java works internally in Unicode, the client character set is always set to Unicode.
Unless an application explicitly changes it, the default locale of the Java VM is set based on the locale of the user operating system on which the Java VM runs. Check your Java VM documentation for information about configuring the Java VM default locale.
Note:
In 3-tier architecture deployments, application servers that are database clients can have settings in their configuration files that specify the NLS_LANG value or the Java VM locale. Check the documentation accompanying these servers.
Related Topics
Oracle Database Administrator?s Reference for Microsoft Windows
See Also:
Oracle Database Globalization Support Guide for more information about configuring user locale preferences
### users.sql
```
prompt **********************************************************
prompt J:\app\oraclexe\product\18.0.0\dbhomeXE\bin\users.sql SAYS
prompt **********************************************************
-- if SQL*Plus writes trailing spaces when spooling data to a file
SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN PARAMETER HEADING PARAMETER FORMAT A30
COLUMN value HEADING value FORMAT A100
COLUMN USERNAME HEADING USERNAME FORMAT A20
COLUMN COMP_NAME HEADING COMP_NAME FORMAT A30
COLUMN FILE_NAME HEADING FILE_NAME FORMAT A70
COLUMN tablespace_name HEADING tablespace_name FORMAT A15
prompt ******** 1. nls_database_parameters :
prompt select * from nls_database_parameters ;
prompt ******** 2. francuski e - all ok if this script is in npp "encoding UTF-8" :
prompt select '? ? ? ? ? é' non_eng_letters, ascii('é') from dual ;
select '? ? ? ? ? é' non_eng_letters, ascii('é') from dual ;
prompt ******** 3. nls_session_parameters :
prompt select PARAMETER, VALUE from nls_session_parameters ;
prompt ******** 4. nls_instance_parameters :
prompt select PARAMETER, VALUE from nls_instance_parameters ;
-- delete t_user where user_name != 'MERCEDES' EE8PC852 US8PC437 EE8MSWIN1250
-- delete t_user where user_alias != 'ora7'
prompt
prompt
prompt ********* APEX: ********
prompt https://docs.oracle.com/cd/E59726_01/install.50/e39144/pre_require.htm#HTMIG376
prompt name and location of the server parameter file eg SPFILEXE.ORA
prompt or initialization parameter file: (initsid.ora) :
prompt SHOW PARAMETER PFILE OUTPUTS :
prompt J:\APP\ORACLEXE\PRODUCT\18.0.0\DBHOMEXE\DATABASE\SPFILEXE.ORA
SHOW PARAMETER PFILE
prompt ***** DETERMINE CURRENT VALUES OF MEMORY_TARGET PARAM.: *****
prompt 0 = your db is using manual memory management - Consult Ora DB Admins Guide to learn how to configure an equivalent memory size using MANUAL MEMORY MANAGEMENT,
prompt instead of continuing with the steps that follow.
prompt If system is using SERVER PARAMETER FILE, set value of the MEMORY_TARGET initialization parameter to at least 300 MB so:
prompt ALTER SYSTEM SET MEMORY_TARGET='300M' SCOPE=spfile; then SHUTDOWN and STARTUP
prompt SHOW PARAMETER MEMORY_TARGET OUTPUTS :
prompt NAME TYPE VALUE
prompt memory_target big integer 0
SHOW PARAMETER MEMORY_TARGET
prompt
prompt
select default_tablespace, temporary_tablespace from dba_users where username like 'APEX%'
/
select tablespace_name, file_name
, (maxbytes - bytes) / 1024/1024 as "Available Space MB"
, autoextensible
from dba_data_files
--where tablespace_name in ('SYSAUX', 'SYSTEM')
/
prompt E N D A P E X ********************************************
prompt
prompt
select comp_name, version, status from dba_registry where comp_id = 'XDB';
--NLS_LANGUAGE=american NLS_TERRITORY=america
--NLS_CHARACTERSET=US8PC437
alter session set
nls_numeric_characters=',.'
nls_date_format='YYYY-MM-DD HH24:MI:SS'
/
-- ne ide: alter session set NLS_LANG=american_america.US8PC437
-- NLS_CHARACTERSET=US8PC437
--SELECT distinct PARAMETER FROM V$NLS_VALID_VALUES
-- CHARACTERSET SORT TERRITORY LANGUAGE
--SELECT PARAMETER, VALUE FROM V$NLS_VALID_VALUES where PARAMETER='CHARACTERSET' order by VALUE
-- EE8ISO8859P2 EE8MSWIN1250 EE8PC852 UTF8
select * from t_tip_doc
/
select a.USER_ID, a.CREATED
, u.NAZIV, u.USER_NAME, u.USER_PASSWORD, u.USER_ALIAS
from all_users a, t_user u
where u.user_name = a.username(+)
order by u.user_name
/
select * from t_user order by user_name
/
prompt D:\>echo %NLS_LANG% vrati AMERICAN_AMERICA.EE8MSWIN1250
prompt D:\>chcp vrati Active code page: 852 = console page win CLI-a
prompt U nls_database_parameters: nlsdbcharset= AL32UTF8 ili EE8MSWIN1250 :
select value from nls_database_parameters where parameter ='NLS_CHARACTERSET'
/
prompt select banner from v$version where banner like 'Oracle Database%';
prompt OUTPUTS : Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
select banner from v$version where banner like 'Oracle Database%';
prompt select instance_name, status, database_status from v$instance;
prompt OUTPUTS :
prompt INSTANCE_NAME STATUS DATABASE_STATUS
prompt xe OPEN ACTIVE
select instance_name, status, database_status from v$instance;
select USERNAME, USER_ID, CREATED from all_users order by username
/
prompt After DB 18c install. 35 rows selected. :
prompt system/MYPSW has no APEX... users
prompt
prompt
prompt show con_name displays :
show con_name
prompt sho user displays :
sho user
prompt show pdbs displays :
show pdbs
prompt
prompt
prompt ALTER SESSION SET container=XEPDB1; show con_id or SELECT name, con_id FROM v$pdbs;
prompt ALTER SESSION SET container=CDB$ROOT; (PDB$SEED=2) SELECT name Service_Name FROM v$active_services WHERE con_id =3;
prompt
prompt
```
<br /><br />
## Export (expdp) vs RMAN
both backup tables, and they both support flashback database, but there are some important differences:
Data Pump Export (expdp) - The export utility is a "logical" backup, usually done by specifying specific tables. If you fail to do a "consistent" export, or if you fail to include related tables and use RESTRICTED and CONSISTENT mode (with referential integrity constraints), you may not be able to recover properly. Export is often used as a supplement to RMAN, usually for the restore of specific tables.
Recovery manager (rman) - RMAN is designed for backup and recovery, a extension of the Enterprise Backup Utility (EBU). RMAN takes full, physical, consistent backups of your database files..
Advantages & disadvantages of Export:
1. Free, and easy to use
2. Very slow, compared to RMAN (examines every data block)
3. Easy restore of a specific table
4. Does not require ARCHIVELOG mode
Advantages & disadvantages of RMAN:
1. RMAN has block-level media recovery
2. Has a catalog for backup tracking and a report utility
3. Fast - If you dedicate a backup device for each production disk, you can backup terabytes in the time it takes to backup any single disk.
4. Does hot or cold backups
5. Backups and restores can be done in parallel
6. Allows incremental backups (block change tracking)
7. Interfaces with media management systems (TMS)
Tutorial : https://support.dbagenesis.com/knowledge-base/oracle-data-pump-utility-expdp-impdp/
Video : https://www.youtube.com/watch?v=b0o4tb7P_xg or https://www.youtube.com/watch?v=rPqcnOZK984
Link to articles: https://support.dbagenesis.com eg https://support.dbagenesis.com/knowledge-base/install-oracle-linux-on-virtualbox/
<br /><br />
## expdp
### 1. create OS level directory
which will be used by Oracle for performing exports and imports :
\# mkdir -p /u02/dp_exp_dir
Create directory inside DB or use **existing DATA_PUMP_DIR** (see below) :
SQL> create directory datapump as '/u02/dp_exp_dir';
**create directory datapumpmy as 'J:\app\oraclexe\product\18.0.0\admin\xe\dpdumpmy\';**
'
Grant permissions on directory :
SQL> **grant read,write on directory datapumpmy to hr ; **
View directory information :
desc dba_directories
SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN PARAMETER HEADING PARAMETER FORMAT A30
COLUMN OWNER HEADING OWNER FORMAT A15
COLUMN DIRECTORY_NAME HEADING DIRECTORY_NAME FORMAT A30
COLUMN DIRECTORY_PATH HEADING DIRECTORY_PATH FORMAT A100
select substr(OWNER,1,30) OWNER, substr(DIRECTORY_NAME,1,30) DIRECTORY_NAME, substr(DIRECTORY_PATH,1,100) DIRECTORY_PATH, ORIGIN_CON_ID from dba_directories;
```
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------------- ------------------------------ ----------------------------------------------------------- -------------
SYS ORA_DBMS_FCP_LOGDIR J:\app\oraclexe\product\18.0.0\dbhomeXE\cfgtoollogs ALL ARE = 1
SYS ORA_DBMS_FCP_ADMINDIR J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\admin
SYS SDO_DIR_WORK
SYS SDO_DIR_ADMIN c:\ade\aime_v\oracle/md/admin
SYS ORACLECLRDIR J:\app\oraclexe\product\18.0.0\dbhomeXE\bin\clr
SYS XMLDIR J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\xml
SYS XSDDIR J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\xml\schema
SYS ORACLE_OCM_CONFIG_DIR2 J:\app\oraclexe\product\18.0.0\dbhomeXE\ccr\state
SYS ORACLE_OCM_CONFIG_DIR J:\app\oraclexe\product\18.0.0\dbhomeXE\ccr\state
SYS OPATCH_INST_DIR J:\app\oraclexe\product\18.0.0\dbhomeXE\OPatch
SYS **DATA_PUMP_DIR** J:\app\oraclexe\product\18.0.0\admin\xe\dpdump\
SYS OPATCH_SCRIPT_DIR J:\app\oraclexe\product\18.0.0\dbhomeXE\QOpatch
SYS OPATCH_LOG_DIR J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\log
SYS ORACLE_BASE J:\app\oraclexe\product\18.0.0
SYS ORACLE_HOME J:\app\oraclexe\product\18.0.0\dbhomeXE
15 rows selected.
```
<br />
### 2. Perform Data Pump Export
In case you want to know all the options available with expdp utility, you can use below command
$ expdp help=y
To take **DB** level export
$ expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y
### To take **schema level export**
$ expdp directory=datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'
To take **table** level export
$ expdp directory=datapump dumpfile=emp_bkp.dmp logfile=emp_bkp.log tables='SCOTT.EMP'
To take **row** level export
$ expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables='SCOTT.EMP' query=\"where deptno=10\"
"
<br />
### 3. Performing Data Pump Import
In case you want to know all the options available with impdp utility, you can use below command
$ impdp help=y
To import full database
$ impdp directory=datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y
To import source schema objects into same schema name
$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:SCOTT'
### To import source schema objects into a different schema
$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='HR:HRDPIMP_TEST'
To import a table
$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT'
To import a table to another user
$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SYSTEM'
To import tables to another tablespace (only in datapump)
$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT' remap_tablespace='MYDATA:MYTBS'
https://support.dbagenesis.com/knowledge-base/oracle-expdp-par-file-example/ A sample data pump export and import :
```
Data Pump schema level Export:
====================
expdp directory=datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'
Data Pump Import:
=================
impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:SCOTT'
```
In real-time, you might perform very **complex** export and import and **frequently** - daily or weekly. Data Pump jobs can be automated using PAR file :
#### cat dept_exp.par
Username=hr/hr
tables=hr.emp
directory=EXP_DIR
dumpfile=QUERY_EXP.dmp
logfile=QUERY_EXP.log
To call the above export PAR file :
#### expdp parfile=exp.par
<br /><br /><br /><a name="inst_11g"></a>
***
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....**[Install 11g](#inst_11g)**.....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)
***
# Install Ora 11g, ver. 11.2.0.1.0
**on Windows 10 64 bit**
You need an Oracle account and a **license** that allows you to upgrade to 11.2.0.4
Download for Windows : https://www.oracle.com/database/technologies/112010-win64soft.html
Tutorial for Windows : **https://www.geeksforgeeks.org/how-to-install-oracle-database-11g-on-windows/**
See https://support.oracle.com/portal/
Extract :
Extract both files win64_11gR2_database_1of2.zip and 2of2 in same directory `J:\apl\possys6\instlcija\instlcija_11g32_64bit\database`
Install :
1. database Folder -> Right click setup.exe -> Run as Administrator
2. "Envir. does not meet min requir." - Click Yes to continue. This will start Oracle Universal Installer
3. leave these fields empty and click on Next or Provide your Email Address to receive alerts from Oracle
4. Select any of the three different Installation Options
1. **cre and conf db** - Install Oracle Server Software and want to Create Database also.
2. or only db sw - to Install Oracle Server only.
3. or upgrade existing db
5. Choose between Server Class and **Desktop Class** Ignore warning related to Admin Password.
6. Configure basic settings :
1. Oracle base : **J:\app\ss**
2. SW location : **J:\app\ss\product\11.2.0\dbhome_1**
3. DB file location : J:\app\ss\oradata
4. DB edition : Enterprise 3,34 GB
5. Char set :Default (EE8MSWIN1250)
6. Global DB name : **orcl**
7. Admin psw : MYPSW
7. Oracle Universal Installer(OUI) will check for the Prerequisites such as Hardware compatibility
8. Click on Finish to start Installation process.
9. Click OK to finish the installation.
10. Copy localhost link provided to open your Enterprise Manager.
Database Control URL is **https://localhost:1158/em**
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: J:/app/ss/product/11.2.0/dbhome_1/localhost_orcl/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
For security reasons, all users are locked except for the following users: SYS and SYSTEM If you have enabled Local Management with Oracle Enterprise Manager, the SYSMAN and DBSNMP accounts are also unlocked. These accounts provide Enterprise Manager with access to the database so it can gather monitoring data and so you can perform administration tasks with Enterprise Manager. If you have enabled Central Management with Oracle Enterprise Manager, the DBSNMP is unlocked, as well as the SYS and SYSTEM user accounts. You can use this page of the Assistant to unlock other database users; or, you can also unlock users at a later time using Oracle Enterprise Manager or the command line tool, SQLPlus. The following table describes the columns in the table of database users.
User Name Lock Account? - Select or clear this column to unlock the account and reset the user password. New Password If you unlock a database user account, you must enter a password for the user in this field. Confirm Password Enter the password again to verify the password.
Click Close Button and you are done with the Installation Process.
Getting Started with Oracle Enterprise Manager :
There are two ways to start Oracle Enterprise Manager:
1. One way https://localhost:1158/em is using the Link provided in last step of Installation Process.
Note: In case if you misplace this link in the future, follow the next step to start it the other way.
2. Go to Start Menu and Search Oracle Folder -> Click on Database Control -> Oracle (Your Global Database Name)
This will take you to the Login Screen of your Oracle Enterprise Manager. Click Advanced -> Proceed To Localhost if
ERR_CERT_AUTHORITY_INVALID -> Login with your Credentials and begin using 11g DB.
### User HR :
#### PS C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin>
#### PS J:\app\ss\product\11.2.0\dbhome_1\bin>
In Win CLI with admin rights we may restart listener : lsnrctl reload
lsnrctl start
#### lsnrctl status
```
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 14-RUJ-2020 15:01:23
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 14-RUJ-2020 00:01:50
Uptime 0 days 14 hr. 59 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File J:\app\ss\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File j:\app\ss\diag\tnslsnr\sspc2\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
PS J:\app\ss\product\11.2.0\dbhome_1\bin>
```
#### Sqlplus system/MYPSW@orcl
--sqlplus / as sysdba
--conn system/MYPSW@orcl
ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
grant CONNECT,DBA,RESOURCE to hr IDENTIFIED BY hr;
exit
#### Sqlplus hr/hr@localhost:1522/orcl
-- orcl in 11g, XEPDB1 in 18c
sho user --(SYS) USER is "HR" show con_name --n/a in 11g, CDB$ROOT or XEPDB1
SELECT name FROM v$database;
--orcl
See **COUNTRIES ORA-01410: invalid ROWID** in this txt.
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
--0
By default, the XML database is configured with http port set to 8080 and FTP port to 2100.
```
--Set FTP and HTTP port
begin
dbms_xdb.setFTPPort(new_port => 2122);
dbms_xdb.setHTTPPort(new_port => 8089);
end;
/
--Get new values
declare
v_ftpport number;
v_httpport number;
begin
select dbms_xdb.getFTPPort into v_ftpport from dual;
select dbms_xdb.getHTTPPort into v_httpport from dual;
dbms_output.put_line(a => 'FTP port:'||v_ftpport);
dbms_output.put_line(a => 'HTTP port:'||v_httpport);
end;
/
```
<br /><br /><br /><a name="deinst_11g"></a>
***
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....**[Deinstall 11g](#deinst_11g)**.....[Install 18c XE](#inst_18cXE).....[ Links](#links)
***
# Deinstall DB 11g (not XE)
http://www.rebellionrider.com/how-to-uninstall-oracle-database-11g-from-windows-10-64bit/ **Manish Sharma** January 10, 2019
1. This PC Properties -> Advance System Settings -> System properties (go to the Advance Tab) -> Environment Variable-> Path -> delete ...product\\11.2.0... or "Environment variable path is too long"
2. Delete the registries of oracle database 11g Winkey + R -> regedit
1. delete the registry for Oracle Software HKEY_LOCAL_MACHINE ?> SOFTWARE ?> ORACLE - Right click ORACLE dir and then select EXPORT - then right click and Delete ORACLE dir **after you make sure** only oracle database 11g software is installed in your system. In case you have other software from oracle installed then take a good look at the content of this folder and delete only those associated with 11g.
2. delete registries which are responsible for running oracle database services HKEY_LOCAL_MACHINE -> SYSTEM -> CurrentControlSet -> SERVICES - Right click CurrentControlSet and select export.
Under services directory you will have to find all the registries listed below and delete them. List of Oracle Database Registries for background services :
....11g Enterprise................................................11g XE
1. Oracle11
2. OracleDBConsoleorcl
3. OracleJobSchedulerORCL
4. OracleMTSRecoveryService
5. OracleOraDb11g_home1ClrAgent
6. OracleOraDb11g_home1TNSListener
7. OracleServiceORCL
8. OracleVssWriterORCL
ORCL is default SID for the Oracle database 11g Enterprise Edition.
3. Restart your PC
4. Wipe out oracle mess - delete all the folders of oracle database 11g from your windows 10 system.
1. **Oracle home (dir where ora DB SW is installed)** is located in the C drive or in the main drive in which your OS is installed and **named as APP**.
2. Thereafter go to the directory with your username. Here select all the files and delete them. If you want to save backups then do not delete flash_recovery_area.
3. go to your Program File folder which is placed in your main drive where OS is installed and here locate a folder with the name ?Oracle? and delete it.
4. delete the entry from your start menu . copy and paste the path given below either in the Run command box or directly into your file explorer :
C:\ProgramData\Microsoft\Windows\Start Menu\Programs- > find oracle database 11g folder eg "Oracle - OraDb11g_home1" - delete this folder.
5. Restart your PC
<br /><br /><br /><a name="links"></a>
***
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....**[ Links](#links)**
***
## http://www.rebellionrider.com/category/oracle-database-18c/
1. expdp/impdp : http://www.rebellionrider.com/data-pump-expdp-how-to-export-schema-oracle/
http://www.rebellionrider.com/system-privileges-with-admin-option-oracle/
2. Install : http://www.rebellionrider.com/how-to-install-oracle-database-18c-on-windows-10/
11g : https://www.youtube.com/watch?v=d_CyuCLC3Ls
find-out-the-sid : http://www.rebellionrider.com/how-to-find-out-the-sid-and-db-home-in-oracle-database/
3. unlock-hr-user http://www.rebellionrider.com/how-to-unlock-hr-user-in-oracle-database-18c-by-manish-sharma/
4. connect-with-sample-schema-hr http://www.rebellionrider.com/how-to-connect-with-sample-schema-hr-in-oracle-database-18c-by-manish-sharma/
5.connect-with-pluggable-db http://www.rebellionrider.com/connect-with-pluggable-database-using-sql-developer-in-oracle-database-18c-by-manish-sharma/
6. uninstall http://www.rebellionrider.com/how-to-uninstall-oracle-database-18c-by-manish-sharma/
<br /><br /><br />
create table sstest (name varchar(10));
insert into sstest values(?slavko s.?);
commit;
https://www.tutorialspoint.com/plsql/plsql_dbms_output.htm
-- DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);
--if buffer_size=NULL=unlimited
```
PS J:\app\ss\product\11.2.0\dbhome_1\bin>
Sqlplus hr/hr@localhost:1522/orcl
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON
DECLARE
lines_chararr dbms_output.chararr;
num_lines number;
num_of_tbls number;
num_of_txt_lines number;
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(NULL);
num_lines := 0;
num_of_tbls := 0 ;
-- 1. txt lines :
dbms_output.put_line (user || ' Tables in DB :'); num_lines := num_lines + 1;
dbms_output.put_line('SELECT table_name FROM user_tables :'); num_lines := num_lines + 1;
num_of_txt_lines := num_lines ;
--2. tbl names :
FOR t IN (SELECT table_name FROM user_tables)
LOOP dbms_output.put_line(t.table_name); num_lines := num_lines + 1;
num_of_tbls := num_of_tbls +1 ;
END LOOP;
--3. put 1. and 2. in lines_ chararr :
dbms_output.get_lines(lines_chararr, num_lines);
--4. display lines_ chararr :
FOR ii IN 1..num_lines LOOP
if ii > num_of_txt_lines then
dbms_output.put_line((ii - num_of_txt_lines)||'. '||lines_chararr(ii));
else dbms_output.put_line(lines_chararr(ii));
end if ;
END LOOP;
--DBMS_OUTPUT.DISABLE;
END;
/
SET SERVEROUTPUT OFF
HR Tables in DB :
SELECT table_name FROM user_tables :
1. REGIONS
2. LOCATIONS
3. DEPARTMENTS
4. JOBS
5. EMPLOYEES
6. JOB_HISTORY
7. T
8. COUNTRIES
```
<br /><br /><br />
___
<a name="md2html"></a>[Top](#top)
>### MD to HTML converters on inet
1. **https://www.tutorialspoint.com/online_markdown_editor.php** or https://markdowntohtml.com/
2. or https://www.browserling.com/tools/markdown-to-html (many converters)
3. or files convert : https://products.aspose.app/pdf/conversion/md-to-html to many formats
4. Links not working : http://demo.showdownjs.com/ (no HTML source)
NOT WORKING : https://daringfireball.net/projects/markdown/dingus or https://pandoc.org/try/
>https://www.w3schools.com/html/html_symbols.asp
>https://www.degraeve.com/reference/specialcharacters.php
<br /><br /><br /><a name="eof"></a>
***
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)
***
https://community.oracle.com/tech/developers/discussion/comment/16847122
Put this Croatian text in https://translate.google.com/
PITANJE 1 : Standalone Forms Builder nam je beskoristan ? (nama koji jo? uvijek radimo da F6i i DB 11g XE client-server SW za mala poduze?a, ali i za velika poduze?a na DB 11g)
Nema Reports ! ?emu slu?i Standalone Forms Builder u praksi ? - samo za u?tedu novca da se ne kupuje kompletna instalacija ?
?ini mi se da je problem u "you cannot run the forms you develop directly from this installation" i u najnovijim WEB Forms i Reports (koji sada - 2022 godina - ne rade sa DB XE !!)
PITANJE 2 :
2.1 Prema obja?njenjima u nastavku ovog teksta trebam jednostavnu uputu za instalaciju i rad sa najnovijim Oracle DB XE, WEB Forms i Reports (koji sada - 2022 godina - ne rade sa DB XE !!), data pump...
2.2 Ponavljam da bi client-server SW Forms6i_2022 and Reports6i_2022 potpuno kompatibilan sa prastarim napu?tenim F6i i R6i bio dovoljan.
2.3 Oracle APEX je deklarativno programiranje postavljanjem vrijednosti osobina ?TA raditi a ne kodiranjem (KAKO korak po korak), sve je u bazi, nejasno je kako pristupiti tome osim by setting properties. Ali dobro bi do?la uputa kao pod 2.1 koju nisam mogao na?i, popra?ena primjerom npr
- blog
- i invoice kupcu,
pa bi APEX bio rje?enje. Poku?ao sam napraviti za sebe takvu uputu i izgubio previ?e vremena kao i za dolje spomenutu najnoviju Oracle DB. Za puno detalja nisam uspio prona?i rje?enje - ne?to ne radi i tu sam blokiran. Primjeri npr blog i invoice kupcu rje?avaju taj naj?e??u uzrok problema.
Bez jednostavne, ali dovoljno sveobuhvatne upute ne mogu vjerovati autorima SW koji pre?esto izjavljuju da imaju ono ?to nemaju.
U vezi : "Why people are so concerned about using the latest Database versions but not the latest of their application layer is completely a mystery to me." Mislim da tu nema misterije : plugable DBs i ostale osobine najnovijih Oracle DB mogu biti vrlo korisne (najnovija DB teoretski garantira nezastarjevanje, ve?u kvalitetu...), ali najnoviji WEB Forms i Reports su novi SW (prakti?ki nekompatibilni sa F6i, R6i) koji potrebuje
- specijalista za instalaciju i odr?avanje ili uputu 2.1 koje nema ?
Nisam uspio prona?i uputu za nas obi?ne ljude za instalaciju, rad i odr?avanje najnovijih Forms i Reports. (iako imam 22 godine iskustva sa f6i i r6i) - jednostavnu i sa svim potrebnim detaljima.
Mislim da je to razlog za?to vi?e prakti?ki nitko ne radi nove projekte sa Forms i Reports, a nerazumljivi i nekompatibilni Mirosoft alati preuzimaju tr?i?te kao i PHP kojeg svi veliki zli imperiji neopravdano (?) podcjenjuju. (Oracle, Mirosoft i sve ostale upute uklju?uju?i PHP su nerazumljiva katastrofa, ?ast iznimkama).
Za instalaciju i upotrebu najnovije Oracle DB XE (isto za najnovije
Forms i Reports koji ne rade sa DB XE ) sam uspio na?i upute i napisao vlastitu koja je prili?no zbrkana kao i izvori iz kojih je nastala. Pomo?u te upute uspio pomo?u data pump prebaciti korisnika, kreirati plugable DBs i raditi sa njima ali
- detalji rada sa data pump su mi nejasni
- detalji rada sa plugable DBs su mi nejasni
Problem slu?aja mog poduze?a je : sa DB 11g koristimo exp.exe i imp.exe (novi na?in data pump, iako sam ga koristio, je nejasan, tj nedovoljno detaljno obja?njen). U DB svakog od nekoliko desetaka poduze?a imamo nekoliko desetaka korisnika. Svake godine stvaramo novog korisnika u DB za podatke te godine.
Zapravo Oracle je napustio slu?aj koji opisujem u ovom tekstu. ?ini se da su autori F i R otplovili u zaborav i nisu u stanju napraviti niti jedno od 2.1, 2.2 niti 2.3 (trgovci i programeri pi?u katastrofalno nejasne upute, prvi zbog komercijalnih razloga, drugi jer nisu svjesni zna?aja uputa). To je pogre?no, jer to bi Oracle-uu priskrbilo puno ve?i dio tr?i?ta alata.
Uzalud si genije ako te nitko ne razumije. Djeca se vole igrati programiranjem ali objasniti je puno puno te?e , to je filozofija, najte?a nauka za koju neuki tvrde da ne postoji ili da je najve?i neprijatelj (kao za sve ?to ne razumiju).
?alosno je da 2022 godine nema nti jednog SW osim PHP koji bi odradio 2.1. PHP nema zadovoljavaju?i framework (pod fw ovdje podrazumjevam stotinjak odsje?aka koda koji se ctrl+c, v ubacuju u skripte). Koliko znam ja sam jedini poku?ao to napraviti (a da se ne inkludira desetke megabajta vanjskog SW i da svaka .fmb bude u vlasitoj mapi) i uspio napraviti samo kostur menu, CRUD koda i Markdown editor na primjeru bloga - za jo? nekoliko desetak odsje?aka koda trebao bih previ?e vremena (https://github.com/slavkoss/fwphp).
Ne bojte se ne?e vi?e biti mojih postova. Ne ?elim nikoga vrije?ati, posebno ne "throw stones at the products, its developers, or anyone else" - Michael, jesi li svjestan koliko je nekorektno ovo ?to si napisao.
?elim (ovdje zadnji puta) govoriti o istini, a ako istina nekog vrije?a to nema veze sa istinom nego sa uvrije?enim - komercijalnim interesima ?
Michael, u nekoliko postova na ovoj stranici si opravdavao lo?u politiku Oracle-a koji je napustio dva mo?da najbolja alata ikad stvorena F6i i R6i.
1. TEHNOLO?KI PROBLEMI :
Od po?etka WEB ere (F6i i R6i) Oracle luta u WEB programiranju - nove F i R verzije su nekompatibilne sa prija?njim, tu je nepotrebni Java developer... Isto radi Microsoft (Silverlight je napu?tena plugin web technologija koju je tako?er i Oracle nedavno napustio). Blazor je najnovije nekompatibilno Microsoft ?edo.
Forms autori ni danas nemaju jedno rje?enje za run forme, nego njih vi?e zbog pogre?ne koncepcije velikih centralnih biblioteka i tko zna ?to jo?...
Kratko i jasno : umjesto da su WEB programiranje utemeljili kao PHP lutali su bespu?ima Javae tj velikih centralnih biblioteka i tko zna ?to jo?...
Ali tako?er i PHP stru?njacima je puno va?nije ne?to zaraditi nego napraviti posao (osobito ako bi posao ne daj bo?e umanjio njihovu zaradu).
2. KOMERCIJALNI PROBLEMI :
Ali nisu va?ne te pri?e, veliki problem je stav Oracle menad?era i trgovaca koji si ti izrazio ovako "upgrade the application layer (e.g. Forms/Reports)... Take some time to investigate...". Mi smo prije desetak godina istra?ivali i rezultat je bio da od tada grupa na?ih programera radi sa Microsoft alatima koji su ?ampioni nerazumljivosti i nekompatibilnosti. Ja poku?avam sa novim oracle alatima sve te godine ali bez kvalitetnih uputa je to mogu?e ako imamo puno, puno vremena (koje nitko nema nego treba zaposliti 2-3 specijalista).
1099 ili 2000 bio sam u Hamburgu mislim CeBit, pitao sam Oracle pretstavnika za na?in kako u?iti F i R, da mi preporu?i knjige. Odmahnuo je rukom i oti?ao. Isto ?ini? ti na ovoj WEB stranici.
Kako sam naivan :
1. Oracle ne zanimaju "little or no cost SW" i mala poduze?a : Power objects, F6i, R6i su zato napu?teni.
2. Oracle ne?e pisati kvalitetne upute jer ?eli da platite skupe kurseve
3. Oracle ?eli da nestanu male firme koje razvijaju SW jer one ne mogu pla?ati skupe kurseve, alate niti specijaliste za te alate. To je sudbina svih malih poduzetnika u globalizaciji koja je novi komunizam (veliki zli imperiji kao Oracle, Microsoft ... su u stvari dr?ava koja je najlo?iji poduzetnik).
Prema tome ja sam jako naivan kad u mom prethodnom postu molim kvalitetne upute i Tebe da jasno ka?e? : bez skupih kurseva, alata, specijalista za te alate ne treba (jer je to nemogu?e) raditi sa novijim Oracle alatima.
Dakle sa Oracle zaposlenicima je nemogu?e razgovarati o ovome ?to sam pokrenuo na ovoj WEB stranici (to znam jo? od CeBit-a) - jednostavno ?ivimo u razli?itim svjetovima :
- moj je "umri galiote" (malo crnog humora o crnoj stvarnosti),
- tvoj je svijet ?inovnika - politi?ara - bogata?a, trgovaca, i potpuno nepotrebnih propagandista, vrhunskih zabavlja?a - gladijatora ...).
Zahvalan sam ti ?to si uop?e objavio i odgovorio na moja 2 posta i nadam se na ovaj. Pretpostavljam da ?e ta tri posta razbiti iluzije o autorima SW alata mladim sposobnim ljudima kao ?to je moj ?ef koji je po?eo sa Power objects, pa nastavio sa F6i, R6i, Microsoft alatima koji su ?ampioni nerazumljivosti i nekompatibilnosti (Oracle je sli?an) - puno, puno nepotrebnog posla koji zahvaljujemo svijetu parazita (?ast iznimkama, engleska kraljica to nije bila).
Rekao sam da ne?u slati daljnje postove, jer sam o?ekivao da ?e Michael prestati razgovarati sa mnom (kao ?to je prestao razgovarati CeBit pretstavnik, trgov?i?, sramota za Oracle, razlog za?to Oracle gubi tr?i?te SW alata).
Nasser_almawali je rekao : "more than 4 hours to complete installation" a to nije detaljno opisano u Oracle uputama pa specijalisti nude uslugu. To je ono ?to sam naglasio u prethodnim postovima.
BITNE OSOBINE SW ALATA U VEZI SA NEZNANJEM
"fundamental difference between some of the technologies" u pogledu besplatnog SW otvorenog koda i komercijalnog SW je, vjerujem neva?na razlika kad govorimo o bitnim osobinama SW alata, npr kompatibilnost, kvalitetne upute... Magla tipa "SW se razvija" tako?er je neva?na.
Tema bitne osobine SW alata i neznanje je vrlo va?na pa bih temeljem svog dugogodi?njeg iskustva ?elio razjasniti (ako sam ih dobro razumio) osnovne ideje o SW alatima za dobrobit mladih kolega koji razvijaju korisni?ki SW i kolega u Oracle-u koji su kao i Microsoft o?ito i neoporecivo proteklih godina lutali sa SW alatima na OGROMNU ?tetu svijeta "umri galiote" zbog :
- razloga neznanja koje ovdje ?elim opisati (ljudi zavr?e ?kole najvi?eg nivoa a da ne znaju govoriti, jo? manje pisati, ali znaju krasti novac ?to jo? jako usavr?e u ?inovni?koj ?abokre?ini)
- i jo? vi?e komercijalnih razloga koje sam opisao u prethodnom postu
Kao ?to ki?a ne razlikuje javni park i privatni bogata?ev park tako i kompatibilnost SW ne ovisi u pogledu besplatnog SW otvorenog koda i komercijalnog SW. Ovisi samo o kvaliteti ideja iza SW. PHP kod (koliko god omalova?avan), sada i prije 20 godina, ako je korektno pisan je prakti?ki potpuno kompatibilan. Ideja jezika ?etvrte generacije vidljiva i u nekad popularnom Dbase i Clipperu je osnova i za PHP. Vrli novi svijet SW alata (metafizi?ki) izvan realnog svijeta koji crnohumorno nazivam "umri galiote" je tu ideju unakazio nekompatibilno??u, nerazumljivo??u, neobja?njavanjem, komercijalnim razlozima, neznanjem...
Imam zanimljivo opa?anje neznanja hrvatskih (i europskih) dr?avnih IT ?inovnika kako shva?aju XML dostavu podataka dr?avnim slu?bama : ne kao primjere cijelog XML-a koje daje Bootstrap, nego su napisali uputu koja diskutira pojedine djelove XML-a. Uputa je dobra ali ima dosta neugodnih gre?aka koje kao i svi povr?ni ?inovni?ki uradci (zakoni, propisi) jako tro?e vrijeme galiota koji to provode u svijetu "umri galiote".
O?ito nisu testirali XML koji zahtjevaju od nas, a to je vidljivo i po uvredljivo glupim porukama gre?aka njihovog SW koji prihva?a XML npr ra?una kupcu koji im ?aljemo. Kad sam autoru (dobre ali) atomizirane upute telefonski rekao gore re?eno odgovorio je da "svako bi htio biti nogometni trener". Poslije mi je ipak poslao cijeli XML, valjda je razumio.
Bio je to veliki uspjeh posti?i telefonski razgovor sa njim, jer ?inovnici ignoriraju neugodni svijet "umri galiote" koji uni?tavaju ne pomi?ljaju?i konzultirati one koje mu?e svojim glupostima.
MALO FILOZOFIJE POVIJESTI I ETIKE / MORALA
Sve civilizacije do sada su ?inovnici uni?tili pa ?e vjerojatno i na?u (robovlasnici, feudalci, bogata?i , Putin, NATO su u biti ?inovnici).
Najjednostavnije spoznaje je najte?e razumjeti :
- npr pravo ?ene da bude svoga tijela gospodar koje nema veze sa pravom djeteta na ?ivot. ?ta itekako ima veze sa pravom djeteta na ?ivot : povijesni pokolji organizirani od istih zlo?ina?kih licemjera koji bi financijski ubijali ?ene djecom koju ne mogu financijski uzdr?avati (umjesto da sami uzdr?avaju i to obilno tu djecu koju po cijenu ubistva majke i djeteta ?ele "sa?uvati" koju bi onda svaka ?ena rado rodila), zamotavali ?ene da ne izazivaju napaljene mu?ja?i?e : 100 godi?nji rat u Europi koji je definitivno odvojio dr?avne i vjerske ?inovnike, dva svjetska rata, zlo?inac Putin, afri?ki masakri, dalekoisto?ni masakri i drugi (kriminalci u ameri?koj vojnoj industriji - jesu li oni ubili Kennedyja? NATO, CIA) po cijelom svijetu uvijek i zauvijek? ),
- npr odvajanje dr?avnih i vjerskih ?inovnika u zapadnom svijetu nakon 100 godi?njeg rata u Europi koji je pokazao da je nam je dovoljna ?teta koju radi nesposobna klika dr?avnih ?inovnika (gora od mafije jer kontrolira vojsku i policiju) i da ne trebamo dodatnu ?tetu koju ?ine vjerski ?inovnici (uzgred re?eno psihi?ki bolesnici koji sami sebi zabranjuju sex i ?enidbu, ali prikrivaju homoseksualce a i imaju ?asne sestre koje su ni?a vrsta u odnosu na mu?karce u sekti).
Tako, sad kad sam vas prosvijetlio najva?nijim istinama o svemu, vrijeme je za mene da prestanem... ili ?elite jo?, ja sam u mirovini, samo pitajte ?
Ne moramo se slagati o ukusima, ali se moramo slagati o istinitim ?injenicama. Ho?e li se netko ?eniti ili vjerovati u boga je stvar privatnog ukusa koju bi ustavom trebalo zabraniti nametati ostalima u vlasttoj sekti a i ?ire (isto kao zabrana veza u poduze?ima, progon homoseksualaca...).
BASIC INFORMATION FOR SMALL AND MEDIUM USERS
What I meant by simple instructions in my previous posts:
Michael, in the following text, could you instead of "???" enter the data (or is it impossible?) and add all the important details (based on the installation done for this information) without referring to the atomized instructions about who knows what:
1. Install DB XE version not older than ??? or newer on Win. 10 64 bit / Win 11 64 bit
Price for one developer: free
for one workplace of the customer: free of charge
2. Install 64-bit Oracle JDK version 8
Price for one developer: ??? (approximately)
for one workplace of the customer: ??? (approximately)
3. Install Fusion Middleware Infrastructure 12.2.1.4
Price for one developer: ??? (approximately)
for one workplace of the customer: ??? (approximately)
4. Install Forms/Reports 12.2.1.4
Price for one developer: ??? (approximately)
for one workplace of the customer: ??? (approximately)
5. Run the Repository Creation Utility (RCU).
6. Run Configuration Wizard.
7. Start all servers to complete configuration.
|
Download
