ODA X6-2S - upgrade from 12.1.2.10.0 to 12.1.2.11.0 ... just a few remarks

Did an update of our ODA X6-2S from 12.1.2.10.0 to 12.1.2.11.0 Patch Bundle and want to share some findings. 

The patching process is described quite good in the README - nevertheless, a few remarks:

• the whole process took about 2 hours - without downloading the necessary files and preparations (for example: read the README ;-))
• The time, the update process takes, depends on the number of databases running on that system (in our case just four)
• The README does not clearly mention that the system is rebooted two times. 
  First time after step odacli update-server, second after step odacli update-storage. But not immediately after the update step - the system waits for 5 Minutes until it starts rebooting (!)
  Do not start any following step before the reboot has taken place and the system is available again. 
• README chapter 4 - Post Patching Steps - tells not the whole truth: oracle talks about issuing an update-image command. But, in the Image-Patch README is mentioned that You have to issue a
  
  odacli update-repository --fileName /tmp/oda-sm-12.1.2.11.0-170503-DB-12.1.0.2.zip
  
  Long story told short:

1. do the 'update-repository' step first 
2. '--fileName' will throw an error - the command is: '--filename' (all lower case) or - alternatively '-f'
3. after updating the repository, issue the update-image

That's it, basically. Happy upgrading!

Read More

ODA X6 - netca cannot add listener - displaying 'Use another port number' messages

Tried to create e new listener to run on a non-default portnumber on an ODA X6. Each time when adding a new port number (doesn't matter which number) an error screen pops up, stating that I shall choose another port number:

I checked more than once and using different tools - there is no port like this used. Finally I checked the logfile - to be found here: 

<GRID_HOME>/cfgtoollogs/netca/trace_OraGrid<something>.log 

it shows error messages like these:

[AWT-EventQueue-0] [ 2017-06-13 13:54:55.424 CEST ] [ConfigureListenerOPS.buildAddressString:787]  Building Address for TCP:

        (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>.<domain>)(PORT=1533)(IP=FIRST))

oracle.net.ca.IllegalEndpointException: The information provided for this listener is currently in use by another listener on this computer.

followed by

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.325 CEST ] [ConfigureListener.validateEndPoint:1059]  Validating end-point: TCP:1534

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.326 CEST ] [ConfigureListenerOPS.buildTCPAddress:807]  Building TCP Address with HOST <hostname>.<domain>

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.327 CEST ] [ConfigureListenerOPS.buildAddressString:787]  Building Address for TCP:

        (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>.<domain>)(PORT=1534)(IP=FIRST))

oracle.ops.mgmt.cluster.ClusterException: PRKC-1099 : The host names or IP addresses passed as an argument are null.

and finally

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.331 CEST ] [ConfigureListenerOPS.isPortFree:1152]  PRKC-1099 : The host names or IP addresses passed as an argument are null.

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.331 CEST ] [ConfigureListenerOPS.isPortFree:1153]  Failed to check if port 1534 is available on cluster nodes. !

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.332 CEST ] [ConfigureListenerOPS.isPortFree:1161]  Is Port 1534 free on cluster nodes: false

oracle.net.ca.IllegalEndpointException: Port 1534 provided for this listener is already in use on node(s) of Oracle Clusterware.

RUBBISH!! The real reason is mentioned earlier in the logfile:

[Thread-8] [ 2017-06-13 13:54:38.899 CEST ] [StreamReader.run:65]  ERROR>No RSA host key is known for <hostname> and you have requested strict checking.

[Thread-8] [ 2017-06-13 13:54:38.899 CEST ] [StreamReader.run:65]  ERROR>Host key verification failed.

...

[main] [ 2017-06-13 13:54:38.901 CEST ] [UnixSystem.checkRemoteExecutionSetup:2417]  checkRemoteExecutionSetup:: Error checking user equivalence using Secured Shell '/usr/bin/ssh'

[main] [ 2017-06-13 13:54:38.908 CEST ] [HAUtils.getAccessibleNodes:3135]  <hostname> is not reachable PRKC-1044 : Failed to check remote command execution setup for node <hostname> using shells /usr/bin/ssh and /usr/bin/rsh

File "/usr/bin/rsh" does not exist on node "<hostname>"

No RSA host key is known for <hostname> and you have requested strict checking.Host key verification failed.

Solution

