Another Post Describe how to install Oracle SOA, Steo by Step To Install Oracle SOA and Extend Admin Server.
As Usual you can download this document From SlideShare.
Download Document Here.
Thank you
Osama Mustafa
Tag: Osama 12c
SIEBEL odbcsql Unable to Connect
Logging into “SBA_81_DSN” as “SADMIN” …
ODBC error 81 in SQLConnect:
523 72
(native error 0).
Unable to login using specified ODBC parameters.
The above error appear when you are trying to run the below command:
odbcsql /s SIEBEL8_DSN /u sadmin /p sadmin
Solutions :
Make sure the you set TNS_ADMIN in .profile or .bash_profile , TNS_ADMIN Should be Directed to Client32 bit , Copy tnsnames.ora from Database home to Client32
it will be like the following :
export TNS_ADMIN=/u01/app/oracle/instanctclient32/
Thank you
Osama mustafa
Mount NFS Device Is Busy
This Error Appear On Solaris when you try to mount filesystem :
mount -F nfs IP:/export/home/oracle/test /export/home/oracle/test
Output :
nfs mount: mount: /export/home/oracle/test : Device busy
Check why it’s Busy using :
fuser -u /export/home/oracle/test
/export/home/oracle/test : 1432c(oracle)
Kill the process :
kill – 9 1432
Thank you
Osama mustafa
Step By Step to Restore RMAN to New Host
Old Topics But Always Good To share , Find Below the Links for Steps to Restore RMAN to New Host
Step by Step Restore RMAN to New Host Here
Thank you
Osama Mustafa
Adopting NON-CDB to CBD
This article describe steps to Adopt non-container database 12c to container 12c
[oracle@test12c Desktop]$ ps -ef | grep pmon
oracle 3230 1 0 04:04 ? 00:00:09 ora_pmon_db12c
oracle 12112 1 0 08:20 ? 00:00:07 ora_pmon_NonCDB
oracle 29621 3203 0 18:47 pts/1 00:00:00 grep pmon
Where :
db12c : Container database
NonCDB : Non Container Database
Let Start
[oracle@test12c Desktop]$ export ORACLE_SID=NonCDB
SQL> select instance_name from v$instance ;
INSTANCE_NAME
—————-
NonCDB
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2295080 bytes
Variable Size 348130008 bytes
Database Buffers 583008256 bytes
Redo Buffers 6062080 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
Run the below procedure to generate the manifest file.
SQL> exec dbms_pdb.describe(pdb_descr_file=>’/u01/noncdb.xml’);
PL/SQL procedure successfully completed.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
I already working on the same host so no need to copy datafiles,if you are not working on same host copy datafiles to same location.
Let’s Work On Container Database .
after copy datafiles to folder, On Os Level , Create directory to use file_name_convert.
[oracle@test12c oradata]$ mkdir newnoncdb
[oracle@test12c newnoncdb]$ pwd
/u01/app/oracle/oradata/newnoncdb
[oracle@test12c Desktop]$ export ORACLE_SID=db12c
[oracle@test12c Desktop]$ sqlplus / as sysdba
SQL> create pluggable database non_cdb as clone
2 using ‘/u01/noncdb.xml’
3 file_name_convert=(‘/u01/app/oracle/oradata/NonCDB’,’/u01/app/oracle/oradata/newnoncdb’) copy;
Pluggable database created.
Where
- using ‘/u01/noncdb.xml’ : file that should be generated using dbms_pdb.describe
- ‘/u01/app/oracle/oradata/NonCDB’ : location for Copied Non_CDB datafile
- ,’/u01/app/oracle/oradata/newnoncdb’ : location for created directory.
SQL> alter pluggable database non_cdb open ;
Pluggable database altered.
SQL> alter pluggable database non_cdb close ;
Pluggable database altered.
SQL> alter pluggable database non_cdb open ;
Pluggable database altered.
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWDB_PLUG MOUNTED
NEW_PLUG_COPY MOUNTED
NEW_PLUG_MOVE MOUNTED
NON_CDB READ WRITE
Now you plugged NON-CDB to Container Database successfully without any problems and it will works fine.
the below step is optional but recommanded for production to move all thing from non-cdb to cdb. ( specially for upgrade 12c later )
SQL> alter session set container=Non_cdb;
Session altered.
OR
[oracle@test12c newnoncdb2]$ sqlplus sys/sys@test12c:1521/Non_cdb as sysdba ;
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Thank you
Osama Mustafa
Pluggable Database Tutorial (12c) Part 2
In my Part 1 Tutorial for how to deal with Pluggable database 12c I mentioned how to use pluggable database , How to create pluggable database , how to create tablespace , and how to open/close pluggable database .
Please check the Part ( 1 ) before continue reading this article here.
Welcome to Pluggable database Part 2
- Rename Pluggable Database
- Manage Pluggable database
- Drop Pluggable database
- Security In Pluggable database
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
—————————– ———-
TEST 3
as you see in the above query, I already created pluggable database called test. and it’s in Read write Mode.
Rename Pluggable database
SQL> alter pluggable database TEST close immediate ;Pluggable database altered.
SQL> alter pluggable database TEST open restricted ;Pluggable database altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
TEST READ WRITE
SQL> alter pluggable database TEST rename global_name to new ;Pluggable database altered.
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
————————————— ———-
new 3
if you are not connected to pluggable database or set session container then you will recicve error message
ORA-65046: operation not allowed from outside a pluggable
SQL> alter pluggable database new close immediate ;Pluggable database altered.
SQL> alter pluggable database new open ;Pluggable database altered.
Manage Pluggable Database
Back to root container using / as sysdba like below :
SQL> conn / as sysdba
Connected.
SQL> select name, con_id from v$active_services order by 1;NAME CON_ID
————————————————— ———-
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
new 3
We Control which list for for tablespace & datafiles by using con_id.
- List tablespace in root container
SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=1;
TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1
CDATA 1
- List Database In root Container
SQL> select file_name, con_id from cdb_data_files where con_id=1;
FILE_NAME CON_ID
————————————————————————— ———-
/u01/app/oracle/oradata/db12c/users01.dbf 1
/u01/app/oracle/oradata/db12c/undotbs01.dbf 1
/u01/app/oracle/oradata/db12c/sysaux01.dbf 1
/u01/app/oracle/oradata/db12c/system01.dbf 1
/u01/app/oracle/oradata/db12c/gls/test.dbf 1
- Temp Tablespace in root container
SQL> select file_name, con_id from cdb_temp_files where con_id=1;
FILE_NAME CON_ID
—————————————————————————– ———-
/u01/app/oracle/oradata/db12c/temp01.dbf 1
- Create Tablespace ( already mentioned in Part (1))
SQL> create tablespace test datafile ‘/u01/app/oracle/oradata/db12c/gls/test03.dbf’ size 20M; Tablespace created.
SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;
TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 1
TEST 1
CDATA 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSAUX 2
TEMP 2
SYSTEM 2
TEMP 3
SYSAUX 3
PDB_TEST 3
SYSTEM 3
14 rows selected.
- Create temp tablespace
SQL> create temporary tablespace temp_test tempfile ‘/u01/app/oracle/oradata/db12c/gls/temp_test.dbf’ size 20M ;
Tablespace created.
SQL> select file_name, con_id from cdb_temp_files where con_id=1;
FILE_NAME CON_ID
————————————————————————— ———-
/u01/app/oracle/oradata/db12c/temp01.dbf 1
/u01/app/oracle/oradata/db12c/gls/temp_test.dbf 1
The Same Steps in root container for create tablespace and temp tablespace for pluggable database.
Security In Pluggable Database
In This Section we will discuss how to manage Users, roles and privileges.
before Demonstration you need to know what is the difference between two users type :
- Common : when you create this kind of users in root it’s automatically replicated in all Pluggable database.
- Local : this kind of users only created on pluggable database that you are connected to it now. and dose not effect on others pluggable database.
To Create Common Users you need to be connected to root container.
SQL> conn / as sysdba
Connected.
SQL> create user c##osama identified by osama ;User created.
SQL> select username, common, con_id from cdb_users where username like ‘C##%’;
USERNAME COM CON_ID
———————————— — ———-
C##TEST YES 1
C##OSAMA YES 1
C##TEST YES 3
C##OSAMA YES 3
SQL> grant create session to c##osama ;Grant succeeded.
SQL> conn c##osama/osama@test12c:1521/db12c ;
Connected.
Let’s connect to pluggable database :
The user i will created it here will not appear in root container.
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.
SQL> create user test identified by test ;User created.
SQL> grant create session to test ;Grant succeeded.
SQL> select username, common, con_id from cdb_users where username =’TEST’;
USERNAME COM CON_ID
—————— ———-
TEST NO 3
SQL> conn test/test@test12c:1521/new ;
Connected.
Same rules and conditions applied on Roles if you created in Root Container it will be replicated to pluggable database, on other hand if you created in Pluggable database it will be local without effecting Container.
Let’s connect to Root Container
SQL> conn / as sysdba
Connected.
SQL> create role c##root_role ;Role created.
SQL> select role, common, con_id from cdb_roles where role=’C##ROOT_ROLE’;
ROLE COM CON_ID
——————— — ———-
C##ROOT_ROLE YES 1
C##ROOT_ROLE YES 3
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.
SQL> create role test2;Role created.
SQL> select role, common, con_id from cdb_roles where role=’TEST2′;
ROLE COM CON_ID
—————- — ———-
TEST2 NO 3
SQL> create role hr container=all ;
create role hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
SQL> create user hr identified by hr container=all ;
create user hr identified by hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
—> you cannot create a common role inside a PDB.
Check user privileges :
SQL> select grantee, privilege, common, con_id from cdb_sys_privs
where privilege=’CREATE SESSION’ and grantee=’TEST’;
GRANTEE PRIVILEGE COM CON_ID
————– —————————————
TEST CREATE SESSION NO 3
Drop Pluggable Database
SQL > drop pluggable database new including datafiles;
Pluugable database dropped.
This document also available on slidshare here
Thank you
Osama mustafa
Enterprise manager Performance Hub (Real Time) 12c
Enterprise manager express is
SQL> drop user osama cascade ;
User dropped.
SQL> create user osama identified by osama;
User created.
SQL> grant select any table to osama;
Grant succeeded.
SQL> grant select any dictionary to osama ;
Grant succeeded.
SQL> grant create session to osama;
Grant succeeded.
SQL> conn osama/osama ;
Connected.
SQL> EXEC DBMS_SQL_MONITOR.BEGIN_OPERATION (‘My_First_JOB’, forced_tracking => ‘Y’);
BEGIN DBMS_SQL_MONITOR.BEGIN_OPERATION (‘My_First_JOB’, forced_tracking => ‘Y’); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: ‘BEGIN_OPERATION’ is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
to avoid this error :
SQL> VAR myjob_id NUMBER;
SQL> EXEC :myjob_id := DBMS_SQL_MONITOR.BEGIN_OPERATION (‘My_First_JOB’, forced_tracking => ‘Y’)
PL/SQL procedure successfully completed.
Where
myjob_id : variable
My_first_Job : Job name.
Now Check Enterprise manager express Page :
if you check the second picture you will see if you don’t stop the sql_monitor the Job will still running
SQL> EXEC DBMS_SQL_MONITOR.END_OPERATION(‘My_First_JOB’, :myjob_id) ;
PL/SQL procedure successfully completed.
Thank you
Osama Mustafa
Technology Geek 