The alternative for relocating ASM datafiles by RMAN commands :
CATALOG START WITH “+DATA2/../”
SWITCH DATABASE TO COPY.
TASK
This task is to relocate all the datafiles of diskgroup DATA1 to another diskgroup DATA2 for a standby database.
SOULUTION
1)Check the diskgroup space availability.
SQL> set pagesize 120
SQL> set linesize 180
SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024,USABLE_FILE_MB/1024
from v$asm_diskgroup;
NAME T TOTAL_MB/1024 FREE_MB/1024 USABLE_FILE_MB/1024
--------- ------------- ------------ -------------------
DATA1 1024.00977 15.6894531 15.6894531
OCR_VOTE 19.9931641 15.4257813 15.4257813
FRA 500.069336 210.331055 210.331055
DATA2 4000.55469 1816.02344 1816.02344
2)Leave managed standby database recovery still going. Create a RMAN script by using below query:
SQL> select 'backup as copy datafile '||file#||' format ''+DATA2'';'
from v$datafile
where name like '+DATA1%' order by 1;
'BACKUPASCOPYDATAFILE'||FILE#||'FORMAT''+DATA2'';'
---------------------------------------------------
backup as copy datafile 124 format '+DATA2';
backup as copy datafile 125 format '+DATA2';
backup as copy datafile 126 format '+DATA2';
backup as copy datafile 127 format '+DATA2';
.....
....
...
..
.
The final RMAN script content:
$cat move_DATA1_to_DATA2.rman
run {
allocate channel stdbych1 type disk;
allocate channel stdbych2 type disk;
allocate channel stdbych3 type disk;
allocate channel stdbych4 type disk;
backup as copy datafile 124 format '+DATA2';
backup as copy datafile 125 format '+DATA2';
backup as copy datafile 126 format '+DATA2';
backup as copy datafile 127 format '+DATA2';
....
...
..
.
}
3) Run the RMAN script to make a copy of all datafiles on diskgroup DATA1 onto diskgroup DATA2.
SQL> alter database recover managed standby database cancel;
Database altered.
5) Switch the datafiles.
RMAN>switch datafile 124 to copy;
RMAN>switch datafile 125 to copy;
RMAN>switch datafile 126 to copy;
RMAN>switch datafile 127 to copy;
....
...
..
.
6) Start the recovery.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
5) Archive log switch, check applied from alert.log, etc.
SQL>alter system archive log current ;
System altered.
6) Unmount diskgroup DATA1 on all the nodes, except the current node.
SQL> alter diskgroup data1 dismount;
Diskgroup altered.
7) Drop the diskgroup DATA1.
SQL> drop diskgroup data1;
drop diskgroup data1
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "DATA1" contains existing files
SQL> drop diskgroup data1 including contents;
Diskgroup dropped.
8) Scan disks and confirm the ASM Disks of DATA1 have been removed from ASM disks.
$su -
# oracleasm listdisks
ASM_DISK01
ASM_DISK02
ASM_DISK03
ASM_DISK04
ASM_DISK05
ASM_DISK06
ASM_DISK07
ASM_DISK08
ASM_FRA01
ASM_SSD1 <----- DATA1 diskgroup
OCR_VOTE01
# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Cleaning disk "ASM_SSD1"
Scanning system for ASM disks...
# oracleasm listdisks
ASM_DISK01
ASM_DISK02
ASM_DISK03
ASM_DISK04
ASM_DISK05
ASM_DISK06
ASM_DISK07
ASM_DISK08
ASM_FRA01
OCR_VOTE01
Oracle opatch utility should have used the latest available JRE to avoid those kind of TZ issues. It is a bug obviously.
SYMPTOMS
For 12.1.0.2 GI Home, opatch 12.2.0.1.5 and later versions throw below errors :
$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.5
Copyright (c) 2016, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/12.1.0.2/grid
Central Inventory : /u01/app/oraInventory
from : /u01/app/12.1.0.2/grid/oraInst.loc
OPatch version : 12.2.0.1.5
OUI version : 12.1.0.2.0
Log file location : /u01/app/12.1.0.2/grid/cfgtoollogs/opatch/opatch2016-09-08_17-08-01PM_1.log
sInstallTime = >2016.Aug.09 08:27:15 AEST<
java.text.ParseException: Unparseable date: "2016.Aug.09 08:27:15 AEST"
at java.text.DateFormat.parse(DateFormat.java:337)
at oracle.sysman.oii.oiii.OiiiCompsXMLHandler$OiiiInstallCompElementHandler.onStart(OiiiCompsXMLHandler.java:923)
at oracle.sysman.oix.oixs.OixsElementHandler.startElement(OixsElementHandler.java:140)
CAUSE
opatch is using $ORACLE_HOME/jdk/jre, which is version 1.6. But opatch has its own jre version 1.8, which is not used by opatch. it does not make sense.
TZ entry AEST is added into $ORACLE_HOME/inventory/ContentsXML/comps.xml.
Before opatch 12.2.0.1.5, TZ EST was used instead. Java1.6 does not understand AEST.
Create a container database (CDB) with selected options through running modified database creation scripts created by DBCA.
Creating a container database ( CDB) by using DBCA will install all the options by default. There is no way to install selective options.
Here is a practical example of manually creating a CDB with selective options — Oracle JVM, TEXT, SPATIAL In 12.1.0.x.
A. DBCA to create CDB creation scripts
Add $ORACLE_HOME/perl/bin to the beginning of the PATH variable. Otherwise there may be compilation errors in catcon.pm because the default perl installation doesn’t have enough packages.
export PATH=$ORACLE_HOME/perl/bin:$PATH
1) Kick off $ORACLE_HOME/bin/dbca, choose “Create Database“.
2) Select the Advanced Mode.
3) Choose right “Database Type” and “Configuration Type”. Select the Custom Database from the list of database templates.
4) Give Database Name, PDB name.
5) Select the available nodes.
6) Tick the box.
7) Give database credentials.
8) Specify storage choices.
9) Database options. Installing all options is compulsory.
10) Database Vault and Label Security options.
11) Oracle database parameters.
12) Don’t choose “Create database”.
13) Prerequisites check progressing….
14) Review the SUMMARY, and click the “Close” when it finishes.
B. Review the scripts
1)Shell scripts and sql scripts generated by DBCA.
$ cd /u01/app/oracle/admin/RACTEST/scripts
$ ls -ltr
-rw-r----- 1 oracle oinstall 329 Oct 24 11:25 spatial.sql
-rw-r----- 1 oracle oinstall 354 Oct 24 11:25 ordinst.sql
-rw-r----- 1 oracle oinstall 1154 Oct 24 11:25 lockAccount.sql
-rwxr-xr-x 1 oracle oinstall 699 Oct 24 11:25 RACTEST1.sh
-rw-r----- 1 oracle oinstall 338 Oct 24 11:25 labelSecurity.sql
-rw-r----- 1 oracle oinstall 1145 Oct 24 11:25 JServer.sql
-rw-r----- 1 oracle oinstall 336 Oct 24 11:25 interMedia.sql
-rw-r----- 1 oracle oinstall 359 Oct 24 11:25 datavault.sql
-rw-r----- 1 oracle oinstall 347 Oct 24 11:25 cwmlite.sql
-rw-r----- 1 oracle oinstall 1636 Oct 24 11:25 CreateDBCatalog.sql
-rw-r----- 1 oracle oinstall 543 Oct 24 11:25 CreateClustDBViews.sql
-rw-r----- 1 oracle oinstall 982 Oct 24 11:25 context.sql
-rw-r----- 1 oracle oinstall 380 Oct 24 11:25 apex.sql
-rw-r----- 1 oracle oinstall 392 Oct 24 11:25 postPDBCreation_RACTESTPDB.sql
-rw-r----- 1 oracle oinstall 1899 Oct 24 11:25 postDBCreation.sql
-rw-r----- 1 oracle oinstall 453 Oct 24 11:25 plug_RACTESTPDB.sql
-rw-r----- 1 oracle oinstall 94 Oct 24 11:25 PDBCreation.sql
-rwxr-xr-x 1 oracle oinstall 2310 Oct 24 17:25 RACTEST1.sql
-rw-r----- 1 oracle oinstall 1166 Oct 24 22:26 CreateDB.sql
-rw-r----- 1 oracle oinstall 2904 Oct 24 23:24 init.ora
-rw-r----- 1 oracle oinstall 675 Oct 26 14:46 CreateDBFiles.sql
2) Review the options and their related scripts.
3) Review the parent sql script RACTEST1.sql. Comments out the unwanted options.
$./RACTEST1.sh
You should Add this entry in the /etc/oratab: RACTEST:/u01/app/oracle/product/12.1.0/dbhome_1:Y
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 11:35:48 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter new password for SYS:
Enter new password for SYSTEM:
Enter new password for DBSNMP:
Enter password for SYS:
OPW-00019: Failed to update the CRS resource with DB password file location
Connected to an idle instance.
SQL> spool /u01/app/oracle/admin/RACTEST/scripts/CreateDB.log append
SQL> startup nomount pfile="/u01/app/oracle/admin/RACTEST/scripts/init.ora";
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 7663544 bytes
Variable Size 3053453384 bytes
Database Buffers 1.4093E+10 bytes
Redo Buffers 25890816 bytes
SQL> CREATE DATABASE "RACTEST"
2 MAXINSTANCES 32
3 MAXLOGHISTORY 1
4 MAXLOGFILES 192
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 1024
7 DATAFILE SIZE 700M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE SIZE 550M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
10 SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
11 SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
12 CHARACTER SET AL32UTF8
13 NATIONAL CHARACTER SET AL16UTF16
14 LOGFILE GROUP 1 SIZE 50M,
15 GROUP 2 SIZE 50M
16 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
17 enable pluggable database;
Database created.
......
.....
....
...
..
.
SQL> alter user CTXSYS account unlock identified by "CTXSYS";
User altered.
SQL> connect "CTXSYS"/"CTXSYS"
Connected.
SQL> host perl /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/RACTEST/scripts -b dr0defin -u CTXSYS/CTXSYS -a 1 /u01/app/oracle/product/12.1.0/dbhome_2/ctx/admin/defaults/dr0defin.sql 1\"AMERICAN\";
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/RACTEST/scripts/dr0defin_catcon_24303.lst
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin_*.lst files for spool files, if any
catconInit: database is not open on the default instanceUnexpected error encountered in catconInit; exiting
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter user CTXSYS password expire account lock;
User altered.
.....
...
..
.
Received two errors, and they are resolved as below:
a) Error “OPW-00019” Creating Password File In ASM For 12c Database (Doc ID 2021520.1).
b) Error when Creating Seed Database – CatconInit: Database Is Not Open On The Default Instance (Doc ID 2149323.1)
SQL> alter user CTXSYS account unlock identified by "CTXSYS";
User altered.
SQL> connect "CTXSYS"/"CTXSYS"
Connected.
SQL> host perl /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/RACTEST/scripts -b dr0defin -u CTXSYS/CTXSYS -a 1 /u01/app/oracle/product/12.1.0/dbhome_2/ctx/admin/defaults/dr0defin.sql 1\"AMERICAN\";
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/RACTEST/scripts/dr0defin_catcon_24303.lst
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin_*.lst files for spool files, if any
catconInit: database is not open on the default instanceUnexpected error encountered in catconInit; exiting
Rerun the script RACTEST1.sh, and recreated the CDB database successfully.
Added TEXT option by using DBCA as below:
D. Check the selected options installed
SQL> select comp_name,status,version from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- ------ -----------
Oracle Text VALID 12.1.0.2.0
Spatial VALID 12.1.0.2.0
Oracle Multimedia VALID 12.1.0.2.0
Oracle Workspace Manager VALID 12.1.0.2.0
Oracle XML Database VALID 12.1.0.2.0
Oracle Database Catalog Views VALID 12.1.0.2.0
Oracle Database Packages and Types VALID 12.1.0.2.0
JServer JAVA Virtual Machine VALID 12.1.0.2.0
Oracle XDK VALID 12.1.0.2.0
Oracle Database Java Packages VALID 12.1.0.2.0
Oracle Real Application Clusters VALID 12.1.0.2.0
11 rows selected.
SQL> select CON_ID,NAME from v$pdbs;
CON_ID NAME
---------- ------------------------------
2 PDB$SEED
3 RACTESTPDB
Note: The following options can also be disabled:
– Oracle Workspace Manager: then comment the execution of owminst.plb in CreateDBCatalog.sql
– Cluster Views (required for the Support of Oracle RAC): then comment the execution of CreateClustDBViews.sql in the file <sid>.sql
E. Manually Run “datapatch” utility
SQL> alter system set cluster_database=false scope=spfile;
SQL> shutdown immediate;
SQL> startup upgrade
SQL> alter pluggable database all open upgrade;
$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Fri Mar 31 10:43:35 2017
Copyright (c) 2015, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_43963_2017_03_31_10_43_35/sqlpatch_invocation.log
Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Patch 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):
Installed in the binary registry only
Bundle series PSU:
ID 160419 in the binary registry and not installed in any PDB
Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED RACTESTPDB
Nothing to roll back
The following patches will be applied:
22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016))
22291127 (Database Patch Set Update : 12.1.0.2.160419 (22291127))
Installing patches...
Patch installation complete. Total patches installed: 6
Validating logfiles...
Patch 22139226 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_apply_RACTEST_CDBROOT_2017Mar31_10_45_12.log (no errors)
Patch 22291127 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_RACTEST_CDBROOT_2017Mar31_10_45_28.log (no errors)
Patch 22139226 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_apply_RACTEST_PDBSEED_2017Mar31_10_45_47.log (no errors)
Patch 22291127 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_RACTEST_PDBSEED_2017Mar31_10_45_56.log (no errors)
Patch 22139226 apply (pdb RACTESTPDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_apply_RACTEST_RACTESTPDB_2017Mar31_10_45_47.log (no errors)
Patch 22291127 apply (pdb RACTESTPDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_RACTEST_RACTESTPDB_2017Mar31_10_45_55.log (no errors)
SQL Patching tool complete on Fri Mar 31 10:46:13 2017
$/u01/app/oracle/product/12.1.0/dbhome_1/OPatch$
SQL> select * from dba_registry_sqlpatch;
REFERENCES
Creating A Container Database (CDB) With A Subset Of Options (Doc ID 2001512.1) Customization of Database Options in a Multitenant Setup ( Doc ID: 1616554.1)
Make DBA Life Easy 