WYSIWYG md editor SimpleMDE or use some desktop markdown text editor    Home    [ html2markdown ]
Markdown txt 01/001_db/01_oracle_DB_18c_instalac.txt Parsedown-ed to HTML.    [ Edit text ]    [ Colored text ]

2020.09.14


Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... Links


1. Oracle DB 18c instalac intro

J:

cd J:\app\oraclexe\product\18.0.0\dbhomeXE\bin = [oracle_home]\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





Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... 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 don't 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 :
  1. 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...)

  2. 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 can't 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




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





Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... 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_implg.log EXCLUDE=SCHEMA:\"LIKE \'APEX%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"

    impdp system/systempassword@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




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;
/





Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... 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.




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 Administrator's 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_1050856233HOQ2KLBG.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



Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... 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_implg.log EXCLUDE=SCHEMA:\"LIKE \'APEX%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"

    impdp system/systempassword@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



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





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;




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;



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.



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



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=\"
    where \ 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 NLSLANG 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



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/



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.


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\"


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





Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... 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;
/





Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... 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





Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... 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/
  5. uninstall http://www.rebellionrider.com/how-to-uninstall-oracle-database-18c-by-manish-sharma/




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





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





Top.....expdp 11g XE......impdp 18c XE.....Deinstall 11g XE .....Install 11g.....Deinstall 11g.....Install 18c XE..... Links