I got this error when I was trying to specify Host Credentials in EM 12c cloud control.
Cause: root.sh had not been executed from agent home
Solution: I executed root.sh from agent home and the problem was resolved.
[root@em12 dbhome_1]# /u01/app/oracle/product/agent/core/12.1.0.2.0/root.sh
I hope this information was useful.
—————————————————————————————–
Related Links:
12c : Access EM Express for CDB / PDB / Non-CDB
12c: Unable To Access EM Express For Non-CDB
In my earlier post, I had demonstarted the performance issue that could arise with in-database archiving. In this post, I am going to provide the solution to the same (as suggested by John Watson).
– Let’s create a table of employees wherein we would like to archive the records of the employees who have retired.
SQL>create table hr.emp (id number primary key, name varchar2(30), retired char(1)) row archival; insert into hr.emp values (1, 'ONE', 'N'); insert into hr.emp values (2, 'TWO', 'N'); insert into hr.emp values (3, 'THREE', 'Y'); SQL> col ora_archive_state for a3 select id, name, retired, ora_archive_state from hr.emp; ID NAME R ORA ---------- ------------------------------ - --- 1 ONE N 0 2 TWO N 0 3 THREE Y 0
– Archive the records of retired employees
SQL> update hr.emp set ora_archive_state=1 where id = 3;
– Let’s create a concatenated index on id, ora_archive_state columns
PDB1@CDB1> create index hr.emp_idx on hr.emp(id, ora_archive_state);
– Let’s try to add a duplicate record for id = 3 - fails
PDB1@CDB1> insert into hr.emp values (3, 'ANOTHER THREE', 'N'); insert into hr.emp values (3, 'ANOTHER THREE', 'N') * ERROR at line 1: ORA-00001: unique constraint (HR.SYS_C009861) violated
– Let’s issue various queries and check their execution plans
— It can be seen that all the queries are executed by accessing only the index on id and ora_archive_state (emp_idx)
PDB1@CDB1> select count(*) from hr.emp; Execution Plan ---------------------------------------------------------- Plan hash value: 660937672 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| EMP_IDX | 3 | 1 (0)| 00:00:01 | -------------------------------------------------------------------- PDB1@CDB1> select count(*) from hr.emp where id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 2081018037 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| EMP_IDX | 1 | 3 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1) PDB1@CDB1>select count(*) from hr.emp where id <3; Execution Plan ---------------------------------------------------------- Plan hash value: 2081018037 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| EMP_IDX | 2 | 6 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"<3) PDB1@CDB1> select count(*) from hr.emp where id between 1 and 3; Execution Plan ---------------------------------------------------------- Plan hash value: 2081018037 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| EMP_IDX | 3 | 9 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=3) Conclusion:
In case in-database archiving is enabled for a table, indexes on various columns should be appended by column ORA_ARCHIVE_STATE to ensure performance.
I hope this post was useful.
Your comments and suggestions are always welcome!
References:
http://www.orafaq.com/node/2824
———————————————————————————————-
Related links:
12c: In-database Archiving
12c: Improve Backup Performance Using In-database Archiving
12c: Performance Issue With In-database Archiving
Traditionally, the historical data needed for query purposes only was removed from the database and stored on tape, because
This solution has major disadvantages such as:
In-Database Archiving stores operational and archival data in the same database so that the data is still online and available and the cost is only slightly higher than storing this information on tape.
– Data is partitioned to distinguish active data from those in archived state.
– The partitions containing historical data
. are compressed and located on low cost storage tiers.
. are identified as read-only so that subsequent backups are not required after the initial database backup.
Demonstration:
– Create two tablespaces
SQL> create tablespace active_tbs; create tablespace archived_tbs;
– Create table to store employees’ details
SQL>create table emp (id number, name varchar2(30), retired char(1)) row archival partition by list(ora_archive_state) (partition p_active values(0) tablespace active_tbs, partition p_archived values (default) tablespace archived_tbs compress);
– Populate table –
SQL>begin
for i in 1..5 loop
insert into emp values (i, 'EMP'||i, 'N');
end loop;
for i in 6..10 loop
insert into emp values (i, 'EMP'||i, 'Y');
end loop;
end;
/
– Check that all the records are currently located in active_tbs as by default ora_archive_state=0
SQL> col name for a10
col retired for a7
col ora_archive_state for a15
select id, name, retired, ora_archive_state
from emp partition (p_active);
ID NAME RETIRED ORA_ARCHIVE_STA
———- ———- ——- —————
1 EMP1 N 0
2 EMP2 N 0
3 EMP3 N 0
4 EMP4 N 0
5 EMP5 N 0
6 EMP6 Y 0
7 EMP7 Y 0
8 EMP8 Y 0
9 EMP9 Y 0
10 EMP10 Y 0
10 rows selected.
– Try to archive the records of retired employees (retired = Y)
— We get error message as ora_archive_state is the partitining key and changing its value would cause rows to move from p_active to p_archived
SQL> update emp set ora_archive_state = 1 where retired='Y';
update emp set ora_archive_state = 1 where retired='Y'
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
– Enable row movement and then archive the records of retired employees (retired = Y)
SQL> alter table emp enable row movement;
update emp set ora_archive_state = 1
where retired='Y';
5 rows updated.
– Check that partition p_active contains records of employees which have not retired (retired = ‘N’)
SQL> select id, name, retired, ora_archive_state from emp partition (p_active); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 1 EMP1 N 0 2 EMP2 N 0 3 EMP3 N 0 4 EMP4 N 0 5 EMP5 N 0
– Check if records of employees which have retired (retired = ‘Y’)have moved to partition p_archived
— No records are displayed because by default only active rows are displayed
SQL> select id, name, retired, ora_archive_state from emp partition (p_archived);
no rows selected
– Enable visibility of archived rows also
SQL> alter session set row archival visibility = all;
– Check that records of employees which have retired (retired = ‘Y’)have moved to partition p_archived
SQL> select id, name, retired, ora_archive_state from emp partition (p_archived); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 6 EMP6 Y 1 7 EMP7 Y 1 8 EMP8 Y 1 9 EMP9 Y 1 10 EMP10 Y 1
– Since archived records will be accessed for read only, change the tablespace archived_tbs to read only
SQL> alter tablespace archived_tbs read only;
-- Try to modify data in archived records – disallows as the tablespace is read only
SQL> update emp set name = 'emp6' where id = 6;
update emp set name = 'emp6' where id = 6
*
ERROR at line 1:
ORA-00372: file 12 cannot be modified at this time
ORA-01110: data file 12:
'+DATA/CDB1/E7CFA41503C950F1E0438602A8C06FD7/DATAFILE/archived_tbs.279.827852189'
– Backup tablespace archived_tbs after it has been made read only
RMAN> connect target sys/oracle@pdb1
backup tablespace archived_tbs;
-- Exclude archived_tbs from whole database backups
RMAN> connect target sys/oracle@cdb1
configure exclude for tablespace pdb1:archived_tbs;
Tablespace PDB1:ARCHIVED_TBS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
I hope information in this post was useful.
Your comments and suggestions are always welcome!
—————————————————————————————–
Related Links:
12c : In-Database Archiving
12c : Performance Issue with In-Database Archiving
12c: Solution To Performance Issue With In-database Archiving
———————————————————————————————
In my earlier article on in-database archiving, I had demonstrated the functionality of this new feature introduced in Oracle 12c. But this feature you could also give problems with performance as pointed out by John Watson (http://www.orafaq.com/node/2824).
Let’s create a table of employees wherein we would like to archive the records of the employees who have retired.
SQL>create table hr.emp (id number primary key, name varchar2(30), retired char(1)) row archival; insert into hr.emp values (1, 'ONE', 'N'); insert into hr.emp values (2, 'TWO', 'N'); insert into hr.emp values (3, 'THREE', 'Y'); SQL> col ora_archive_state for a3 select id, name, retired, ora_archive_state from hr.emp; ID NAME R ORA ---------- ------------------------------ - --- 1 ONE N 0 2 TWO N 0 3 THREE Y 0
– Archive the records of retired employees
SQL> update hr.emp set ora_archive_state=1 where id = 3;
– Check that archived records are not visible now
SQL> select id, name, retired, ora_archive_state from hr.emp; ID NAME R ORA ---------- ------------------------------ - --- 1 ONE N 0 2 TWO N 0
– Let’s check that primary key constraint on the table won’t allow me to add an employee with same id as a retired employee
SQL> insert into hr.emp values (3, 'ANOTHER THREE', 'N'); insert into hr.emp values (3, 'ANOTHER THREE', 'N') * ERROR at line 1: ORA-00001: unique constraint (HR.SYS_C009857) violated
- Let’s create another table which is copy of hr.emp but without row archival clause
SQL> create table hr.emp_copy as select * from hr.emp; alter table hr.emp_copy add primary key(id);
– Verify that emp_copy does not contain column ora_archive_state
SQL> select id, name, retired, ora_archive_state from hr.emp_copy; select id, name, retired, ora_archive_state from hr.emp_copy * ERROR at line 1: ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier
– Let’s issue the same query against the two tables and check the execution plan
— In case of table without row archival, the query is executed by accessing the index only
SQL> set autot trace exp
select count(*) from hr.emp_copy where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2028575012
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C009854 | 1 | 3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
– In case of table with row archival, Table Access by RoWID is used in addition to Index Unique Scan , the reason being the filter
“ORA_ARCHIVE_STATE”=’0′ used while executing the query
SQL> select count(*) from hr.emp where id=1 2 ; Execution Plan ---------------------------------------------------------- Plan hash value: 1746399475 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2015 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 2015 | | | |* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 2015 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SYS_C009859 | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMP"."ORA_ARCHIVE_STATE"='0') 3 - access("ID"=1) ----------------
– We can try to resolve this problem by appending column ORA_ARCHIVE_STATE to the primary key
SQL> alter table hr.emp drop primary key; alter table hr.emp add primary key(id, ora_archive_state);
– Let’s check that now the query is executed using index access only
SQL> select count(*) from hr.emp ;
Execution Plan
----------------------------------------------------------
Plan hash value: 907615972
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2002 | | |
|* 2 | INDEX FULL SCAN| SYS_C009860 | 2 | 4004 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."ORA_ARCHIVE_STATE"='0')
filter("EMP"."ORA_ARCHIVE_STATE"='0')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
— But this could give rise to another problem related to constraint violation
– Let’s try to add an employee with id same as retired employee i.e. id = 3
— Oops.. It lets me do it!
SQL> insert into hr.emp values (3, 'ANOTHER THREE', 'N');
1 row created.
— Simply because primary key is composed of id and ora_archive_state and
ora_archive_state column is set to 0 (active/unarchived) by default
SQL> alter session set row archival visibility = all; select id, ora_archive_state, name, retired from hr.emp; ID ORA NAME R ---------- --- ------------------------------ - 1 0 ONE N 2 0 TWO N 3 1 THREE Y 3 0 ANOTHER THREE N
I still have to find the solution to this problem.
Thanks for your time!
References:
http://www.orafaq.com/node/2824
——————————————————————–
Related Links:
12c: In-Database Archiving
12c: Improve Backup Performance Using In-database Archiving
12c: Solution To Performance Issue With In-database Archiving
—————————————————————————————-
While playing with 12c, I had to repeatedly issue commands to identify the CDB/PDB I was currently in. So, I decided to modify my glogin.sql to set my SQL prompt to reflect my current CDB/PDB.
- Edit glogin.sql file
$vi $ORACLE_HOME/sqlplus/admin/glogin.sql
-- Add following lines to it
define gname=idle
column global_name new_value gname
set heading off
set termout off
col global_name noprint
select upper(sys_context ('userenv', 'con_name') || '@' || sys_context('userenv', 'db_name')) global_name from dual;
set sqlprompt '&gname> '
set heading on
set termout on
– Test that script is working as expected
– Connect to container database CDB1
SQL> conn sys/oracle@cdb1 as sysdba
- Verify that the prompt displays current container (CDB$ROOT) and container database (CDB1)
CDB$ROOT@CDB1> sho con_name CDB$ROOT CDB$ROOT@CDB1> sho parameter db_name db_name string cdb1
- Connect to pluggable database PDB1
CDB$ROOT@CDB1> conn sys/oracle@pdb1 as sysdba
- Verify that the prompt displays current container (PDB1) and container database (CDB1)
PDB1@CDB1> sho con_name PDB1 PDB1@CDB1> sho parameter db_name db_name string cdb1
Note: SQL prompt will not change and hence will not reflect current PDB name if Alter Session set container =…. is used to modify current container .
Happy Prompting!
——————————————————–
Related Links:
——————————————————–
In this post, I will demonstrate the conversion of a 12.1.0.1 non-CDB to a PDB. We will plug in 12.1.0.1 non-cdb named ncdb12c into a CDB called CDB2
Current scenario:
Source 12.1.0.1 non-cdb : ncdb12c
Target CDB : CDB2
Target PDB : ncdb12c
To get data from a Non-CDB to a PDB you have to do a convert of a Non-CDB to PDB. The conversion process is similar to unplug and plug of a pdb.
Overview:
– Log into ncdb12c as sys
– Get the database in a consistent state by shutting it down cleanly.
– Open the database in read only mode
– Run DBMS_PDB.DESCRIBE to create an XML file describing the database.
– Shut down ncdb12c
– Connect to target CDB (CDB2)
– Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)
– Plug-in Non-CDB (NCDB12c) as PDB(NCDB12c) into target CDB(CDB2).
– Access the PDB and run the noncdb_to_pdb.sql script.
– Open the new PDB in read/write mode.
Implementation:
- Log into ncdb12c as sys and check that it is a non CDB
NCDB12C>sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string ncdb12c NCDB12C>select name, cdb from v$database; NAME CDB --------- --- NCDB12C NO NCDB12C>select instance_name from v$instance; INSTANCE_NAME ---------------- NCDB12C
-- Find out names of datafiles for NCDB12c (needed for FILE_NAME_CONVERT)
NCDB12C>select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ncdb12c/system01.dbf /u01/app/oracle/oradata/ncdb12c/sysaux01.dbf /u01/app/oracle/oradata/ncdb12c/undotbs01.dbf /u01/app/oracle/oradata/ncdb12c/users01.dbf
To convert it to be a PDB we first need to get the database in a consistent state and then run DBMS_PDB.DESCRIBE to create an XML file to describe the database.
- Shutdown the database cleanly and open it in read only mode
SQL> shutdown immediate; startup mount; alter database open read only; exec dbms_pdb.describe (PDB_DESCR_FILE=>'/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml'); ho ls -l /u01/app/oracle/oradata/ncdb12c/ncdb12c.xml -rw-r--r-- 1 oracle asmadmin 3994 Jul 24 16:15 /u01/app/oracle/oradata/ncdb12c/ncdb12c.xml
- Shutdown the source non-CDB (NCDB12c)
SQL> shutdown immediate; exit
Now we can plug NONCDB into a existing CDB database cdb2
– connect to target CDB (CDB2)
SQL>conn sys/oracle@cdb2 as sysdba
select name, CDB from v$database;
NAME CDB
---------- ---
CDB2 YES
– Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)
SQL>
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml',
pdb_name => 'NCDB12C')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
NO
- If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view
SQL> col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB12C'; NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- NCDB12C Non-CDB to WARNING PDB plugged in is a non-CDB, PENDING PDB requires noncdb_to_pdb.sql be run. NCDB12C Parameter WARNING CDB parameter sga_target mismatch: PENDING Previous 394264576 Current 1895825408 NCDB12C Parameter WARNING CDB parameter pga_aggregate_target PENDING mismatch: Previous 131072000 Current 629145600
There are WARNING’s only. We can continue.
– Make the target directory for datafiles and create pluggable database
SQL> ho mkdir -p /u02/app/oracle/oradata/cdb2/ncdb12c
– Plug-in Non-CDB (NCDB12c) as PDB (NCDB12c):
I am plugging the database in to a CDB on the same server with COPY clause and hence using FILE_NAME_CONVERT.
SQL> CREATE PLUGGABLE DATABASE ncdb12c USING '/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml'
COPY
file_name_convert=('/u01/app/oracle/oradata/ncdb12c','/u02/app/oracle/oradata/cdb2/ncdb12c');
Pluggable database created.
– check that datafiles for the plugagble database ncdb12c have been created in the location specified in FILE_NAME_CONVERT
SQL> ho ls /u02/app/oracle/oradata/cdb2/ncdb12c/* /u02/app/oracle/oradata/cdb2/ncdb12c/sysaux01.dbf /u02/app/oracle/oradata/cdb2/ncdb12c/system01.dbf /u02/app/oracle/oradata/cdb2/ncdb12c/temp01.dbf /u02/app/oracle/oradata/cdb2/ncdb12c/users01.dbf
– check that newly created PDB NCDB12c is in mounted state
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ---------- ---------- PDB$SEED READ ONLY PDB1 MOUNTED NCDB12C MOUNTED
– check that status of newly created PDB NCDB12c is NEW
SQL> col pdb_name for a15
select pdb_name, status from dba_pdbs where pdb_name = 'NCDB12C';
PDB_NAME STATUS
--------------- -------------
NCDB12C NEW
–Run noncdb_to_pdb.sql script, complete the following steps:
- Access the newly creatd PDB.
SQL> alter session set container=ncdb12c;
sho con_name
CON_NAME
------------------------------
NCDB12C
- Run the noncdb_to_pdb.sql script:
NCDB12c>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
– connect to CDB and verify that warnings for sga_target and pga_aggregate_target mismatch have been resolved
SQL>conn sys/oracle@cdb2 as sysdba col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB12C'; NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- NCDB12C Parameter WARNING CDB parameter sga_target mismatch: RESOLVED Previous 394264576 Current 1895825408 NCDB12C Parameter WARNING CDB parameter pga_aggregate_target RESOLVED mismatch: Previous 131072000 Current 629145600 NCDB12C Non-CDB to ERROR PDB plugged in is a non-CDB, PENDING PDB requires noncdb_to_pdb.sql be run.
You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.
– Open the new PDB in read/write mode.
SQL>shu immediate; alter pluggable database ncdb12c open;
– check that the status of NCDB12c changes to OPEN now
SQL>select pdb_name, status from dba_pdbs;
PDB_NAME STATUS
--------------- -------------
PDB1 NORMAL
PDB$SEED NORMAL
NCDB12C NORMAL
– verify that warning for requires noncdb_to_pdb.sql be run has also been resolved
SQL> col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB12C';SQL> SQL> SQL> NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- NCDB12C Parameter WARNING CDB parameter sga_target mismatch: RESOLVED Previous 394264576 Current 1895825408 NCDB12C Parameter WARNING CDB parameter pga_aggregate_target RESOLVED mismatch: Previous 131072000 Current 629145600 NCDB12C Non-CDB to ERROR PDB plugged in is a non-CDB, RESOLVED PDB requires noncdb_to_pdb.sql be run. Note:
If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB’s state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.
References:
Oracle documentation
————————————————————————————————
Related Links:
Clone An Existing PDB As Non-SYS User
——————————————————————————————————————
Let’s see how to access EM express for various types of databases.
Oracle 12c has introduced multitenant architecture in which we can have different types of databases:
– CDB’s
– PDB’s
– Non CDB’s
Let’s see how to access EM express for various types of databases:
At the root container, data is displayed for the entire CDB, and all actions taken impact all containers. Data pertaining to a PDB, such as a user or a SQL statement, is qualified with the container name. The actions a user can perform at the root container include:
– viewing and setting initialization parameters;
– administering common users and privileges; and
– managing memory configuration, undo information and redo logs.
- Find out the port on which EM Express is configured:
Three methods are available:
1. When dbca completes, note down the the port on which EM Express is configured as displayed.
2.Find out status of the listener
[oracle@em12c /]$ lsnrctl status
(output trimmed)
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=em12c.oracle.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
3. Login to CDB as issue the sql query as follows:
SQL> conn sys/oracle@cdb1 as sysdba
select dbms_xdb_config.gethttpsport from dual;
GETHTTPSPORT
------------
5500
- Access EM express by entering following URL in the browser:
https://<hostname>:5500/em
When a user is connected to a PDB, data is displayed only for the container in question, and actions only impact that container.
- Login to PDB
SQL> alter session set container=pdb1; sho con_name CON_NAME ------------------------------ PDB1
- Configure the port (5501) for EM express as follows:
SQL> exec dbms_xdb_config.sethttpsport(5501);
- Confirm that EM has been configured on specified port (1501)
SQL> select dbms_xdb_config.gethttpsport from dual;
GETHTTPSPORT
------------
5501
- Access EM express by entering following URL in the browser:
https://<hostname>:5501/em
For a non- CDB, and all actions a user can perform in a non-CDB are available:
– viewing and setting initialization parameters;
– administering common users and privileges;
– managing memory configuration, undo information and redo logs.
– managing tablespaces etc.
- Find out the port on which EM Express is configured:
Two methods are available:
1. When dbca completes, note down the the port on which EM Express is configured as displayed.
2. Login to non-CDB (Note container name as Non consolidated)
SQL> conn sys/oracle@ncdb as sysdba
sho con_name
CON_NAME
------------------------------
Non Consolidated
- Issue the sql query as follows:
select dbms_xdb_config.gethttpsport from dual;
GETHTTPSPORT
------------
5502
- Access EM express by entering following URL in the browser:
https://<hostname>:5502/em
Above method will work if non-CDB is registered with the default listener running on port 1521. In case non-CDB is registered with the non-default listener, please follow the method as given in this post.
References:
http://www.oracle.com/technetwork/database/manageability/emx-intro-1965965.html#A5
http://www.oracle.com/technetwork/database/manageability/emx-cdb-1965987.html
http://docs.oracle.com/database/121/ADMQS/em_manage.htm#ADMQS033
How to Configure EM Express For A Single PDB?
———————————————————————————————
Related Links:
12c: Unable To Access EM Express For Non-CDB
Error : NMO not setuid-root (Unix Only)