Monthly Archives: December 2017

Unknown's avatar Frank 11:27 pm on December 30, 2017  

duplicate cluster database using RMAN backup

Using RMAN backups, we can duplicate a database to different environments for testing purposes, or validate the backup itself.
Here is the procedure and command examples,

The high level procedure

0) using dbca create an empty database (only needed for first time)
1) generate asm file list (see instructions below)
2) alter system set cluster_database=FALSE scope=spfile;
3) srvctl stop database -d TEST2 -o immediate
4) drop asm files (see instructions below)
5) srvctl start instance -d TEST2 -i TEST21 -o nomount
6) rman auxiliary / catalog rcat/rcat@rcat  msglog logfile cmdfile rman_script
7) alter system set cluster_database=TRUE scope=spfile;
8) srvctl stop database -d TEST2 -o immediate
9) srvctl start database -d TEST2

How to generate asm file list

SQL> spool file_list.txt
SQL> select file_name from cdb_data_files;
SQL> select file_name from cdb_temp_files;
SQL> select member from v$logfile;
SQL> alter session set container = pdb$SEED;
SQL> select file_name from dba_data_files;
SQL> select file_name from dba_temp_files;
SQL> spool off

How to drop asm file

SQL> alter diskgroup "DG1" drop file '+DG1/TEST1/615776D7ECB497D5E053425A030AC5D8/DATAFILE/users.9257.963829293';

Example ramn script

run
{
ALLOCATE auxiliary CHANNEL c1 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
ALLOCATE auxiliary CHANNEL c2 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
ALLOCATE auxiliary CHANNEL c3 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
ALLOCATE auxiliary CHANNEL c4 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
set until time="to_date('2017-12-28 09:00:00', 'YYYY-MM-DD HH24:MI:SS')";
duplicate database TEST1 to TEST2 ;
}
Unknown's avatar Frank 10:43 pm on December 22, 2017  

migrate pluggable database to oracle 12.2

To migrate pluggable database from 12.1 to 12.2, you can follow these steps:
1. create CDB in 12.2
2. run prerequisite check in 12.1
3. run script generated by prerequisite check
4. extend temp tablespace if needed
5. unplug pdb
6. create pdb in 12.2 using unplugged pdb
7. upgrade new pdb
8. post-upgrade

create CDB in oracle 12.2

pre-upgrade

$ /u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/bin/java -jar \
  /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar \
  -c "pdb1" TEXT TERMINAL

prerequisite result

....
....
==============
BEFORE UPGRADE
==============

  Run /preupgrade_fixups_TESTDB.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------
     SYSAUX                            4000 MB  DISABLED     1502 MB  None
     SYSTEM                            4000 MB  DISABLED     2782 MB  None
     TEMP                                20 MB  DISABLED      150 MB  Extend
....
....
Preupgrade generated files:
    /u01/app/oracle/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
    /u01/app/oracle/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql

unplug PDB from oracle 12.1

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml';

create PDB in oracle 12.2 using unplugged PDB

SQL> create pluggable database pdb1 using '/home/oracle/pdb1.xml';

upgrade newly created PDB

SQL> alter pluggable database pdb1 open upgrade;
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1" -l /home/oracle catupgrd.sql
$

post upgrade

SQL> alter pluggable database pdb1 close immediate instances=all;
SQL> alter pluggable database pdb1 open read write instances=all;
$ datapatch -verbose

optional : re-plug original pdb in 12.1

SQL> drop pluggable database pdb1 keep datafiles;
SQL> create pluggable database pdb1 using '/home/oracle/pdb1.xml' NOCOPY TEMPFILE REUSE;;

If the instructions does not work for you, please post your question in the comments.

Unknown's avatar Frank 4:54 pm on December 16, 2017  

sqlplus login without typing password

If you use sqlplus very often, you should put all your passwords into wallet so that you do not have to type password whenever you logon to a database.
Follow these simple commands, you will be able to create wallet, add credentials into wallet, and use it.

create wallet

mkstore -wrl c:\ora-wallet -create

create credential

mkstore -wrl c:\ora-wallet -createCredential db_name username password

show credential

mkstore -wrl c:\ora-wallet -listCredential

update sqlnet.ora

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = c:\ora-wallet)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

sqlplus login

sqlplus /@db_name