Long story told short: simply configure user equivalence:

# change to HOME directory:

cd

# create a directory called .ssh (if not existing)

mkdir .ssh

# change mod to 700

chmod 700 .ssh

# change directory to .ssh

cd .ssh

# create the RSA key file

ssh-keygen -t rsa

(accept the default location for the key file and confirm the passphrase (usually press enter twice to leave the passphrase empty))

# create the DSA keyfile

ssh-keygen -t dsa

(accept the default location for the key file and confirm the passphrase (usually press enter twice to leave the passphrase empty))

# create the authorized keys files (one with the hostname for better visibility what node information is in - in case You want to copy that file to other hosts)

cat *.pub >> authorized_keys.<hostname>

cp authorized_keys.<hostname> authorized_keys

# now, add the host to the list of the known_hosts (I used this command - other commands are available as well):

/usr/bin/ssh -o FallBackToRsh=no  -o PasswordAuthentication=no  -o StrictHostKeyChecking=no  -o NumberOfPasswordPrompts=0 <hostname> /bin/true

# Result of the last command

Warning: Permanently added '<hostname>,<host's IP>' (RSA) to the list of known hosts.

You should now have the following files in Your .ssh directory

authorized_keys

authorized_keys.<hostname>

id_dsa

id_dsa.pub

id_rsa

id_rsa.pub

known_hosts

When You now try to create a new listener, everything works fine :-)

It could be that easy! ;-)

Read More

ODA X4 - Migrating a database from ASM to ACFS

As promised ... Here's the post about migrating a database from ASM to ACFS on an ODA 4 - probably, this will work for newer ODA Servers as well

Given:

- Oracle Database Appliance machine, X4-<something>
- Software Release 12.1.2.7.0
- Prepared DB Software: 11.2.0.3, 11.2.0.4, 12.1.0.2

Story behind:

Task was, to create a new database of release 11.2.0.3 for testing purposes. Command used to create the database:

oakcli create database -db TDB -oh /u01/app/oracle/product/11.2.0.3/dbhome_1

After that, I started configuring the database, creating tablespaces, schemas, setting parameters, and so on ... lots of work, actually.

What I expected was a database using ACFS as storage option (as all the other databases on that system) - what I got was ASM ... this, because starting with software release 12.1.2.0 the default storage option is ACFS for all databases of release 11.2.0.4 an higher. In other words: creating a database 11.2.0.3, using oakcli, will result in a database using ASM. At that stage, I had two possibilities: Drop the database and recreate it - or migrate an ASM DB to ACFS. Decision was: ACFS :-)

Found a document which describes that in very detail: STEPS TO MIGRATE NON-CDB DATABASES TO ACFS ON ORACLE DATABASE APPLIANCE 12.1.2 . I used it, but found a few bugs in that doc and decided to write a blog post to present a shorter story.

Step 1 - create an empty snapshot (as user root)

To prepare the filesystem / directory structure for the new ACFS based database and to be able to use the snapshot feature, one have to create an empty snapshot

acfsutil snap create -w TDB /u02/app/oracle/oradata/datastore

Step 2 - ACFS: create directories to store the datafiles and other stuff (as user root)

mkdir /u01/app/oracle/oradata/datastore/TDB
mkdir /u01/app/oracle/fast_recovery_area/datastore/TDB
mkdir /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB
chown oracle:oinstall /u01/app/oracle/oradata/datastore/TDB
chown oracle:oinstall /u01/app/oracle/fast_recovery_area/datastore/TDB
chown oracle:oinstall /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB

Step 3 - File destinations: prepare the spfile (as user oracle - sql)

alter system set db_create_file_dest='/u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB' scope=both sid='*';
alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/datastore/TDB' scope=both sid='*';
alter system set db_create_online_log_dest_2='/u01/app/oracle/oradata/datastore/TDB' scope=both sid='*';
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/datastore/TDB' scope=both sid='*';

Step 4 - Migrate data files, control files and online redo logs (as user oracle)

First, copy the data files to the new destination by using rman and the 'copy database to destination' feature, and switch the database to the copy:

<Prompt>: rman target /

RMAN> startup mount
...
RMAN> backup as copy database to destination '/u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB'; ...
RMAN> switch database to copy;
...
RMAN> exit

Next, get the current control file name(s), using sqlplus, and reset the control files

