Posts

Migrating an Oracle 12c Database to a 18c Pluggable Database in the Oracle Cloud

Testing transport tablespace - Migrating an Oracle 12c Database to a 18c Pluggable Database in the Oracle Cloud

++++++++++++++++++++++
On-premises 12cR2 database

++++++++++++++++++++++
Create a tablespace CREATE SMALLFILE TABLESPACE TESTTBS DATAFILE '/opt/oracle/oradata/TSOC/tstdb01.dbf' SIZE 300M AUTOEXTEND ON NEXT 1 MAXSIZE 500M SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL AUTOALLOCATE; Tablespace created.
Create the user test in the tablespace
SYS@TSOC > CREATE USER test IDENTIFIED BY test
DEFAULT TABLESPACE "TESTTBS" TEMPORARY TABLESPACE "TEMP";  2    3
User created.
SYS@TSOC >  ALTER USER test QUOTA UNLIMITED ON TESTTBS;
User altered.
SYS@TSOC > GRANT CONNECT, RESOURCE to test;
Grant succeeded.


Create a TEST TABLE SYS@TSOC > CREATE TABLE test.test01 (id number, name varchar2(100));
Table created.
SYS@TSOC > INSERT INTO test.test01 (ID, NAME) VALUES ('1', 'DOE');  INSERT INTO test.test01 (ID, NAME) VALUES ('2', 'SOE');  …

Oracle 18c : Database creation using DBCA

Oracle 18c : Database creation using DBCA
Click below link - 

Oracle18c create-database-using-dbca




Recovering Pluggable Database (PDB) table in Container Database (CDB)

Recovering Pluggable Database (PDB) table in Container Database (CDB)
Connect to the PDB and create a test table 
SQL> connect HR/Password#123@pdb1 Connected. SQL> set echo on SQL> create table EMPLOYESS_TEST as select * from EMPLOYEES;
Table created.
SQL> select count (*) from EMPLOYESS_TEST;
  COUNT(*) ----------        107
SQL> alter session set container=pdb1;
Session altered.
SQL> select timestamp_to_scn(sysdate) from v$database;
TIMESTAMP_TO_SCN(SYSDATE) -------------------------                   3337108
SQL> SELECT NAME, CURRENT_SCN FROM V$DATABASE;
NAME      CURRENT_SCN --------- ----------- TESTDB03      3337119
NOTE this SCN which will be used to recover the table.
Take an RMAN Backup of the PDB 
SQL> ho [oracle@TESTDB03 ~]$  rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Sat Mar 3 23:33:06 2018 Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDB03 (DBID…

Oracle 18c : Testing - Installing Sample Schema

18c- Install Sample Schema  This document is relate to create sample schema under pluggable database.
SQL> alter session set container=PDB1; Session altered.
Create a tablespace to use for sample schema SQL> create tablespace example01 datafile '/u02/app/oracle/oradata/TESTDB03/PDB1/example01.dbf' size 100M autoextend on next 10M maxsize 500M; Tablespace created.
Here I am creating HR sample schema SQL> @?/demo/schema/human_resources/hr_main.sql
specify password for HR as parameter 1: Enter value for 1: Password#123
specify default tablespeace for HR as parameter 2: Enter value for 2: example01
specify temporary tablespace for HR as parameter 3: Enter value for 3: TEMP
specify log path as parameter 4: Enter value for 4: /u02/app/oracle/oradata/TESTDB03/PDB1

PL/SQL procedure successfully completed.

User created.

User altered.

User altered.

Grant succeeded.

Grant succeeded.

Session altered.

Session altered.

Session altered.
******  Creating REGIONS table ....

Table cre…

FLASHBACK RAC DATABASE WITHOUT STANDBY DATABASE

RAC Database -> Flashback to Different Restore Points
Precheck ============= SQL>select name,time from v$restore_point; NAMETIME --------------------------------------------------------------------------- BEFORE_UPGRADE21-FEB-18 04.37.05.000000000 PM
Flashback is off with guarantee flashback database
SQL> select log_mode,flashback_on from v$database;
LOG_MODEFLASHBACK_ON ------------------------------------ ------------------------------------------------------ ARCHIVELOGRESTORE POINT ONLY
I am going to create a restore point before changing further
Create a restore point RP02 ======================== SQL> create restore point RP02 guarantee flashback database; Restore point created.
Doing some change to the database [Create a table test03 – After RP02 and before RP03]
SQL> create table test03 (col1 number) tablespace users; Table created.
SQL> insert into test03 values ('03'); 1 row created.
SQL> commit; Commit complete.
Create another restore point RP03 ======================…