ANJANI APPS DBA: 12C DB

Showing posts with label 12C DB. Show all posts

Sunday, 5 March 2023

RMAN Transportable Database - Cross-Platform Database Migration Same endian

Dear All,

in this post i am shaing cross platform db migration using Rman transportable db .

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Purpose

+ We can use RMAN "Transportable Database" conversion for performing cross platform database migration between two operating system platforms belonging to the same endian format.

Use V$TRANSPORTABLE_PLATFORM view to check the endianess of source and target database platforms.

+ If you need to migrate database across endian platforms i.e. from a little-endian platform to big-endian platform or vice-versa, we cannot use RMAN "Transportable Database" conversion. In this case, you would be using transportable tablespaces along with RMAN datafile conversion.

+ Also, note that for certain platform migration, we need not use RMAN convert (either using "Transportable Database" or "Transportable Tablespaces") at all. For these platforms, you can directly perform RMAN duplication (active or backup-based) or RMAN restore/recover without any conversion:

Document 1079563.1 RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support

Details

For explaining RMAN "Transportable Database" methodology, we will migrate a 11.2.0.3 database running on Linux x86 64-bit (little-endian) platfrom to Solaris Operating System x86 32-bit (little-endian) platform

1. Execute DBMS_TDB.CHECK_EXTERNAL function to identify any external tables, directories, or BFILEs. RMAN cannot automate the transport of these files, so you must copy the files manually and re-create the database directories.

SQL> set serveroutput on;

SQL> declare x boolean; begin x := dbms_tdb.check_external; end;

  2  /

The following directories exist in the database:

SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR

PL/SQL procedure successfully completed.

As per output above, we have 2 directories defined in the database. Check the operating system path for these directories using below query. Ensure that the same path is available on the target system for these directories to be usable or rename the directory paths after conversion.

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from DBA_DIRECTORIES;

DIRECTORY_NAME            DIRECTORY_PATH

------------------------- ----------------------------------------

DATA_PUMP_DIR             /orasoft/admin/ora11gR2/dpdump/

ORACLE_OCM_CONFIG_DIR     /orasoft/rdbms/11.2.0.3/ccr/state

2. Start the source database in READ ONLY mode:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database open read only;

3. Execute DBMS_TDB.CHECK_DB function. This check ensures that no conditions would prevent the transport of the database, such as incorrect compatibility settings, in-doubt or active transactions, or incompatible endian formats between the source platform and destination platform. Any condition reported by CHECK_DB must be resolved before proceeding

SQL> set serveroutput on

SQL>

SQL> DECLARE db_ready BOOLEAN;

  2  BEGIN

  3  db_ready := DBMS_TDB.CHECK_DB('Solaris Operating System (x86)', DBMS_TDB.SKIP_NONE);

  4  END;

  5  /

PL/SQL procedure successfully completed.

4. Transportable Database using RMAN COVERT DATABASE command can be performed on the source system or target system.

It's default behavior is to perform datafile conversion on all datafiles in the database. However, only datafiles that contain undo data require conversion including all datafiles beloging to SYSTEM tablespace and all UNDO tablespaces.

To skip datafile conversion for datafiles that do not contain undo data, you must perform a target platform conversion as shown in this document.

Get a list of datafiles that require conversion and ones that don't

SQL> select FILE_NAME "Datafiles requiring Conversion" from DBA_DATA_FILES

  2  where TABLESPACE_NAME in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);

Datafiles requiring Conversion

--------------------------------------------------------------------------------

/oradata/ora11gR2/system01.dbf

/oradata/ora11gR2/undotbs01.dbf

SQL> select FILE_NAME "Files NOT requiring Conversion" from DBA_DATA_FILES

  2  where TABLESPACE_NAME NOT in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);

Files NOT requiring Conversion

--------------------------------------------------------------------------------

/oradata/ora11gR2/sysaux01.dbf

/oradata/ora11gR2/users01.dbf

5. Now we need to move all the above files (the ones which need conversion and ones which do not) to the target system. For datafiles which do not require conversion, they can directly be moved to their final destination on the target system. For datafiles which need conversion, need to be moved to a staging location on the target system where we will run CONVERT on them during which they will be placed in their final destination.

To minimize time for migration, we need to evaluate how datafile copying (to the target system) and datafile conversion can be merged into a single step. One method would be to NFS mount the source system directory (containing the datafiles) on the target system. Thus, when datafile conversion is run on target system, the datafiles will be placed in their final location eliminating the extra step of transferring the datafiles to the target system.

