http://dbmentors.blogspot.com/2013/05/restore-rman-rac-database-backup-to.html
-MGMTDB:/oraclebase/app/oracle/product/18.0.0/grid:N
test:/oraclebase/app/oracle/product/18.0.0/db11g_home:N
11G HOME --/oraclebase/app/oracle/product/18.0.0/db11g_home
export ORACLE_SID=test1
alter system set cluster_database = False scope = SPFILE sid='*';
create pfile from spfile;
1. Stop the db service
srvctl stop database -d test
2. start the database in mount exclusive mode:
startup mount exclusive restrict
select instance_name,status,logins from v$Instance;
\
[oracle@ompl ~]$ srvctl status database -d test
Instance test1 is running on node ompl
Instance test2 is not running on node ompl002
create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';
drop database
SQL> startup nomount pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';
ORACLE instance started.
Total System Global Area 7.9369E+10 bytes
Fixed Size 2236848 bytes
Variable Size 7516196432 bytes
Database Buffers 7.1672E+10 bytes
Redo Buffers 178704384 bytes
SQL> create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';
File created.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
SQL> select dbid from v$database;
DBID
----------
3447057297
______________________________________________________
node 2
[oracle@ompl002 dbs]$ cat inittest2.ora_bk
SPFILE='+DATA_DG01/test/spfiletest.ora'
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS02' does not exist or of wrong type
Process ID: 112727
Session ID: 667 Serial number: 3
startup with pfile
starup mount pfile='/orace/initdwpx.ora'
alter database open;
alter tablespace undotbs2 rename to undotdbs02;
exit
srvctl stop database -d test
check the spfile location on both the side
crsctl stat res -t
[oracle@ompl002 admin]$ srvctl status database -d test
Instance test1 is running on node ompl001
Instance test2 is running on node ompl002
srvctl stop database -d test
--------------------------------------------------------------------------------------
stop the cluster on second node
[oracle@ompl002 dbs]$ sqlplus sys/***@pdrl-scan
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 9 15:07:54 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prddl-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
oragrid$ srvctl stop LISTENER -n OMPRD001
oragrid$srvctl stop LISTENER -n oracledb1
oragrid$srvctl stop LISTENER -n oracledb2
srvctl stop scan_listener
srvctl start scan_listener
-____________________________________________________________________________________
set dbid=344705297
RMAN_test_CTL_2019_07_02_01_43.bkp
run
{
allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testadm02,CvInstanceName=Instance001)";
restore controlfile from 'c-3447057297-20190712-00';
}
output file name=+DATA_DG01/test/controlfile/current.267.1013439113
output file name=+DATA_DG02/test/controlfile/current.256.1013439113
sql> alter database mount;
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
output file name=+DATA_DG01/test/controlfile/current.260.1013403753
output file name=+DATA_DG02/test/controlfile/current.256.1013403755
+DATA_DG01/test/controlfile/current.287.1013473803
+DATA_DG02/test/controlfile/current.279.1013473805
run {
allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_02_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_03_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_04_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_05_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_06_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_07_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_08_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_09_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_10_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
#set until time "TO_DATE('2019-07-07 11:44','yyyy-mm-dd hh24:mi:ss')";
set newname for datafile 1 to '+DATA_DG03';
set newname for datafile 2 to '+DATA_DG03';
set newname for datafile 3 to '+DATA_DG03';
set newname for datafile 4 to '+DATA_DG03';
set newname for datafile 5 to '+DATA_DG03';
set newname for datafile 6 to '+DATA_DG03';
set newname for datafile 7 to '+DATA_DG01';
set newname for datafile 9 to '+DATA_DG01';
set newname for datafile 10 to '+DATA_DG02';
set newname for datafile 11 to '+DATA_DG01';
set newname for datafile 14 to '+DATA_DG03';
set newname for datafile 16 to '+DATA_DG02';
set newname for datafile 18 to '+DATA_DG02';
set newname for datafile 19 to '+DATA_DG02';
set newname for datafile 20 to '+DATA_DG02';
set newname for datafile 21 to '+DATA_DG02';
set newname for datafile 22 to '+DATA_DG02';
set newname for datafile 23 to '+DATA_DG03';
set newname for datafile 24 to '+DATA_DG03';
set newname for datafile 25 to '+DATA_DG03';
set newname for datafile 26 to '+DATA_DG03';
set newname for datafile 29 to '+DATA_DG03';
set newname for datafile 168 to '+DATA_DG03';
set newname for datafile 200 to '+DATA_DG02';
# restore the database and switch the datafile names
restore database;
switch datafile all;
recover database;
release channel tape_01_test;
release channel tape_02_test;
release channel tape_03_test;
release channel tape_04_test;
}
Restore archive
__________________________________________________________________________________________________
rman target / catalog=rmanadmin1/****@RMADB
connect catalog rmanadm/*****@RMADB
run
{
allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
allocate channel tape_02_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,
ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";
set archivelog destination to '+RECO_DG01/archive';
restore archivelog from logseq=136422 until logseq=136640 thread=1;
release channel tape_01_test;
release channel tape_02_test;
}
restore archivelog from logseq=121835 until logseq=122150 thread=2;
recover database using backup controlfile until cancel;
AUTO
select 'alter database rename file '''||member||''' to '''||replace(member,'&sourcedb','&targetdb')||''';' from v$logfile;
alter database open RESETLOGS;
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file:
'+DATA_XDW/test/changetracking/ctf.281.816780643'
ORA-17502: ksfdcre:1 Failed to create file
+DATA_XDW/test/changetracking/ctf.281.816780643
ORA-17501: logical block size 4294967295 is invalid
ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted
ORA-17503: ksfdopn:2 Failed to open file
+DATA_XDW/test/changetracking/ctf.281.816780643
ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted
ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted
SQL> alter database disable block change tracking;
Database altered.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
recover database until available redo;
select name from v$tempfile;
select member from v$logfile;
select name, open_mode, dbid from v$database;
NAME OPEN_MODE DBID
--------- -------------------- ----------
test READ WRITE 34470572097
SELECT NAME FROM V$TEMPFILE;
SHUT immediate;
startup mount;
nid target=/ or nid target=sys/ChangeMe2019
starup mount;
alter database open resetlogs;
alter system set cluster_database = True scope = SPFILE sid='*';
make changes in pfile;
Add the cluster parameter
create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';
create pfile='/home/dba/initdwpxw.ora' from spfile;
check the pfile parameter
test2.thread=2
test1.thread=1
test2.instance_number=2
test1.instance_number=1
test2.undo_tablespace='UNDOTBS2'
test1.undo_tablespace='UNDOTBS1'
*.remote_listener='omprl-scan:1521'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.cluster_database=true
startup nomount pfile='/home/dba/initdwpxw.ora';
create spfile='+DATA_DG01' from pfile='/home/dba/initdwpxw.ora';
SHUT IMMEDIATE
ASMCMD> cd +DATA_DG01/test/parameterfile
ASMCMD> ls -l
check the spfile
on node 1
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ cat initdwpxw1.ora
SPFILE='+DATA_DG01/test/parameterfile/spfile.267.924478703'
[oracle@rac1 dbs]$
rm spfile*
On node 2
[oracle@rac2 dbs]$ cat initdwpxw1.ora
SPFILE='+DATA_DG01/test/parameterfile/spfile.267.924478703'
[oracle@rac2 dbs]$
rm spfile*
node 1
. oraenv
test1
startup nomount;
show parameter pfile
alter database mount;
alter database open;
select name, open_mode from gv$database;
follow the above step on node 2 and start the db.
startup mount;
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA_DG01/test/spfiletest
.ora
SQL> show parameter cluster;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
select name,open_mode from gv$database;
select MEMBER from gv$logfile;
ADD DATABASE TO CLUSTER
crsctl check cluster -all
crsctl stat res -t
[oracle@rac1 ~]$ which srvctl
/oraclebase/app/oracle/product/18.0.0/dbhome_1/bin/srvctl
[oracle@rac1 ~]$ srvctl add database -d test -o /oraclebase/app/oracle/product/18.0.0/dbhome_1
srvctl config database -d test -a
srvctl add instance -d test -i test1 -n ompl
srvctl add instance -d test -i test2 -n ompl002
srvctl config database -d test -a
database is already started in sqlplus
srvctl start database -d test
crsctl stat res ora.test.db -t
// some time disk may not appear just restart the db in cluster.
srvctl stop database -d test
srvctl start database -d test
add service
[oracle@rac1 ~]$ srvctl add service -d test -s test_SRV -r test1 -a test2
[oracle@rac1 ~]$ srvctl start service -d test -s test_SRV
[oracle@rac1 ~]$ srvctl status service -d test -s test_SRV
______________________________________________________________________________________________
change the archive to noarchive before compressing. else delete the archives.
[oracle@OMPRLE001 advance_compression]$ more check_what_to_execute
nohup ksh run_advance_compression.ksh > run_advance_compression.ksh.out &
[oracle@OMPRLD001 advance_compression]$ more run_advance_compression.ksh
nohup sqlplus /nolog @Advanced_compress_01.sql > Advanced_compress_01.sql.out &
nohup sqlplus /nolog @Advanced_compress_02a.sql > Advanced_compress_02a.sql.out &
nohup sqlplus /nolog @Advanced_compress_02b.sql > Advanced_compress_02b.sql.out &
nohup sqlplus /nolog @Advanced_compress_03a.sql > Advanced_compress_03a.sql.out &
nohup sqlplus /nolog @Advanced_compress_03b.sql > Advanced_compress_03b.sql.out &
nohup sqlplus /nolog @Advanced_compress_04.sql > Advanced_compress_04.sql.out &
nohup sqlplus /nolog @Advanced_compress_05.sql > Advanced_compress_05.sql.out &
nohup sqlplus /nolog @Advanced_compress_06.sql > Advanced_compress_06.sql.out &
nohup sqlplus /nolog @Advanced_compress_07.sql > Advanced_compress_07.sql.out &
nohup sqlplus /nolog @Advanced_compress_08.sql > Advanced_compress_08.sql.out &
__________________________________________________________________________________
++++++++++++++++++++++++++++++++++++++++++++++++++
uncompress
connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select name from v$database
alter table AZIwU.SALES_F move nocompress;
alter table AZIwU.ZIU_DAILY_PLANNED_ORDER_FACTS move nocompress;
alter table AZIwU.Z_INTPART_TAB nocompress;
alter table AZIwU.Z_TEST nocompress;
quit;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
alter table test.PO_RECEIVING_FACTS move PARTITION SYS_P237220 ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4;
_+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
:1,$ s/compress/ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4/g
index
select 'alter index '||index_owner||'.'||index_name ||' REBUILD PARTITION ' || PARTITION_NAME || ' online nologging parallel 4 ;' from DBA_IND_PARTITIONS where STATUS='UNUSABLE';
move_nocompress
select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' PARTITION '||PARTITION_NAME||' uncompress; ' from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';
select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' uncompress; ' from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';
select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; ' from all_tab_partitions WHERE table_name in ('SALES_F','ZIU_DAILY_PLANNED_ORDER_FACTS','Z_INTPART_TAB','Z_INTPART_TAB','Z_TEST');
select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4; ' from all_tab_partitions WHERE table_name in ('SALES_FACTS_NEW');
select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move SUBPARTITION '||SUBPARTITION_NAME||' compress; ' from all_tab_subpartitions WHERE table_name in ('KVI_SU','KVI_S','OMSG.MED','OMSG.KV');
select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COM
PRESS ADVANCED NOLOGGING PARALLEL 4; ' from all_tab_partitions WHERE table_name in ('W_AR_BALANCE_CUSTOMER_F','W_KRONOS_HOURS_F','W_OMNI_SALES_ORDER_LINE_F');
select * from OMSTG.KVI_SUMMARY_WBREAKS_STG where rownum<=1;
select * from OMSTG.KVI_SUMMARY_WBREAKS_FINAL_STG where rownum<=1;
select * from OMSTG.MED_KVI_SUMMARY where rownum<=1;
alter table OMSTG.MED_KVI_SUMMARY move PARTITION SYS_P254450 compress;
select * from OMSTG.KVI_SUMMARY_WBREAKS_STG where rownum<=1;
alter table DBMON.LOGIN_TRAIL move compress;
alter table DBMON.SALES_FACTS_TEST move compress;
alter table OBDW.W_GLLANCE_FS_BKP10062015 move compress;
alter table OBDW.W_GLALANCE_F_BKP10062015 move compress;
alter table test.AR_BANCE_FACTS_20180517 move compress;
if any table in advance compression.
header
SET HEADING ON SET PAGESIZE 50001. SET LINESIZE 80. SET TERMOUT OFF SET VERIFY OFF SET FEEDBACK OFF SET TRIMS OFF
connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select name from v$database;
set heading off
set pages 400 lines 400
connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select name from v$database
++++++++++++++++++++++++++++++++++++++++++++++++++++
crsctl check cluster -all
Upgrade:
________
http://oracle-help.com/oracle-18c/upgrading-oracle-grid-infrastructure-11gr2-to-18c/
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/db_1
[oracle@localhost ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.
upgrade
Empty Recycle bin.
SQL> PURGE DBA_RECYCLEBIN ;
DBA Recyclebin purged.
Run Gather statistics to finish upgrade soon.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed
SQL> @emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
[oracle@test admin]$ cd $ORACLE_HOME/olap/admin/
SQL> @catnoamd.sql
/oraclebase/app/oracle/product/18.0.0/db11g_home/jdk/bin/java -jar /oraclebase/app/ora18c/product/18.0.0/software/rdbms/admin/preupgrade.jar FILE DIR /oraclebase/app/preupgrade
==================
PREUPGRADE SUMMARY
==================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2019-02-08T19:42:18
After run the above tool we will get below sql files.
[oracle@test ~]$ cd /u01/preupgrade/
[oracle@test preupgrade]$ ls -lrt
total 648
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:38
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 oracle
-rw-r--r-- 1 oracle oinstall 7963 Feb 8 19:42 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 14846 Feb 8 19:42 dbms_registry_extended.sql
-rw-r--r-- 1 oracle oinstall 422048 Feb 8 19:42 preupgrade_package.sql
-rw-r--r-- 1 oracle oinstall 83854 Feb 8 19:42 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 14383 Feb 8 19:42 parameters.properties
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 upgrade
-rw-r--r-- 1 oracle oinstall 50172 Feb 8 19:42 components.properties
-rw-r--r-- 1 oracle oinstall 1 Feb 8 19:42 checksBuffer.tmp
-rw-r--r-- 1 oracle oinstall 14521 Feb 8 19:42 preupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 9914 Feb 8 19:42 postupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 11816 Feb 8 19:42 preupgrade.log
Run the preupgrade_fixup.sql
SQL> @/oraclebase/app/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-07-15 13:08:59
For Source Database: test
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 18.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. invalid_objects_exist NO Manual fixup recommended.
2. network_acl_priv NO Manual fixup recommended.
3. exclusive_mode_auth NO Manual fixup recommended.
4. mv_refresh NO Informational only.
Further action is optional.
5. pre_fixed_objects YES None.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
__________________________________________________________________
[oracle@test preupgrade]$ export ORACLE_SID=test1
[oracle@test preupgrade]$ export ORACLE_HOME=/oraclebase/app/oracle/product/18.0.0/db11g_home
[oracle@test preupgrade]$ export PATH=/oraclebase/app/oracle/product/18.0.0/db11g_home/bin:$PATH
[oracle@test ]$
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 8 19:48:36 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-08 19:42:16
For Source Database: DBWR18C
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 18.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. em_present NO Manual fixup recommended.
3. amd_exists NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg NO Informational only.
Further action is optional.
7. mv_refresh NO Informational only.
Further action is optional.
8. pre_fixed_objects YES None.
9. tablespaces_info NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Check INVALID objects count before upgrade :
ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
--------
0
http://www.acehints.com/2011/12/how-to-change-oracle-11g-10g-rac.html -- archivelog to noarchivelog
____________________________________________________________________________________________________________
Upgrade Database from 11.2.0.3 to 18c using DBCA :-
DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.
DBUA provides below options:
– Upgrade timezone.
– Gather dictionary statistics before upgrade.
– Make user tablespaces read only.
– Take RMAN backup before upgrade.
– Create Restore Point for Database Flashback
– Restore database backup to rollback upgrade
– Option to execute Custom scripts before and after upgrade
– show the location of DBUA logs and Alert log files.
– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.
_____________________________________________________________________________________________
[oracle@test ~]$ export ORACLE_SID=test1
[oracle@test~]$ export ORACLE_HOME=/oraclebase/app/ora18c/product/18.0.0/software
[oracle@test ~]$ export PATH=/oraclebase/app/ora18c/product/18.0.0/software/bin/:$PATH
[oracle@test ~]$ dbua
Select database name to upgrade to 18c version.
________________________________________________________________________________
Preupgrade checks :
If you want to upgrade APEX version to 18.2,download APEX software 18.2 and run apexins.sql and upgrade it.
@apexins.sql APEX APEX TEMP /i/
After preupgrade warnings are resolved,select upgrade options to upgrade the database.
Select Recovery options to recover the database in case of issues.
Create listener or use existing listener which is running.
Select option if you want to configure EM express and port number.
Post Upgrade checks :
SQL> @postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
WARNING - This script was generated for database DBWR18C.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-08 19:42:17
For Source Database: DBWR18C
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 18.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ----------------------- --------- ---------------------------
10. depend_usr_tables YES None.
11. old_time_zones_exist YES None.
12. post_dictionary YES None.
13. post_fixed_objects NO Informational only.Further action is optional.
14. upg_by_std_upgrd YES None.
The fixup scripts have been run and resovled what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
Now the database upgrade is complete and the database is ready for normal use.
SQL> select VERSION,name,open_mode from v$instance,v$database;
VERSION NAME OPEN_MODE
----------- ------- ----------
18.0.0.0.0 DBWR18C READ WRITE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_31.dat 31 0
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
31
Next Post
Oracle 18c Database Upgrade From…
datafile
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set lines 100
set pages 500
set heading off
set feedback off
set trimspool on
select 'set newname for datafile '||file_id||' to '''||file_name||''';'
from dba_data_files order by file_id;
set newname for datafile 1 to '+DATA_XDW/test/datafile/system.262.816780069';
set newname for datafile 2 to '+DATA_XDW/test/datafile/sysaux.263.816780077';
set newname for datafile 3 to '+DATA_XDW/test/datafile/pbi.362.943098607';
set newname for datafile 4 to '+DATA_XDW/test/datafile/undotbs2.266.816780099';
set newname for datafile 5 to '+DATA_XDW/test/datafile/users.391.897502637';
set newname for datafile 6 to '+DATA_XDW/test/datafile/qltxplain_data01.333.897478069';
set newname for datafile 7 to '+RECO_XDW/test/datafile/stg_temp.679.1001862685';
set newname for datafile 9 to '+DATA_XDW/test/datafile/test.579.898105713';
set newname for datafile 10 to '+DATA_XDW/test/datafile/undotbs01.514.925655041';
set newname for datafile 11 to '+DATA_XDW/test/datafile/fods.580.898105755';
set newname for datafile 14 to '+DATA_XDW/test/datafile/other.290.816861941';
set newname for datafile 16 to '+DATA_XDW/test/datafile/omstg_xxeom_artctl_mv_stg.411.898689079';
set newname for datafile 18 to '+DATA_XDW/test/datafile/stg_hist.522.915645149';
set newname for datafile 19 to '+DATA_XDW/test/datafile/edw.441.920392165';
set newname for datafile 20 to '+DATA_XDW/test/datafile/undotbs01.548.925655127';
set newname for datafile 21 to '+DATA_XDW/test/datafile/undotbs01.528.925655137';
set newname for datafile 22 to '+DATA_XDW/test/datafile/mstg.287.892722509';
set newname for datafile 23 to '+DATA_XDW/test/datafile/undotbs01.527.925655147';
set newname for datafile 24 to '+DATA_XDW/test/datafile/bdw.403.892722977';
set newname for datafile 25 to '+DATA_XDW/test/datafile/undotbs01.525.925655155';
set newname for datafile 26 to '+DATA_XDW/test/datafile/undotbs01.521.925655165';
set newname for datafile 29 to '+DATA_XDW/test/datafile/sysaux.402.978467891';
set newname for datafile 168 to '+DATA_XDW/test/datafile/sysaux.462.869854087';
set newname for datafile 200 to '+DATA_XDW/test/datafile/system.602.893182993';
logfile
select 'alter database rename file '''||member||''' to '''||replace(member,'&sourcedb','&targetdb')||''';' from v$logfile;
ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_4.269.904561065'
multiplexing controlfile
sqlplus '/ as sysdba'
SQL> create pfile='/tmp/spfileBACKUP.ora' from spfile;
sqlplus '/ as sysdba'
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA01/MYDB/controlfile/current.200.899418013
SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01' scope=spfile sid='*';
sqlplus '/ as sysdba'
SQL> shutdown immediate;
SQL> startup nomount;
rman
RMAN> connect target /
RMAN> restore controlfile from '+DATA1/MYDB/controlfile/current.200.899418013';
sqlplus '/ as sysdba'
SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01/MYDB/controlfile/current.200.800418013' scope=spfile sid='*';
alter database rename file '+DATA_XDW/test/onlinelog/group_1.259.932591795' to '+RECO_DG01/test/onlinelog/group_1.259.932591795';
alter database rename file '+DATA_XDW/test/onlinelog/group_1.258.932591799' to '+RECO_DG01/test/onlinelog/group_1.258.932591799';
alter database rename file '+DATA_XDW/test/onlinelog/group_2.261.932591803' to '+RECO_DG01/test/onlinelog/group_2.261.932591803';
alter database rename file '+DATA_XDW/test/onlinelog/group_2.260.932591807' to '+RECO_DG01/test/onlinelog/group_2.260.932591807';
alter database rename file '+DATA_XDW/test/onlinelog/group_3.269.932591811' to '+RECO_DG01/test/onlinelog/group_3.269.932591811';
alter database rename file '+DATA_XDW/test/onlinelog/group_3.268.932591815' to '+RECO_DG01/test/onlinelog/group_3.268.932591815';
alter database rename file '+DATA_XDW/test/onlinelog/group_4.271.932591821' to '+RECO_DG01/test/onlinelog/group_4.271.932591821';
alter database rename file '+DATA_XDW/test/onlinelog/group_4.270.932591823' to '+RECO_DG01/test/onlinelog/group_4.270.932591823';
alter database rename file '+DATA_XDW/test/onlinelog/group_5.274.932591829' to '+RECO_DG01/test/onlinelog/group_5.274.932591829';
alter database rename file '+DATA_XDW/test/onlinelog/group_5.273.932591833' to '+RECO_DG01/test/onlinelog/group_5.273.932591833';
alter database rename file '+DATA_XDW/test/onlinelog/group_6.276.932591837' to '+RECO_DG01/test/onlinelog/group_6.276.932591837';
alter database rename file '+DATA_XDW/test/onlinelog/group_6.275.932591841' to '+RECO_DG01/test/onlinelog/group_6.275.932591841';
alter database rename file '+DATA_XDW/test/onlinelog/group_7.278.932591845' to '+RECO_DG01/test/onlinelog/group_7.278.932591845';
alter database rename file '+DATA_XDW/test/onlinelog/group_7.277.932591849' to '+RECO_DG01/test/onlinelog/group_7.277.932591849';
alter database rename file '+DATA_XDW/test/onlinelog/group_8.280.932591853' to '+RECO_DG01/test/onlinelog/group_8.280.932591853';
alter database rename file '+DATA_XDW/test/onlinelog/group_8.279.932591857' to '+RECO_DG01/test/onlinelog/group_8.279.932591857';
alter database rename file '+DATA_XDW/test/onlinelog/group_9.336.819990135' to '+RECO_DG01/test/onlinelog/group_9.336.819990135';
alter database rename file '+DATA_XDW/test/onlinelog/group_9.337.819990137' to '+RECO_DG01/test/onlinelog/group_9.337.819990137';
alter database rename file '+DATA_XDW/test/onlinelog/group_10.338.819990141' to '+RECO_DG01/test/onlinelog/group_10.338.819990141';
alter database rename file '+DATA_XDW/test/onlinelog/group_10.339.819990145' to '+RECO_DG01/test/onlinelog/group_10.339.819990145';
alter database rename file '+DATA_XDW/test/onlinelog/group_11.340.819990165' to '+RECO_DG01/test/onlinelog/group_11.340.819990165';
alter database rename file '+DATA_XDW/test/onlinelog/group_11.341.819990169' to '+RECO_DG01/test/onlinelog/group_11.341.819990169';
alter database rename file '+DATA_XDW/test/onlinelog/group_12.342.819990173' to '+RECO_DG01/test/onlinelog/group_12.342.819990173';
alter database rename file '+DATA_XDW/test/onlinelog/group_12.343.819990177' to '+RECO_DG01/test/onlinelog/group_12.343.819990177';
alter database rename file '+DATA_XDW/test/onlinelog/group_13.344.819990201' to '+RECO_DG01/test/onlinelog/group_13.344.819990201';
alter database rename file '+DATA_XDW/test/onlinelog/group_13.345.819990205' to '+RECO_DG01/test/onlinelog/group_13.345.819990205';
alter database rename file '+DATA_XDW/test/onlinelog/group_14.346.819990209' to '+RECO_DG01/test/onlinelog/group_14.346.819990209';
alter database rename file '+DATA_XDW/test/onlinelog/group_14.347.819990213' to '+RECO_DG01/test/onlinelog/group_14.347.819990213';
alter database rename file '+DATA_XDW/test/onlinelog/group_15.348.819990229' to '+RECO_DG01/test/onlinelog/group_15.348.819990229';
alter database rename file '+DATA_XDW/test/onlinelog/group_15.349.819990233' to '+RECO_DG01/test/onlinelog/group_15.349.819990233';
alter database rename file '+DATA_XDW/test/onlinelog/group_16.350.819990237' to '+RECO_DG01/test/onlinelog/group_16.350.819990237';
alter database rename file '+DATA_XDW/test/onlinelog/group_16.351.819990239' to '+RECO_DG01/test/onlinelog/group_16.351.819990239';
alter database rename file '+DATA_XDW/test/onlinelog/group_17.352.819990269' to '+RECO_DG01/test/onlinelog/group_17.352.819990269';
alter database rename file '+DATA_XDW/test/onlinelog/group_17.353.819990273' to '+RECO_DG01/test/onlinelog/group_17.353.819990273';
SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE;
RUN
{
# Set a new location for logs 1 through 100.
SET ARCHIVELOG DESTINATION TO '/fs1/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 1 UNTIL SEQUENCE 100;
# Set a new location for logs 101 through 200.
SET ARCHIVELOG DESTINATION TO '/fs2/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 101 UNTIL SEQUENCE 200;
# Set a new location for logs 201 through 300.
SET ARCHIVELOG DESTINATION TO '/fs3/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 201 UNTIL SEQUENCE 300;
# restore and recover datafiles as needed
.
.
.
}
________________________________________________
create.sql_prd
SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;
Tablespace altered.
SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;
Tablespace altered.
SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;
Tablespace altered.
SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;
Tablespace altered.
SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;
Tablespace altered.
