Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The database upgrade stages with almost zero interruption from the current Data Guard configuration Single Node Primary and Physical Standby are as follows.
1. We set up a Physical Standby database.
2. We are preparing Primary and Standby databases for Upgrade. For this, the first thing to do is to activate the Flashback database in both databases.
Flashback database must be activated in Primary. Because the main flashback before starting one of the steps, upgrade, will be returned.
In the standby database, the reason for commissioning is to return to the guarantee restore point that we will create our system in case of possible failure.
a. We are querying whether the Flashback Database feature is enabled.
1 2 3 4 5 | [Source - 26-04-2017 10:08:19] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO |
1 2 3 4 5 | [Standby - 26-04-2017 10:08:19] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO |
b. We activate Flashback Database in primary database.
1 2 3 | [Source - 26-04-2017 10:17:09] SQL> alter database flashback on; Database altered. |
c. We activate Flashback Database in standby database.
1 2 3 4 5 6 7 8 9 10 11 | [Standby - 26-04-2017 10:18:54] SQL> alter database recover managed standby database cancel; Database altered. [Standby - 26-04-2017 10:19:05] SQL> alter database flashback on; Database altered. [Standby - 26-04-2017 10:19:07] SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
d. We are questioning whether Flashback Database is activated in databases.
1 2 3 4 5 | [Source - 26-04-2017 10:18:26] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
1 2 3 4 5 | [Standby - 26-04-2017 10:19:43] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
3. We define Restore Points in Primary and Standby databases.
a. We are making identification in the primary database.
1 2 3 | [Source - 26-04-2017 10:19:40] SQL> create restore point pre_upgrade guarantee flashback database; Restore point created. |
1 2 3 4 5 6 7 8 9 10 11 | [Standby - 26-04-2017 10:22:11] SQL> alter database recover managed standby database cancel; Database altered. [Standby - 26-04-2017 10:22:19] SQL> create restore point pre_upgrade_physcl guarantee flashback database; Restore point created. [Standby - 26-04-2017 10:22:21] SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
This means that although Physical Standby is converted to Logical, it is temporary, so it should not forget its ID (DB_NAME and DB_ID) and will be converted back to Physical.
a. We create a LogMiner dictionary in the primary database so that SQL Apply can correctly interpret the transactional changes.
1 2 3 | [Source - 26-04-2017 10:21:13] SQL> exec dbms_logstdby.build; PL/SQL procedure successfully completed. |
b. Redo Apply is stopped in the standby database and the instance is put into MOUNT mode.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [Standby - 26-04-2017 10:22:30] SQL> alter database recover managed standby database cancel; Database altered. [Standby - 26-04-2017 10:27:42] SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. [Standby - 26-04-2017 10:28:07] SQL> startup mount; ORACLE instance started. Total System Global Area 3273641984 bytes Fixed Size 2257680 bytes Variable Size 738200816 bytes Database Buffers 2516582400 bytes Redo Buffers 16601088 bytes Database mounted. |
c. The Physical Standby database is converted to Logical.
1 | [Standby - 26-APR-17] SQL> alter database recover to logical standby keep identity; |
This command just waits without any feedback at this stage. The reason is that it searches for LogMiner Dictionary in logs. For this reason, LogMiner Dictionary is created again in the Primary database.
1 2 3 | [Source - 26-04-2017 10:27:11] SQL> exec dbms_logstdby.build; PL/SQL procedure successfully completed. |
As soon as the PL/SQL Procedure is completed, the conversion command to Logical is also completed.
d. The conversion process is concluded by opening the database.
1 2 3 | [Standby - 26-APR-17] SQL> alter database open; Database altered. |
e. We pass to the controls.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Standby - 26-APR-17] SQL> select status from v$instance; STATUS ------------ OPEN [Standby - 26-APR-17] SQL> select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE LOGICAL STANDBY [Standby - 26-APR-17] SQL> alter database start logical standby apply immediate; Database altered. |
f. By creating a table in the primary database, we check whether it is reflected in the Standby database.
1 2 3 4 5 6 7 8 9 | [Source - 26-04-2017 10:33:05] SQL> create table test.employees_yedek as select * from hr.employees; Table created. [Source - 26-04-2017 10:33:26] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
1 2 3 4 5 | [Standby - 26-APR-17] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
5. Automatic deletion of Foreign Archive Logs is disabled, in case of need during the upgrade.
1 2 3 | [Standby - 26-APR-17] SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE'); PL/SQL procedure successfully completed. |
6. Preparations before Upgrade are completed in the Logical Standby database.
a. Sending logs to Logical Standby is prevented.
1 2 3 | [Source - 26-04-2017 10:33:43] SQL> alter system set log_archive_dest_state_2=DEFER scope=memory; System altered. |
b. By stopping SQL Apply, a guaranteed restore point is defined.
1 2 3 4 5 6 7 | [Standby - 26-APR-17] SQL> alter database stop logical standby apply; Database altered. [Standby - 26-APR-17] SQL> create restore point pre_upgrade_logical guarantee flashback database; Restore point created. |
c. We are creating some tables to see that the transactions coming during the upgrade are processed to Logical Standby after the SQL Apply is started after the Upgrade.
1 2 3 4 5 6 7 | [Source - 26-04-2017 10:37:46] SQL> create table test.jobs_yedek as select * from hr.jobs; Table created. [Source - 26-04-2017 10:41:54] SQL> create table test.job_history_yedek as select * from hr.job_history; Table created. |
d. We create the paths where the new version will be installed.
1 2 3 | [root@standby ~]# mkdir -p /u01/app/oracle/product/12.1.0/db_1 [root@standby ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.1.0/db_1 [root@standby ~]# chmod -R 775 /u01/app/oracle/product/12.1.0/db_1 |
7. 12cR1’s Software is installed on Logical Standby.
8. Go to 12cR1’s ORACLE_HOME/bin directory and run DBUA.
1 | ./dbua |
9. We question whether the upgrade has taken place.
1 2 3 4 5 6 7 8 9 | [Standby - 26-04-2017 13:41:01] SQL> select banner from v$version; BANNER ---------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production |
10. The log flow from the Primary database to Standby is provided again and it is questioned whether the Standby database is SYNC with the Primary.
a. Log flow of Primary to Standby is started.
1 2 3 | [Source - 26-04-2017 13:13:44] SQL> alter system set log_archive_dest_state_2=ENABLE scope=memory; System altered. |
b. SQL Apply starts on the standby database.
1 2 3 | [Source - 26-04-2017 13:12:30] SQL> alter database start logical standby apply immediate; Database altered. |
c. We check whether the tables created in Primary during the upgrade are formed in Standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Source - 26-04-2017 13:15:51] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK [Standby- 26-04-2017 13:15:51] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOB_HISTORY_YEDEK JOBS_YEDEK EMPLOYEES_YEDEK |
d. The Primary’s Standby and SYNC are checked.
1 2 3 4 5 | [Standby - 26-04-2017 13:25:57] SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS; SYSDATE APPLIED_TIME ------------------- ------------------- 26-04-2017 13:26:06 26-04-2017 13:26:05 |
11. We question whether the logs go from Primary to Standby without any problems.
a. Existing Log sequence numbers are queried.
1 2 3 4 5 | [Source - 26-04-2017 13:15:54] SQL> select max(sequence#) from v$archived_log group by thread#; MAX(SEQUENCE#) -------------- 27 |
1 2 3 4 5 | [Standby- 26-04-2017 13:16:15] SQL> select max(sequence#), applied from dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) APPLIED -------------- -------- 27 YES |
b. Log Switch operation is performed in Primary.
1 2 3 | [Source - 26-04-2017 13:16:48] SQL> alter system switch logfile; System altered. |
c. We are checking whether the logs go to Standby.
1 2 3 4 5 | [Source - 26-04-2017 13:17:54] SQL> select max(sequence#) from v$archived_log group by thread#; MAX(SEQUENCE#) -------------- 28 |
1 2 3 4 5 | [Standby- 26-04-2017 13:17:35] SQL> select max(sequence#), applied from dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) APPLIED -------------- -------- 28 YES |
12. Logical Standby, which is passed to 12cR1 with Switchover, is made Primary and Standby is made in Primary, which is 11gR2.
a. We question the compatibility of Primary with Switchover.
1 2 3 4 5 | [Source - 26-04-2017 13:30:52] SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- SESSIONS ACTIVE |
b. Primary database is converted to Standby.
1 2 3 | [Source - 26-04-2017 13:31:00] SQL> alter database commit to switchover to logical standby; Database altered. |
c. We question the suitability of Logical Standby to Primary.
1 2 3 4 5 | [Standby - 26-04-2017 13:26:47] SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY |
d. Converted to Logical Standby Primary.
1 2 3 | [Standby - 26-04-2017 13:32:07] SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered. |
e. We pass to the controls.
1 2 3 4 5 6 7 8 9 10 11 | [Source - 26-04-2017 13:31:55] SQL> select status from v$instance; STATUS ------------ OPEN [Source - 26-04-2017 13:33:27] SQL> select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE LOGICAL STANDBY |
1 2 3 4 5 6 7 8 9 10 11 | [Standby - 26-04-2017 13:33:07] SQL> select status from v$instance; STATUS ------------ OPEN [Standby - 26-04-2017 13:33:27] SQL> select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE PRIMARY |
1 2 3 4 5 | [Source - 26-04-2017 13:33:39] SQL> select recovery_mode from v$archive_dest_status where dest_id <2; RECOVERY_MODE ----------------------- IDLE |
f. Tables are created in the new Primary.
1 2 3 4 5 6 7 | [Standby - 26-04-2017 13:34:03] SQL> create table test.regions_yedek as select * from hr.regions; Table created. [Standby - 26-04-2017 13:38:01] SQL> create table test.locations_yedek as select * from hr.locations; Table created. |
13. The new Logical Standby database is flashbacked to the Restore Point taken before the Upgrade. The reason for this operation is to initially bring the database to the moment of the Physical Standby database before it is pulled to Logical.
Thus, when I complete the conversion from Logical to Physical, the old Primary database will be a Physical Standby that came before the Upgrade.
a. We get the original Primary database MOUNT mod.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Source - 26-04-2017 13:45:03] SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. [Source - 26-04-2017 13:55:41] SQL> startup mount; ORACLE instance started. Total System Global Area 3273641984 bytes Fixed Size 2257680 bytes Variable Size 738200816 bytes Database Buffers 2516582400 bytes Redo Buffers 16601088 bytes Database mounted. |
b. Restore Point’s information is learned.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Source - 26-APR-17] SQL> desc v$restore_point; Name Null? Type ----------------------------------------- -------- ---------------------------- SCN NUMBER DATABASE_INCARNATION# NUMBER GUARANTEE_FLASHBACK_DATABASE VARCHAR2(3) STORAGE_SIZE NUMBER TIME TIMESTAMP(9) RESTORE_POINT_TIME TIMESTAMP(9) PRESERVED VARCHAR2(3) NAME VARCHAR2(128) [Source - 26-APR-17] SQL> select max(length(name)) from v$restore_point; MAX(LENGTH(NAME)) ----------------- 11 [Source - 26-APR-17] SQL> column name format a11 [Source - 26-APR-17] SQL> select SCN, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, TIME, RESTORE_POINT_TIME, NAME from v$restore_point; SCN DATABASE_INCARNATION# GUA TIME RESTORE_POINT_TIME NAME ---------- --------------------- --- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------- 1082928 2 YES 26-APR-17 10.21.13.000000000 AM PRE_UPGRADE |
c. We are doing a flashback to Restore Point.
1 2 3 | [Source - 26-04-2017 14:00:35] SQL> flashback database to restore point pre_upgrade; Flashback complete. |
[Source]–>ALERT_LOG
1 2 3 4 5 6 7 8 9 10 11 12 | Wed Apr 26 14:01:21 2017 flashback database to restore point pre_upgrade Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start started logmerger process Parallel Media Recovery started with 4 slaves Flashback Media Recovery Log /u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2017_04_26/o1_mf_1_15_dj0lyc6d_.arc Wed Apr 26 14:01:23 2017 Incomplete Recovery applied until change 1082929 time 04/26/2017 10:21:13 Flashback Media Recovery Complete Completed: flashback database to restore point pre_upgrade |
14. In the original Primary database, the paths where 12cR1 will be loaded are created.
1 2 3 | [root@source ~]# mkdir -p /u01/app/oracle/product/12.1.0/db_1 [root@source ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.1.0/db_1 [root@source ~]# chmod -R 775 /u01/app/oracle/product/12.1.0/db_1 |
15. 12cR1’s Software is installed in the original Primary database.
16. We are editing some files as there is no 12cR1 database in the Original Primary database yet.
a. There are no files like LISTENER, TNSNAMES. We copy such files to new paths.
1 2 3 4 5 6 7 8 9 | [root@source ~]# su - oracle [oracle@source ~]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsource /u01/app/oracle/product/12.1.0/db_1/dbs/ [oracle@source ~]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/spfilesource.ora /u01/app/oracle/product/12.1.0/db_1/dbs/ [oracle@source ~]$ cd /u01/app/oracle/product/12.1.0/db_1/dbs/ [oracle@source dbs]$ ll total 12 -rw-r--r-- 1 oracle oinstall 2992 Feb 3 2012 init.ora -rw-r----- 1 oracle oinstall 1536 Apr 26 14:16 orapwsource -rw-r----- 1 oracle oinstall 3584 Apr 26 14:16 spfilesource.ora |
b. It is organized according to Bash Profile 12cR1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | [oracle@source ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_UNQNAME=source; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE DB_HOME=$ORACLE_BASE/product/12.1.0/db_1; export DB_HOME ORACLE_HOME=$DB_HOME; export ORACLE_HOME ORACLE_SID=source; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM BASE_PATH=/usr/sbin:$PATH; export BASE_PATH PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$BASE_PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH ulimit -u 65536 ulimit -n 65536 alias log='cd /u01/app/oracle/diag/rdbms/source/source/trace; pwd' alias oh='cd $ORACLE_HOME; pwd' |
c. Bash Profile is enabled to be active. We check if the change has taken place.
1 | [oracle@source ~]$ . .bash_profile |
1 2 | [oracle@source ~]$ echo $ORACLE_HOME /u01/app/oracle/product/12.1.0/db_1 |
17. In the new Software location, we connect to the database and MOUNT.
1 2 3 4 5 6 7 | [oracle@source ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 26 14:23:23 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. |
1 2 3 4 5 6 7 8 9 | [Source - 26-APR-17] SQL> startup mount; ORACLE instance started. Total System Global Area 3288334336 bytes Fixed Size 2929744 bytes Variable Size 738200496 bytes Database Buffers 2533359616 bytes Redo Buffers 13844480 bytes ORA-00205: error in identifying control file, check alert log for more info |
[Source]–>ALERT_LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Wed Apr 26 14:48:46 2017 ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/source/control02.ctl' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8 Additional information: 27431 ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/source/control01.ctl' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8 Additional information: 27431 ORA-205 signalled during: ALTER DATABASE MOUNT... |
18. It turns out that the error is related to the control file locations. The control file location is reduced to one and the problem is solved by changing its name.
1 2 3 4 5 | [Source - 26-04-2017 14:50:27] SQL> shu immediate; ORA-01507: database not mounted ORACLE instance shut down. |
1 2 3 4 5 6 7 8 9 | [Source - 26-APR-17] SQL> startup mount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initsource.ora'; ORACLE instance started. Total System Global Area 3288334336 bytes Fixed Size 2929744 bytes Variable Size 738200496 bytes Database Buffers 2533359616 bytes Redo Buffers 13844480 bytes Database mounted. |
19. The original database is now converted to Physical Standby.
1 2 3 | [Source - 26-APR-17] SQL> alter database convert to physical standby; Database altered. |
20. The database is closed and taken to the MOUNT step.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Source - 26-APR-17] SQL> shu immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. [Source - 26-APR-17] SQL> startup mount; ORACLE instance started. Total System Global Area 3288334336 bytes Fixed Size 2929744 bytes Variable Size 738200496 bytes Database Buffers 2533359616 bytes Redo Buffers 13844480 bytes Database mounted. |
21. We are checking whether the database has passed to Physical Standby.
1 2 3 4 5 6 7 8 9 10 11 | [Source - 26-APR-17] SQL> select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED PHYSICAL STANDBY [Source - 26-APR-17] SQL> select recovery_mode from v$archive_dest_status where dest_id<2; RECOVERY_MODE ----------------------- IDLE |
22. It is ensured that the logs come from Primary to Standby and Redo Apply is started.
1 2 3 4 5 6 7 | [Standby - 26-04-2017 15:17:00] SQL> alter system set log_archive_dest_2='SERVICE=source SYNC REOPEN=15 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=source' scope=both; System altered. [Standby - 26-04-2017 15:17:55] SQL> alter system set log_archive_dest_state_2=ENABLE scope=both; System altered. |
[Standby]–>ALERT_LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Fatal NI connect error 12504, connecting to: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=)(CID=(PROGRAM=oracle)(HOST=standby.tivibulab.local)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.42.67)(PORT=1521))) VERSION INFORMATION: TNS for Linux: Version 12.1.0.2.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production Time: 26-APR-2017 15:20:21 Tracing not turned on. Tns error struct: ns main err code: 12564 TNS-12564: TNS:connection refused ns secondary err code: 0 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 Wed Apr 26 15:20:21 2017 Error 12504 received logging on to the standby |
There are some points to check when redos are not sent. These are, respectively,
TNSNAMES.ORA, LISTENER.ORA, PASSWORD FILES,
a. We check the TNSNAMES.ORA file in Primary.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [oracle@standby admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.tivibulab.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) ) LISTENER_STANDBY = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.tivibulab.local)(PORT = 1521)) |
b. It is understood that there is no Standby and the Standby database is added to TNSNAMES.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@standby admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/productk/12.1.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.tivibulab.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) ) SOURCE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = source.tivibulab.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = source) ) ) LISTENER_STANDBY = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.tivibulab.local)(PORT = 1521)) |
c. Good versions of these files are sent to the Standby database.
1 2 3 4 5 6 7 8 | [oracle@standby admin]$ scp tnsnames.ora sqlnet.ora source:/u01/app/oracle/product/12.1.0/db_1/network/admin/ The authenticity of host 'source (172.20.42.67)' can't be established. RSA key km is c8:91:c0:8b:44:d7:4b:c6:1b:07:ed:48:a6:d1:4a:6d. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'source,172.20.42.67' (RSA) to the list of known hosts. oracle@source's password: tnsnames.ora 100% 632 0.6KB/s 00:00 sqlnet.ora 100% 198 0.2KB/s 00:00 |
1 2 3 | [oracle@standby admin]$ scp listener.ora source:/u01/app/oracle/product/12.1.0/db_1/network/admin/ oracle@source's password: listener.ora 100% 347 0.3KB/s 00:00 |
d. On the standby side, we update the LISTENER.ORA file.
1 2 3 4 5 6 7 8 9 10 11 | [oracle@source admin]$ vi listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = source.tivibulab.local)(PORT = 1521)) De (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) |
e. Listener STOP is START.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | [oracle@source admin]$ lsnrctl LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 26-APR-2017 15:44:58 Copyright (c) 1991, 2014, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source.tivibulab.local)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 26-APR-2017 09:03:12 Uptime 0 days 6 hr. 41 min. 47 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/source/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source.tivibulab.local)(PORT=1521))) Services Summary... Service "source" has 1 instance(s). Instance "source", status UNKNOWN, has 1 handler(s) for this service... Service "source_DGMGRL" has 1 instance(s). Instance "source", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully LSNRCTL> stop Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source.tivibulab.local)(PORT=1521))) The command completed successfully LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source.tivibulab.local)(PORT=1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 2: No such file or directory |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | LSNRCTL> start Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/source/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source.tivibulab.local)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source.tivibulab.local)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 26-APR-2017 15:47:58 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/source/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source.tivibulab.local)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully |
f. With all these operations, the logs were sent to Standby successfully and there were no errors left in the logs. We check whether the logs go to the standby side.
1 2 3 4 5 | [Standby - 26-04-2017 15:53:02] SQL> select max(sequence#) from v$archived_log group by thread#; MAX(SEQUENCE#) -------------- 147 |
1 2 3 4 5 6 | [Source - 26-04-2017 15:53:02] SQL> select max(sequence#),applied from v$archived_log group by thread#,applied; MAX(SEQUENCE#) APPLIED -------------- --------- 147 NO 28 YES |
23. Redo Apply is started and with this process, 12cR1 Upgrade in Standby database is completed.
1 2 3 | [Source - 26-04-2017 15:54:21] SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
[Source]–>ALERT_LOG
1 2 3 4 5 6 7 8 9 10 11 12 | Wed Apr 26 15:59:17 2017 Media Recovery Log /u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2017_04_26/o1_mf_1_44_dj15sj8c_.arc Resize operation completed for file# 3, old size 737280K, new size 747520K Resize operation completed for file# 3, old size 747520K, new size 752640K Resize operation completed for file# 3, old size 752640K, new size 762880K Resize operation completed for file# 3, old size 762880K, new size 773120K Wed Apr 26 15:59:20 2017 Media Recovery Log /u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2017_04_26/o1_mf_1_45_dj15sjog_.arc Resize operation completed for file# 3, old size 773120K, new size 783360K Resize operation completed for file# 3, old size 783360K, new size 793600K Resize operation completed for file# 3, old size 793600K, new size 798720K Wed Apr 26 15:59:22 2017 |
1 2 3 4 5 | [Source - 26-04-2017 15:57:14] SQL> select recovery_mode from v$archive_dest_status where dest_id<2; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY |
24. The standby database is opened and it is seen whether the tables have come or not.
1 2 3 4 5 6 7 8 9 10 11 | [Source - 26-04-2017 16:04:19] SQL> alter database recover managed standby database cancel; Database altered. [Source - 26-04-2017 16:05:07] SQL> alter database open; Database altered. [Source - 26-04-2017 16:05:21] SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [Standby - 26-04-2017 16:04:46] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- REGIONS_YEDEK JOB_HISTORY_YEDEK JOBS_YEDEK LOCATIONS_YEDEK EMPLOYEES_YEDEK [Source - 26-04-2017 16:05:35] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- EMPLOYEES_YEDEK LOCATIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK REGIONS_YEDEK |
25. We check if the upgrade is running smoothly in both databases.
a.
1 2 3 4 5 6 7 8 9 | [Standby - 26-04-2017 16:05:54] SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production |
1 2 3 4 5 6 7 8 9 | [Source - 26-04-2017 16:05:54] SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production |
b.
1 2 3 4 5 | [Standby - 26-04-2017 16:06:50] SQL> SELECT version FROM v$instance; VERSION ----------------- 12.1.0.2.0 |
1 2 3 4 5 | [Source - 26-04-2017 16:06:50] SQL> SELECT version FROM v$instance; VERSION ----------------- 12.1.0.2.0 |
c.
1 2 3 4 5 6 7 8 9 10 11 | [Standby - 26-04-2017 16:09:44] SQL> column PRODUCT format a39 [Standby - 26-04-2017 16:10:00] SQL> column VERSION format a10 [Standby - 26-04-2017 16:10:10] SQL> column STATUS format a16 [Standby - 26-04-2017 16:10:20] SQL> select * from product_component_version; PRODUCT VERSION STATUS --------------------------------------- ---------- ---------------- NLSRTL 12.1.0.2.0 Production Oracle Database 12c Enterprise Edition 12.1.0.2.0 64bit Production PL/SQL 12.1.0.2.0 Production TNS for Linux: 12.1.0.2.0 Production |
1 2 3 4 5 6 7 8 9 10 11 | [Source - 26-04-2017 16:08:45] SQL> column PRODUCT format a39 [Source - 26-04-2017 16:10:00] SQL> column VERSION format a10 [Source - 26-04-2017 16:10:10] SQL> column STATUS format a16 [Source - 26-04-2017 16:10:20] SQL> select * from product_component_version; PRODUCT VERSION STATUS --------------------------------------- ---------- ---------------- NLSRTL 12.1.0.2.0 Production Oracle Database 12c Enterprise Edition 12.1.0.2.0 64bit Production PL/SQL 12.1.0.2.0 Production TNS for Linux: 12.1.0.2.0 Production |
d.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Source - 26-04-2017 23:09:51] SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY [Source - 26-04-2017 23:10:21] SQL> alter database recover managed standby database cancel; Database altered. [Source - 26-04-2017 23:10:35] SQL> select recovery_mode from v$archive_Dest_status where dest_id<2; RECOVERY_MODE ----------------------- IDLE |
1 2 3 4 5 6 7 8 9 | [Source - 26-04-2017 23:11:04] SQL> alter database recover managed standby database disconnect; Database altered. [Source - 26-04-2017 23:11:23] SQL> select recovery_mode from v$archive_Dest_status where dest_id<2; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY |
Starting the recovery just by typing “disconnect” came in 12c. This is proof that we have passed to healthy 12c.
26. Compatible parameters of databases are increased.
a. We DROP Restore Point in Standby.
1 2 3 | [Source - 26-APR-17] SQL> drop restore point pre_upgrade; Restore point dropped. |
b. We change the Compatible parameter.
1 2 3 | [Source - 26-APR-17] SQL> alter system set compatible='12.1.0.2.0' scope=spfile; System altered. |
c. We close and open the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Source - 26-APR-17] SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. [Source - 26-APR-17] SQL> startup; ORACLE instance started. Total System Global Area 3288334336 bytes Fixed Size 2929744 bytes Variable Size 738200496 bytes Database Buffers 2533359616 bytes Redo Buffers 13844480 bytes Database mounted. Database opened. |
d. We check for changes.
1 2 3 4 5 6 | [Source - 26-APR-17] SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.1.0.2.0 noncdb_compatible boolean FALSE |
e. We are querying the Restore Points in Primary.
1 2 3 4 5 6 | [Standby - 26-04-2017 23:10:21] SQL> select name from v$restore_point; NAME -------------------------------------------------------------------------------- PRE_UPGRADE_PHYSCL PRE_UPGRADE_LOGICAL |
f. Restore Points in Primary are DROPed.
1 2 3 4 5 6 7 | [Standby - 26-04-2017 23:25:44] SQL> drop restore point PRE_UPGRADE_PHYSCL; Restore point dropped. [Standby - 26-04-2017 23:25:59] SQL> drop restore point PRE_UPGRADE_LOGICAL; Restore point dropped. |
g. We change the Compatible parameter.
1 2 3 | [Standby - 26-04-2017 23:26:10] SQL> alter system set compatible='12.1.0.2.0' scope=spfile; System altered. |
h. By closing the database, the parameter is activated.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Standby - 26-04-2017 23:26:16] SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. [Standby - 26-04-2017 23:26:40] SQL> startup; ORACLE instance started. Total System Global Area 3288334336 bytes Fixed Size 2929744 bytes Variable Size 788532144 bytes Database Buffers 2483027968 bytes Redo Buffers 13844480 bytes Database mounted. Database opened. |
I. We check for changes.
1 2 3 4 5 6 | [Standby - 26-APR-17] SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.1.0.2.0 noncdb_compatible boolean FALSE |
27. Summary of All Our Work