This method will work if you are performing migration for below cases:

a) Source database uses file-system, Target database uses file-system
b) Source database uses file-system, Target database uses ASM

If source database uses ASM, we need to use BACKUP AS COPY command in RMAN (this needs to be run when the database in in MOUNT mode or OPEN read only) to move the datafiles from ASM to a file-system location on the source and then NFS mount that location on the target system.

Alternatively if source database uses SAN storage, check with your system administrator if it is possible to mount the source san diskgroups directly on the target server in read only mode.

In this example, our source Linux x86 64-bit uses file-system whereas our target Solaris x86 32-bit will use ASM. We will NFS mount the source system directory which contains the datafiles i.e. '/oradata' to the target Solaris system

NFS mounting is outside the scope of this article but here is how it is done

On source, put below line in /etc/exports:

/oradata        oraSolaris10(ro)

Re-start NFS:

# /etc/init.d/nfs stop

# /etc/init.d/nfs start

On target, perform the NFS mount:

# mount -o vers=3  -F nfs 192.168.114.10:/oradata /oradata

6. We now need to move the initialization parameter file from source to target system. If source database is using spfile, create pfile from it and move it to the target:

SQL> create pfile = '/oradata/ora11gR2/initSol11g.ora' from spfile;

On the target system, move this pfile to $ORACLE_HOME/dbs location and make suitable changes to reflect mount-points available on the target system and any other changes you might want to make. you might want to specifically change these parameters: audit_file_dest, control_files, db_name, db_recovery_file_dest, diagnostic_dest

STARTUP NOMOUNT the instance on the target system using the modified pfile:

SQL> startup nomount pfile='/orasoft/rdbms/11.2.0.3/dbs/initSol11g.ora'

7. Now, start the conversion process in RMAN on target system for the datafiles that we identified in Step 4 i.e. ones which contain UNDO data:

RMAN> CONVERT FROM PLATFORM 'Linux x86 64-bit'

2> PARALLELISM 2

3> DATAFILE '/oradata/ora11gR2/system01.dbf' FORMAT '+DATA'

4> DATAFILE '/oradata/ora11gR2/undotbs01.dbf' FORMAT '+DATA';

Starting conversion at target at 08-NOV-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=62 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=12 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input file name=/oradata/ora11gR2/system01.dbf

channel ORA_DISK_2: starting datafile conversion

input file name=/oradata/ora11gR2/undotbs01.dbf

converted datafile=+DATA/sol11g/datafile/undotbs1.256.766638629

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:02:53

converted datafile=+DATA/sol11g/datafile/system.257.766638629

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:23

Finished conversion at target at 08-NOV-11

You can increase PARALLELISM value if there are many files which need to be converted so that more channels are allocated. If the target database will not use ASM, specify the file-system path in the FORMAT clause where the datafile will finally reside.

8. Now, copy rest of the datafiles (which do not contain undo and hence do not require conversion) to the ASM diskgroup using RMAN without conversion.

Note that to copy files into ASM we have again used CONVERT command (without FROM PLATFORM clause). When CONVERT DATAFILE is used without specifying a source or destination platform, we do not perform any kind of conversion. This is specifically used to move files into ASM from file-sytem.

If the target database will not use ASM, this step is not needed. Instead use OS cp command to copy these datafile directly to the directory where you want them to reside.

RMAN> CONVERT PARALLELISM 2

2> DATAFILE '/oradata/ora11gR2/sysaux01.dbf' FORMAT '+DATA'

3> DATAFILE '/oradata/ora11gR2/users01.dbf' FORMAT '+DATA';

Starting conversion at target at 08-NOV-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=13 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input file name=/oradata/ora11gR2/sysaux01.dbf

channel ORA_DISK_2: starting datafile conversion

input file name=/oradata/ora11gR2/users01.dbf

converted datafile=+DATA/sol11g/datafile/users.259.766639567

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:17

converted datafile=+DATA/sol11g/datafile/sysaux.258.766639567

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:59

Finished conversion at target at 08-NOV-11

9. Now, we need to create the controlfile for our target database. You can generate the controlfile creation script by running below on the source database:

SQL> alter database backup controlfile to trace resetlogs;

Edit the controlfile creation script to reflect:

a) Datafile names for the target database where the datafiles finally reside (after running CONVERT DATAFILE)
b) Redo Log names as per target system path
c) database name as needed for the target database.