<Prompt>:  sqlplus / as sysdba
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+REDO/TDB/controlfile/current.266.943174969
SQL> alter system reset control_files;
System altered.
SQL> exit

Restore the control file to ACFS (with rman)

<Prompt>:  rman target /
RMAN> shutdown immediate
...
RMAN> startup nomount
...
RMAN> restore controlfile from '+REDO/TDB/controlfile/current.266.943174969';
...
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2too_.ctl
output file name=/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2tqf_.ctl
Finished restore at 05-MAY-17

RMAN> exit

Change the control_files parameter in the database's spfile

<Prompt>:  sqlplus / as sysdba
SQL> alter system set control_files='/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2too_.ctl','/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2tqf_.ctl' scope=spfile sid='*';

System altered.

Last activity in this step: Migrate the online redo logs. Open sqlplus and execute the following:

declare
 cursor rlc is
   select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
     from v$log
   union
   select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
     from v$standby_log
     order by 1;
 stmt varchar2(2048);
 swtstmt varchar2(1024) := 'alter system switch logfile';
 archstmt varchar2(1024) := 'alter system archive log current';
 ckpstmt varchar2(1024) := 'alter system checkpoint global';
begin
 for rlcRec in rlc loop
   if (rlcRec.srl = 'YES') then
     stmt := 'alter database add standby logfile thread ' ||
       rlcRec.thr || ' size ' ||
       rlcRec.bytes_k || 'K';
     execute immediate stmt;
     stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
     execute immediate stmt;
   else
     stmt := 'alter database add logfile thread ' || rlcRec.thr || ' size ' || rlcRec.bytes_k || 'K';
      dbms_output.put_line(stmt);
     execute immediate stmt;
     begin
       stmt := 'alter database drop logfile group ' || rlcRec.grp;
       dbms_output.put_line(stmt);
       execute immediate stmt;
     exception
       when others then
         execute immediate swtstmt;
         execute immediate archstmt;
         execute immediate ckpstmt;
         execute immediate stmt;
     end;
   end if;
 end loop;
end;
/

What about the Tempfiles? This (as user oracle):

<Prompt>:  sqlplus / as sysdba
-- get ddl of current TEMP
select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual;

-- rename the tablespace
alter tablespace temp rename to temp_old;

-- create a new TEMP by using the information from the GET_DDL
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 1468006400 AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;

-- make the new tablespace to the new default TEMP
alter database default temporary tablespace TEMP;

-- drop old temp tablespace
drop tablespace temp_old;

Move spfile and password file to the new location and switch off ASM usage (as user root)

First, move spfile and pwdfile from ASM to ACFS. Use asmcmd to get current spfile and password file names

<Prompt>: su - grid -c "asmcmd"
ASMCMD> ls -l DATA/TDB

copy the spfile to the new location spfile (and the password file)

su - grid -c "asmcmd cp '+DATA/TDB/PARAMETERFILE/spfile.285.943175449' /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora"

do not forget to change the owner:group for the new files:

chown oracle:oinstall /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora

spfile has been copied to the new directory - but the database is not aware of that ...:

srvctl config database -d TDB
Database unique name: TDB
...

Change spfile location in the database config (the -p option) and tell the DB that it does not need Disk Groups anymore (the -z option):

srvctl modify database -d TDB -p /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora -z

Check config:

Database unique name: TDB
...
Spfile: /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora
...
Disk Groups: 
...
Database is administrator managed

Stop the database and start again

srvctl stop database -d TDB
srvctl start database -d TDB

Final steps - clean up (as user oracle)

If the DB is running properly and the alert log does not show any problems, than it's time to clean up and remove the old datafile copies

<Prompt>: rman target /
RMAN> list datafilecopy all;
...
List of Datafile Copies
=======================
...

For each datafile copy

RMAN> delete noprompt datafilecopy '<name as shown in list datafilecopy>';

Conclusion

Several steps to execute - but it's working fine. Can save a lot of time and makes a export / import scenario obsolete.

Read More

Coming up next: ODA X4 - migrating a database from ASM to ACFS

When using oakcli 12.1.2.x, a 'create database' command will create the database on ACFS ... except for databases of versions lower than 11.2.0.4. - those databases are created on ASM. So, instead of dropping and recreating a database, You could 'simply' migrate from ASM to ACFS. The coming post will describe the 'how-to' and the necessary steps to do so.

Read More