Tag Archives: oracle 12c

Renaming and Relocating Online Data Files in Oracle 12c

Renaming and Relocating Online Data Files in Oracle 12c

In Oracle 12c, New feature is given from moving the datafiles from one location to another without downtime of the environment.
It increase the great flexibility of the Oracle Database moving datafiles to one location to other without downtime like move datafiles which is less needed to low cost storage or Move datafile to ASM Storage online without downtime.

Renaming an Online Data File

--renames the data file user1.dbf to user01.dbf while keeping the data file in the same location
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user1.dbf' TO '/u02/oradata/ORCL/user01.dbf';

Relocating or Move the online Data file

-- Move the datafile from one location to anther online.
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf';

Copying an Online Data File
KEEP keyword is used to keep the old file as its and copy file to new location

-- Copy the datafile from one location to anther without removing the old location file.
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf' keep;

Relocating or Move an Online Data File and Overwriting an Existing File
REUSE Keyword is used to overwrite the existing file with same name present in Destination folder

ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf' REUSE;

Move or Relocate an online data file to ASM

ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '+dg01/data/orcl/datafile/user01.dbf';

Advertisements

WITH MONITOR_DATA AS SELECT INST_ID query found caused Performance issue

WITH MONITOR_DATA AS SELECT INST_ID Performance issue in oracle 12c

The following statement causing lot of execution and consuming lot of CPU in my AWR report.

In Oracle 12c, new feature is introduced called as “Automatic Report Capturing Feature”

WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT
.....
DBMS_XPLAN.BUILD_PLAN_XML( TABLE_NAME=>'gv$sql_plan', PLAN_TAG=>'plan', FILTER_PREDS=>:B35 , FORMAT=>'-PROJECTION +ALIAS +ADAPTIVE')
ELSE NULL END XPLAN_XML FROM DUAL) V1) CONST_VIEW

It is related with optimizer_adaptive_plans
If the CPU consumption is high then it is not an expected behaviour, So we need to disable the monitoring of MMON_SLAVE.

Solution:
Disable the following parameter for stop monitoring the database:
Default setting is 60 seconds

alter system set "_report_capture_cycle_time"=0;

Cloning of PDB database into same CDB database Oracle

Cloning of PDB database into same CDB database in Oracle 12c

Step 1: Create new directory in oradata folder e.g PDB2 (location where you want to create database).

Step 2: Connect to container database( CDB)
sqlplus sys as sysdba

Step 3: Check the pluggable database status

SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
-------- -----------
PDB$SEED READ ONLY
PDB1 READ WRITE

Step 4: Close the pdb1 database

SQL> alter pluggable database pdb1 close;
Pluggable database altered.

step 5: Open in read only mode PDB1 database.

SQL> alter pluggable database pdb1 open read only;
Pluggable database altered.

Step 6: Start cloning process from pdb1 to pdb2.

Option 1:It will create files as oracle managed file system otherwise you need to specify manually each file.

SQL> create pluggable database pdb2 from pdb1 create_file_dest='D:\container\oradata\cdb1\pdb2';

Option 2: It will create the exact struture as present in old pdb.

SQL> CREATE PLUGGABLE DATABASE pdb3 FROM pdb1
FILE_NAME_CONVERT = ('D:\container\oradata\cdb1\pdb1\', 'D:\container\oradata\cdb1\pdb3\')
PATH_PREFIX = 'D:\container\oradata\cdb1\pdb3';
Pluggable database created.

Step 7: Check the database

SQL> select name,open_mode from v$pdbs;

NAME      OPEN_MODE
--------  -----------
PDB$SEED  READ ONLY
PDB1      READ ONLY
PDB2      MOUNTED

Step 8: Open the database for user

alter pluggable database pdb1 close;
alter pluggable database pdb1 open;

alter pluggable database pdb2 close;
alter pluggable database pdb2 open;

Step 9: check file location and status

select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn;

select con_id, tablespace_name, File_Name from cdb_data_files order by 1, 2;

Step 10: Drop the PDB database

-- It will keep datafiles on disk only temp tablespace is deleted
DROP PLUGGABLE DATABASE pdb1 KEEP DATAFILES;

-- It will drop all the datafiles associated with pluggable database
DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;