Here is the controlfile creation script for our example:

SQL> CREATE CONTROLFILE REUSE SET DATABASE "SOL11G" RESETLOGS ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '+DATA/sol11g/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '+DATA/sol11g/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 '+DATA/sol11g/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  DATAFILE

 12    '+DATA/sol11g/datafile/system.257.766638629',

 13    '+DATA/sol11g/datafile/sysaux.258.766639567',

 14    '+DATA/sol11g/datafile/undotbs1.256.766638629',

 15    '+DATA/sol11g/datafile/users.259.766639567'

 16  CHARACTER SET WE8MSWIN1252;

Control file created.

10. Now, open the database on the target system. Once, database opens, add tempfiles to the TEMP tablespace

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 20971520  AUTOEXTEND OFF;

Tablespace altered.

11. Complete the database conversion by running UTLIRP and UTLRP as below:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP UPGRADE;

SQL> @ ?/rdbms/admin/utlirp

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> @ ?/rdbms/admin/utlrp

Thanks,

Srini

Monday, 26 September 2016

Oracle Database 12.1.0.2c: Hot Cloning Of Pluggable Databases

Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 2 has introduced various enhancements to the cloning of Pluggable Databases to overcome the limitations imposed by the initial release of Oracle Database 12c (12.1.0.1). In Oracle 12.1.0.2c:

PDBs can be hot cloned, i.e. you don’t need to put the source PDB in read-only for cloning so that it can be cloned to a CDB online. In Oracle 12.1.0.1c, a pluggable database can be cloned only if it is read-only.
PDBs can be remote cloned, i.e. the source PDB may be located in a remote container. This feature was listed in Oracle Database 12c Release 1 (12.1.0.1), but didn’t work.
There is no need to create the directories where the database files of clone PDB will be placed. If the directory is not already present, it will be automatically created as part of the cloning operation.

In this article, I will demonstrate hot cloning of a PDB locally as well as remotely. For the purpose of this demonstration, I have two virtual machines, each with a container database.

Current scenario:

Host: host01
Container Database: cdb1 with pluggable database pdb1
Host: host02
Container Database: destcdb with pluggable database pdb1

Demonstration 1: Hot cloning of PDB locally

We will clone the pluggable database pdb1 to pdb1clone in the same CDB, i.e. cdb1 on host host01
Source host: host01
Source PDB: pdb1 in container database cdb1
Destination host: host01
Destination PDB: pdb1clone in container database cdb1
Currently, there is only one PDB called pdb1 currently open in READ WRITE mode in the container database cdb1.
CDB1>select name, cdb from v$database;

CDB1>select name, cdb from v$database;
NAME      CDB
--------- ---
CDB1      YES

CDB1>select con_id, name, open_mode from v$pdbs;
    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE

CDB1>select name from v$datafile where con_id = 3;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf

Verify that directory corresponding to data files of target PDB, i.e. pdb1clone is not present:

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/ 
total 1782620
-rw-r----- 1 oracle asmadmin  17973248 Jul 26 15:03 control01.ctl
drwxr-x--- 2 oracle oinstall      4096 Jun 29 12:06 pdb1
drwxr-x--- 2 oracle oinstall      4096 Jun 29 11:59 pdbseed
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:18 redo01.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:18 redo02.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 26 15:03 redo03.log
-rw-r----- 1 oracle asmadmin 650125312 Jul 26 15:02 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 817897472 Jul 26 15:02 system01.dbf
-rw-r----- 1 oracle asmadmin  62922752 Jul 26 15:02 temp01.dbf
-rw-r----- 1 oracle asmadmin 173023232 Jul 26 15:01 undotbs01.dbf
-rw-r----- 1 oracle asmadmin   5251072 Jul 25 15:18 users01.dbf

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/ | grep pdb1
drwxr-x--- 2 oracle oinstall      4096 Jun 29 12:06 pdb1

Using the CREATE PLUGGABLE DATABASE ... FROM command we will clone the existing PDB (pdb1) to create a new PDB (pdb1clone) in the same container database (cdb1). This statement copies the files associated with the source PDB to a new location and associates the files with the target PDB.

CDB1>create pluggable database pdb1clone from pdb1 
     file_name_convert = ('pdb1','pdb1clone');
Pluggable database created.

We can see that the new PDB called pdb1clone is in MOUNTED state when created and is opened successfully thereafter.
CDB1>sho pdbs

CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1CLONE                      MOUNTED

CDB1>alter pluggable database pdb1clone open;
Pluggable database altered.

CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1CLONE                      READ WRITE NO
CDB1>alter session set container=pdb1clone;
Session altered.

CDB1>sho con_name
CON_NAME
------------------------------
PDB1CLONE

CDB1>select count(*) from hr.employees;
  COUNT(*)
----------
       107

Note that the directory for the data files of the clone PDB pdb1clone has been created automatically in the location specified using FILE_NAME_CONVERT.
[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/pdb1clone

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/pdb1clone
total 2089832
-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:53 example01.dbf
-rw-r----- 1 oracle asmadmin   20979712 Jul 25 15:52 pdb1clone_temp012016-06-29_12-06-27-PM.dbf
-rw-r----- 1 oracle asmadmin    5251072 Jul 25 15:53 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle asmadmin  555753472 Jul 25 15:53 sysaux01.dbf
-rw-r----- 1 oracle asmadmin  272637952 Jul 25 15:53 system01.dbf

Hence, we have been able to hot clone a PDB locally without:

Placing the source PDB in READ ONLY mode
Creating the directory for the destination PDB

Demonstration 2: Hot cloning of PDB remotely

We will clone the pluggable database pdb1 in CDB cdb1 on host host01 to pdb1new in another CDB, i.e. destcdb on host host02:
Source host: host01
Source PDB: pdb1 in container database cdb1
Destination host: host02
Destination PDB: pdb1new in container database destcdb
Currently, there is only one PDB called pdb1 open in READ WRITE mode in destination container database destcdb:

DESTCDB>sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

On the target container database destcdb, we need to create the database link to connect to source container database cdb1 which will be used in the CREATE PLUGGABLE DATABASE.

DESTCDB>create database link cdb1_link 
        connect to system identified by oracle using 'host01:1521/cdb1';
Database link created.

Verify that the source pluggable database (pdb1@cdb1) that we want to clone is in READ WRITE mode.

CDB1> select con_id, name, open_mode from v$pdbs where name = 'PDB1';

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PDB1                           READ WRITE

Let’s execute the CREATE PLUGGABLE DATABASE statement using the database link (cdb1_link) as previously defined.

DESTCDB> create pluggable database pdb1new from pdb1@cdb1_link;
 create pluggable database pdb1new from pdb1@cdb1_link
                                                     *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified

Let’s find out location of datafiles for pdb1@cdb1 on host01:

CDB1>alter session set container = pdb1;
Session altered.
CDB1>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf

Verify that directory corresponding to data files of target PDB, i.e. pdb1new, is not present on the target host host02:

[oracle@host02 destcdb]$ ls -l /u01/app/oracle/oradata/destcdb
total 1761816
-rw-r----- 1 oracle asmadmin  17973248 Jul 25 15:35 control01.ctl
drwxr-x--- 2 oracle oinstall      4096 Jul 24 16:04 PDB1
drwxr-x--- 2 oracle oinstall      4096 Jul 24 15:57 pdbseed
-rw-r----- 1 oracle asmadmin  52429312 Jul 24 16:09 redo01.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:35 redo02.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 24 16:08 redo03.log
-rw-r----- 1 oracle asmadmin 629153792 Jul 25 15:34 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 817897472 Jul 25 15:35 system01.dbf
-rw-r----- 1 oracle asmadmin  62922752 Jul 25 15:26 temp01.dbf
-rw-r----- 1 oracle asmadmin 173023232 Jul 25 15:35 undotbs01.dbf
-rw-r----- 1 oracle asmadmin   5251072 Jul 25 15:19 users01.dbf

Let’s specify FILE_NAME_CONVERT and re-execute the CREATE PLUGGABLE DATABASE statement using the database link (cdb1_link) we previously defined:

DESTCDB>create pluggable database pdb1new from pdb1@cdb1_link 
        file_name_convert = ('/u01/app/oracle/oradata/cdb1/pdb1','/u01/app/oracle/oradata/destcdb/pdb1new');
Pluggable database created.

By default the new pluggable database is created in MOUNTED state and can be opened.

DESTCDB>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1NEW                        MOUNTED

DESTCDB>alter pluggable database pdb1new open;
Pluggable database altered.

DESTCDB>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1NEW                        READ WRITE NO
DESTCDB>alter session set container=pdb1new;
Session altered.
DESTCDB>select count(*) from hr.employees;
  COUNT(*)
----------
       107

Verify that the directory for data files of pbdnew has been created automatically on host02 in the location specified using FILE_NAME_CONVERT:

[oracle@host02 pdb1new]$ ls -l /u01/app/oracle/oradata/destcdb/pdb1new
total 2089832
-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:41 example01.dbf
-rw-r----- 1 oracle asmadmin   20979712 Jul 25 15:39 pdb1_temp012016-06-29_12-06-27-PM.dbf
-rw-r----- 1 oracle asmadmin    5251072 Jul 25 15:41 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle asmadmin  555753472 Jul 25 15:41 sysaux01.dbf
-rw-r----- 1 oracle asmadmin  272637952 Jul 25 15:41 system01.dbf

Hence, we have been able to hot clone a PDB remotely without:

Placing the source PDB in READ ONLY mode
Creating the directory for the destination PDB

Summary:

In Oracle 12.1.0.2c, various enhancements been made to cloning of PDB:

PDBs can be hot cloned, i.e. you don’t need to put the source PDB in read-only for cloning so that it can be cloned to a CDB online.
PDBs can be hot cloned remotely as well, i.e. the source PDB can be located in a remote container.
There is no need to create the directories where the database files of clone PDB will be placed. If the directory is not already present, it will be automatically created as part of the cloning operation.

Thanks
Srini

Tuesday, 20 September 2016

Troubleshooting the Discovery of Targets in OEM12c

Troubleshooting the Discovery of Targets in OEM12c

Please enable perl trace to debug mode and then after discovery please send perl log file to OracleSupport for further observation.

Go to /agent_inst/sysman/config
Open emd.properties file and
Change this line EMAGENT_PERL_TRACE_LEVEL=INFO to EMAGENT_PERL_TRACE_LEVEL=DEBUG
Restart agent
Run discovery again and send us /agent_inst/sysman/log/emagent_perl.trc file.

Thanks

Srini

APEX 5 upgrade/Install on 12c Oracle DB (12.1.0.2.0)

1. Apex 5 downloaded from otn.oracle.com

2. Patch #20618595 (For a 12.1.0.2.0 database)

Down the DB and listener.

Run the "CheckConflictAgainstOHWithDetail"

Once above check is Passed apply the patch:

export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin

Go to the PATCH Directory and opatch apply.

Now need to upgrade all PDBs with the new patch we applied. This is done with a utility called datapatch.

datapatch

Create 3 tablespace for APEX:

create tablespace APEXFILE datafile '/apps/apexora/app/apexora/oradata/APEXORA/APEXFILE.dbf'size 100Mautoextend on maxsize 2000Mextent management local uniform size 64K;
create tablespace APEXUSER datafile '/apps/apexora/app/apexora/oradata/APEXORA/APEXUSER.dbf'size 100Mautoextend on maxsize 2000Mextent management local uniform size 64K;

create tablespace APEXEBS datafile '/apps/apexora/app/apexora/oradata/APEXORA/APEXEBS.dbf'size 100Mautoextend on maxsize 2000Mextent management local uniform size 64K;

Now rename the apex 4.2 home to apex_old

mv apex apex_old

unzip apex_5.0_en.zip in any location and mv apex to $ORACLE_HOME.

cd $ORACLE_HOME/apex

start up sqlplus as sys

sqlplus / as sysdba

@apexins.sql APEXEBS APEXFILE TEMP /i/

Once upgrade finish check the APEX version:

SQL> select version from sys.dba_registry where comp_id = 'APEX';.

next i ran rest-config sql

SQL> @apex_rest_config_cdb.sql this is also completed with out any issue.

@reset_image_prefix_con.sql this is also completed with out any issue.

These two are key sql for APEX5 on 12c CDB installation.

SQL> select version from sys.dba_registry where comp_id = 'APEX';

VERSION
------------------------------
5.0.3.00.03

Thanks

Srini

Oracle12c DB CDB and PDB Auto start

Determining Whether a Database Is a CDB or Not:

SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID
--------- --- -----------------------

APEXORA YES 0

Creation of a PDB:

Creation of a PDB from Seed

You can use the CREATE PLUGGABLE DATABASE statement to create a PDB by copying the files from PDB$SEED, which is a template for creating PDBs. The following figure illustrates creation from the seed.

Creating a PDB Using the STORAGE, DEFAULT TABLESPACE, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE apexpdb ADMIN USER apexdb IDENTIFIED BY welcome123 STORAGE (MAXSIZE 5G MAX_SHARED_TEMP_SIZE 100M) DEFAULT TABLESPACE sales DATAFILE '/apps/apexora/app/apexora/oradata/APEXORA/apexpdb/apexpdb01.dbf' SIZE 250M AUTOEXTEND ON PATH_PREFIX = '/apps/apexora/app/apexora/oradata/APEXORA/apexpdb/' FILE_NAME_CONVERT = ('/apps/apexora/app/apexora/oradata/APEXORA/pdbseed', '/apps/apexora/app/apexora/oradata/APEXORA/apexpdb');

Pluggable database created.

How to start the Pluggable DB:

SQL> select instance_name, version, status, con_id from v$instance;

INSTANCE_NAME VERSION STATUS CON_ID

---------------- ----------------- ------------ ----------

APEXORA 12.1.0.2.0 OPEN 0

We set save state parameter for APEXPDB:

As you see APEXPDB started as well. Other PDB’s are still mount mode. Because we did not set save state parameter for these PDB’s.

Let me start other PDB as well.

SQL> alter pluggable database PDBORCL OPEN;

Pluggable database altered.

I will put all PDBS in Save state and Bounce CDB .PDB will auto start after the save stateset.

Thanks
Srini

Reverting to Release 4.2 in a CDB

To revert to a previous Oracle Application Express release 4.2 in a CDB:

Make sure All PDB's Open at this stage.

alter pluggble database all open;

If you altered your images directory, you must point the text alias /i/ back to images directory for the release you want to revert to. See "Copying the Images Directory."
Change your working directory to apex/core in the 4.2 source.
Cd $OH_HOME/apex/core create below sqls.This your apex5 directory under OH_HOME

Create a new text file in that directory named apx42dgrd1.sql consisting of the following:

alter session set current_schema = SYS;
 
@core_sys_views.sql
 
grant select on sys.wwv_flow_gv$session to APEX_040200;
 
@wwv_flow_val.sql
@wwv_flow_val.plb
@wwv_dbms_sql.sql
@wwv_dbms_sql.plb
 
begin
    dbms_utility.compile_schema('APEX_040200');
end;
/

Create a second new text file in that directory named apx42dgrd.sql consisting of the following:

set define '^'
 
whenever sqlerror exit
 
column :xe_home new_value OH_HOME NOPRINT
variable xe_home varchar2(255)
 
set serverout on
begin
-- get oracle_home
    sys.dbms_system.get_env('ORACLE_HOME',:xe_home);
    if length(:xe_home) = 0 then
        sys.dbms_output.put_line(lpad('-',80,'-'));
        raise_application_error (
            -20001,
            'Oracle Home environment variable not set' );
    end if;
end;
/
whenever sqlerror continue
 
set termout off
select :xe_home from sys.dual;
set termout on
 
host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx42dgrd apx42dgrd1.sql

Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
```
On UNIX and Linux:
```
```
$ sqlplus /nolog
SQL> SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
```
Execute the following commands:
```
@apx42dgrd.sql
```

Once this is completed successful goto the next step.

Change your working directory to apex in the 4.2 source.
cd $OH_HOME
mv apex apex_5_old
mv apex_old apex this your old apex4.2 directory and create below sqls under apex4.2 .

Create a new text file in that directory name apx42dgrd1.sql with the following contents:

set define '^'
 
ALTER SESSION SET CURRENT_SCHEMA = SYS;
 
@apexvalidate x x APEX_040200
 
ALTER SESSION SET CURRENT_SCHEMA = APEX_040200;
exec apex_040200.wwv_flow_upgrade.switch_schemas('APEX_050000','APEX_040200');
ALTER SESSION SET CURRENT_SCHEMA = SYS;
declare
    l_apex_version varchar2(30);
begin
    l_apex_version := apex_040200.wwv_flows_release;
    dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_040200');
    dbms_registry.downgraded('APEX',l_apex_version);
    validate_apex;
end;
/

Create a second new text file in that directory named apx42dgrd.sql consisting of the following:

set define '^'
 
whenever sqlerror exit
 
column :xe_home new_value OH_HOME NOPRINT
variable xe_home varchar2(255)
 
set serverout on
begin
-- get oracle_home
    sys.dbms_system.get_env('ORACLE_HOME',:xe_home);
    if length(:xe_home) = 0 then
        sys.dbms_output.put_line(lpad('-',80,'-'));
        raise_application_error (
            -20001,
            'Oracle Home environment variable not set' );
    end if;
end;
/
whenever sqlerror continue
 
set termout off
select :xe_home from sys.dual;
set termout on
 
host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx42dgrd apx42dgrd1.sql

Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
```
On UNIX and Linux:
```
```
$ sqlplus /nolog
SQL> SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
```
Execute the following:
```
@apx42dgrd.sql
```

SQL> @apx42dgrd.sql

PL/SQL procedure successfully completed.

catcon: ALL catcon-related output will be written to apx42dgrd_catcon_17261.lst
catcon: See apx42dgrd*.log files for output generated by scripts
catcon: See apx42dgrd_*.lst files for spool files, if any
catcon.pl: completed successfully

SQL> commit;

Commit complete.

SQL> select version from sys.dba_registry where comp_id = 'APEX';

VERSION
------------------------------
4.2.5.00.08

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Now you are done with reverting.

Thanks

Srini

Ora-28014 : cannot drop administrative users on Oracle 12C

ORA-28014: cannot drop administrative users on Oracle 12C

SQL> drop user APEX_050000 ;
drop user APEX_050000
*
ERROR at line 1:
ORA-28014: cannot drop administrative users

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> drop user APEX_050000 cascade;

User dropped.

How to create Table Spaces in PDB$SEED

How to create Table Spaces in PDB$SEED

SQL> alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database pdb$seed OPEN READ WRITE;
alter pluggable database pdb$seed OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database PDB$SEED already open

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter pluggable database pdb$seed OPEN READ WRITE;

Pluggable database altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB$SEED
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> create tablespace APEXFILE datafile '/apps/apexora/app/apexora/oradata/APEXORA/pdbseed/APEXFILE.dbf'
2 size 100M
3 autoextend on maxsize 2000M
4 extent management local uniform size 64K;

Tablespace created.

SQL> create tablespace APEXUSER datafile '/apps/apexora/app/apexora/oradata/APEXORA/pdbseed/APEXUSER.dbf'
2 size 100M
3 autoextend on maxsize 2000M
4 extent management local uniform size 64K;

Tablespace created.

SQL> reate tablespace APEXEBS datafile '/apps/apexora/app/apexora/oradata/APEXORA/pdbseed/APEXEBS.dbf'
SP2-0734: unknown command beginning "reate tabl..." - rest of line ignored.
SQL> create tablespace APEXEBS datafile '/apps/apexora/app/apexora/oradata/APEXORA/pdbseed/APEXEBS.dbf'
2 size 100M
3 autoextend on maxsize 2000M
4 extent management local uniform size 64K;

Tablespace created.

SQL> alter system set db_securefile=NEVER scope=both;

System altered.

SQL> commit;

Commit complete.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ ONLY;

Pluggable database altered.

SQL> how con_name;
SP2-0734: unknown command beginning "how con_na..." - rest of line ignored.
SQL> show con_name;

CON_NAME
------------------------------
PDB$SEED
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter session set "_oracle_script"=FALSE;

Session altered.

SQL> commit;

Commit complete.

Thanks
Srini

SYSDBA grant to any User in Oracle 12c

Common Users & SYSDBA with #Oracle 12c:

SQL> select name,open_mode,con_id from v$pdbs;

NAME OPEN_MODE CON_ID

------------------------------ ---------- ----------

PDB$SEED READ ONLY 2

PDBORCL READ WRITE 3

APEXPDB READ WRITE 4

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS TRUE TRUE FALSE FALSE FALSE FALSE 0

SQL> create user c##_sys identified by oracle container=all;

User created.

SQL> grant sysdba to c##_sys container=all;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS TRUE TRUE FALSE FALSE FALSE FALSE 0

C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0

C##_SYS can now do anything to any PDB

Thanks

Srini

Database Name change using NID ...

DBNAME change steps after RMAN recovery on R12.1.3 instance with 11g DB

DBNAME change using nid utility:

Once RMAN recovery is completed and DB has been open with PROD db name.now here we want to change to TARGET instance name.As I faced small confusion while using this utility.I would like to share those steps.

Here My TARGET DB is Up and Running as PROD name after using RMAN clone steps.

go to $ORACLE_HOME/dbs run " relink all".It will resolve any libray issues while executing "nid".

oracle/xxxxx/orabin/11.1.0/dbs]$ nid target=sys dbname=XXXRATE

DBNEWID: Release 11.1.0.7.0 - Production on Wed May 11 21:09:15 2016

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Password:
Connected to database xxxxx (DBID=2579751613)

Connected to server version 11.1.0

Control Files in database:
/oracle/xxxxx/oradata/cntrl01.dbf
/oracle/xxxxx/oradata/cntrl02.dbf
/oracle/xxxxx/oradata/cntrl03.dbf

Change database ID and database name xxxxx to XXXRATE? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2579751613 to 140640575
Changing database name from xxxxx to NJVCRATE
Control File /oracle/xxxxx/oradata/cntrl01.dbf - modified
Control File /oracle/xxxxx/oradata/cntrl02.dbf - modified
Control File /oracle/xxxxx/oradata/cntrl03.dbf - modified
Datafile /oracle/xxxxx/oradata/system01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system05.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/ctxd01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/owad01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_queue02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/odm.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/olap.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/sysaux01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/apps_ts_tools01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system12.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind06.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_ref03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_int02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/sysaux02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/sysaux03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media10.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data05.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/sysaux04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind07.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_ref04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data06.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media11.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/undo11.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc12.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc13.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc14.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc15.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media12.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data07.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data08.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data09.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data10.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data11.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system13.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system14.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media05.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media06.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media07.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media08.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media09.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/ctxd02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/odm1.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_summ02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_int03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_nolog02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind08.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind09.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind10.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_archive02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc05.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc06.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc07.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc08.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc09.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc10.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc11.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/njvcusers01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media13.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc16.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data12.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media14.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data13.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind11.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_queue03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/undo12.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_queue04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media15.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/xxnjvc17.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data14.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media16.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_ref05.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media17.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data15.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind12.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_ref06.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media18.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind13.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data16.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_ref07.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/apexebs01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/apexfile01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/apexuser01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind14.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data17.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system10.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system06.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/portal01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system07.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system09.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system08.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/system11.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_ref01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_int01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_summ01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_nolog01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_archive01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_queue01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_media01.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_data03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind03.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind04.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_txn_ind05.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/a_ref02.db - dbid changed, wrote new name
Datafile /oracle/xxxxx/oradata/temp12.db - dbid changed, wrote new name
Control File /oracle/xxxxx/oradata/cntrl01.dbf - dbid changed, wrote new name
Control File /oracle/xxxxx/oradata/cntrl02.dbf - dbid changed, wrote new name
Control File /oracle/xxxxx/oradata/cntrl03.dbf - dbid changed, wrote new name
Instance shut down

Database name changed to XXXRATE.
Modify parameter file and generate a new password file before restarting.
Database ID for database XXXRATE changed to 140640575.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

2)Open pfile and change the dbname parameter from prod to target name and start the database.now the db has been started with target dbname but still datafile locations are not changed .

3)Take the control file backup on open Db which is target instance.

SQL> alter database backup control file to trace as '/tmp/control.sql';

Database altered.

Shutdown the Instance and take the the control files backup ,as we are going to recreate the control file again for change the data file locations.

4) Control File creation:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2166536 bytes

Variable Size 427819256 bytes

Database Buffers 624951296 bytes

Redo Buffers 14000128 bytes

SQL> @/tmp/control.sql

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

Now start the lsnrctl on target instance.Now your DB Instance is ready.

Clean the FND_NODEs and run auto config on DB node with production apps password.Then after Your DB is ready .

Thanks

Srini

ANJANI APPS DBA

Oracle Apps DBA

Oracle Apps DBA

Sunday, 5 March 2023

RMAN Transportable Database - Cross-Platform Database Migration Same endian

Applies to:

Purpose

Details

Monday, 26 September 2016

Oracle Database 12.1.0.2c: Hot Cloning Of Pluggable Databases

Current scenario:

Demonstration 1: Hot cloning of PDB locally

Demonstration 2: Hot cloning of PDB remotely

Summary:

Tuesday, 20 September 2016

Troubleshooting the Discovery of Targets in OEM12c

APEX 5 upgrade/Install on 12c Oracle DB (12.1.0.2.0)

Oracle12c DB CDB and PDB Auto start

Creation of a PDB:

Creation of a PDB from Seed

Reverting to Release 4.2 in a CDB

Ora-28014 : cannot drop administrative users on Oracle 12C

ORA-28014: cannot drop administrative users on Oracle 12C

How to create Table Spaces in PDB$SEED

SYSDBA grant to any User in Oracle 12c

Common Users & SYSDBA with #Oracle 12c:

Database Name change using NID ...

DBNAME change steps after RMAN recovery on R12.1.3 instance with 11g DB

Popular Posts