Ayuda para tus sistemas

viernes, 2 de agosto de 2013

MySQL export sql select to csv file.

To export data from a sql statement in a MySQL environment, to a csv file:

select * into outfile '/var/tmp/1.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n' from tabla1;

This SQL statement will export the desired data to the file /var/tmp/1.csv, in this example fields are separated by commas.

jueves, 1 de agosto de 2013

Oracle 11 RAC Services, creating and managing services

Services

Services are used to manage the workload in an Oracle RAC environment. Services are designed to group a workload, so users meetings the same requeriments can be grouped by using the same service.
For example, in an Oracle Rac environment could be defined a service for users who execute small and short transactions, other service for users executing long running transactions, and so on...

Service could be defined for both admin-managed and policy-managed databases.
There are restrictions creating services on policy-managed databases, services there are assigned to a server pool and can be defined as a Singleton or Uniform service.
A Singleton service runs only on one database instance on its server pool, and the user does not have the control over wich instance will serve the service.
A Uniform service runs on all database instances in its server pool.
The administrator-managed database runs the service assigning it in the preferred instances, if the preferred instance fails the service will run on the available instance.

Managing Services.

Database Services.

This sql statement show information about services from v$services view.

SQL> select name, network_name, creation_date, goal, dtp, aq_ha_notification,clb_goal from v$services;

NAME NETWORK_NAME CREATION_ GOAL D AQ_ CLB_G
-------------------- ------------------------------ --------- ------------ - --- -----
servicetest servicetest 22-JUL-13 NONE N NO LONG
RACDBXDB RACDBXDB 07-JUL-13 NONE N NO LONG
RACDB.test RACDB.test 07-JUL-13 NONE N NO LONG
SYS$BACKGROUND 07-JUL-13 NONE N NO SHORT
SYS$USERS 07-JUL-13 NONE N NO SHORT

With srvctl utility we can obtain information about a configured service.

[oracle@racnode1 ~]$ srvctl config service -d RACDB

Service name: servicetest

Service is enabled

Server pool: racdb_servicetest

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Preferred instances: RACDB1

Available instances: RACDB2

Preferred an available instances.

Preferred instances for a service are instances in wich the service will be started, available instances are backup instances, if a preferred instance fails, the service will be started on these instances.

Management Policy.

Oracle 11 allows specifying the management policy, manual or automatic, occasionally the DBA may want to start the database services manually, with srvct utility change the management policy to MANUAL to accomplish this goal.

Changing Service goal.

Service goal consist in a classification per service, this parameter could be SHORT or LONG due to the session life.

[oracle@racnode1 ~]$ srvctl modify service -s SERVICE_NAME -d RACDB -j SHORT

[oracle@racnode1 ~]$ srvctl config service -d RACDB
Service name: servicetest
Service is enabled
Server pool: racdb_servicetest
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: SHORT
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Preferred instances: RACDB1
Available instances: RACDB2

Connection Load Balancing Goal has changed from LONG to short.

Changing DTP (Distributed Transaction Processing).

By default all services running distributed transactions will be distributed along all rac instances, this option can be disabled or enabled.

[oracle@racnode1 ~]$ srvctl modify service -s servicetest -d RACDB -x TRUE

[oracle@racnode1 ~]$ srvctl config service -d RACDB

Service name: servicetest

Service is enabled

Server pool: racdb_servicetest

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: true

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: SHORT

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Preferred instances: RACDB1

Available instances: RACDB2

[oracle@racnode1 ~]$ srvctl modify service -s servicetest -d RACDB -x FALSE

[oracle@racnode1 ~]$ srvctl config service -d RACDB

Service name: servicetest

Service is enabled

Server pool: racdb_servicetest

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: SHORT

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Preferred instances: RACDB1

Available instances: RACDB2

Changing TAF (Transparent Application Failover).

Established sessions on a database are reallocated on other database instance in case of a source instance failure.

There are three policies: NONE, BASIC and PRECONNECT.

None: No TAF policy applied.

Basic: Restarts the failed query on the new database instance upon failover.

Preconnect: The same as the Basic method but in this case Oracle anticipates connection failure creating a shadow connection on the other instance, which is always available on the available instance, to improve the failover time.

[oracle@racnode1 ~]$ srvctl modify service -s servicetest -d RACDB -P BASIC

Creating new services.

The following command creates a service called TEST that defines RACDB1 as the preferred instance, RACDB2 as available instance, and BASIC TAF policy, also configures the service to start automatically using automatic management policy.

[oracle@racnode1 ~]$ srvctl add service -d racdb -s TEST -r RACDB1 -a RACDB2 -P basic -y AUTOMATIC

[oracle@racnode1 ~]$ srvctl config service -d RACDB -s TEST

Service name: TEST

Service is enabled

Server pool: racdb_TEST

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Preferred instances: RACDB1

Available instances: RACDB2

Starting, Stoping and disabling the service.

[oracle@racnode1 ~]$ srvctl start service -d RACDB -s TEST

[oracle@racnode1 ~]$ srvctl status service -d RACDB

Service servicetest is running on instance(s) RACDB1

Service TEST is running on instance(s) RACDB1

[oracle@racnode1 ~]$ srvctl stop service -d RACDB -s TEST

[oracle@racnode1 ~]$ srvctl status service -d RACDB

Service servicetest is running on instance(s) RACDB1

Service TEST is not running.

[oracle@racnode1 ~]$ srvctl disable service -d RACDB -s TEST

[oracle@racnode1 ~]$ srvctl config service -d RACDB -s TEST

Service name: TEST

Service is disabled

Server pool: racdb_TEST

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Preferred instances: RACDB1

Available instances: RACDB2

Oracle RAC SRVCTL operations

Below, a few commands to manage databases with srvctl utility.

Show registered databases in OCR repository.

[grid@racnode1 ~]$ srvctl config database
RACDB
RCAT
RMANCAT

Show configuration details of a database.

[grid@racnode1 ~]$ srvctl config database -d racdb
Database unique name: RACDB
Database name: RACDB
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/RACDB/spfileRACDB.ora
Domain: test
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACDB
Database instances: RACDB1,RACDB2
Disk Groups: DATA,FRA
Services:
Database is administrator managed

Change management policy mode.

[grid@racnode1 ~]$ srvctl modify database -d racdb -y MANUAL
[grid@racnode1 ~]$ srvctl config database -d racdb
Database unique name: RACDB
Database name: RACDB
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/RACDB/spfileRACDB.ora
Domain: test
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: MANUAL
Server pools: RACDB
Database instances: RACDB1,RACDB2
Disk Groups: DATA,FRA
Services:
Database is administrator managed

Change start option mode.

[grid@racnode1 ~]$ srvctl modify database -d racdb -s mount
[grid@racnode1 ~]$ srvctl config database -d racdb
Database unique name: RACDB
Database name: RACDB
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/RACDB/spfileRACDB.ora
Domain: test
Start options: mount
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACDB
Database instances: RACDB1,RACDB2
Disk Groups: DATA,FRA
Services:
Database is administrator managed

Show database status.

[grid@racnode1 ~]$ srvctl status database -d racdb
Instance RACDB1 is running on node racnode1
Instance RACDB2 is not running on node racnode2

Show nodeapps status.

[grid@racnode1 ~]$ srvctl status nodeapps
VIP racnode1-vip is enabled
VIP racnode1-vip is running on node: racnode1
VIP racnode2-vip is enabled
VIP racnode2-vip is running on node: racnode1
Network is enabled
Network is running on node: racnode1
Network is not running on node: racnode2
GSD is disabled
GSD is not running on node: racnode1
GSD is not running on node: racnode2
ONS is enabled
ONS daemon is running on node: racnode1
ONS daemon is not running on node: racnode2
eONS is enabled
eONS daemon is running on node: racnode1
eONS daemon is not running on node: racnode2

Start / Stop database.

[oracle@racnode1 ~]$ srvctl start database -d racdb

[oracle@racnode1 ~]$ srvctl stop database -d racdb

Stop database with options.

[oracle@racnode1 ~]$ srvctl stop database -d racdb -o immediate

Start / Stop database on a node.

[oracle@racnode1 ~]$ srvctl start instance -d RACDB -i RACDB1

[oracle@racnode1 ~]$ srvctl stop instance -d RACDB -i RACDB1

Stop database with abort option on a node.

[oracle@racnode1 ~]$ srvctl stop instance -d RACDB -i RACDB1 -o abort

lunes, 4 de febrero de 2013

MySQL convert latin1 database to utf8

On the necessity of migrating the character set on a MySQL database, it is possible to easily achieve following these steps.

1.- Exporting database structure, it is important exporting to original character set.

mysqldump --default-character-set=latin1 --skip-set-charset -d -u root -p bd1 > bd1_estructura.sql

2.- Export data to a dump file, also exporting to original character set.
mysqldump --default-character-set=latin1 --skip-set-charset -t -u root -p bd1 > bd1_data.sql

3.- On the first dump file, change the character set with the new desired character set, in this case utf-8.

DEFAULT CHARSET=latin1
to
DEFAULT CHARSET=utf8

4.- Create the new database with utf-8 database character set.
create database bd2 default character set utf8 collate utf8_general_ci;

5.- Import structure with utf-8 character set.
mysql --default-character-set=utf8 -u root -p bd2 < bd1_estructura.sql

6.- Finally import data, also with utf-8 character set.
mysql --default-character-set=utf8 -u root -p bd2 < bd1_data.sql

domingo, 30 de septiembre de 2012

Oracle RMAN DUPLICATE database

This practical case, shows how to easily duplicate a Oracle database using RMAN.

Starting with our database, instance named TEST, in open mode, we'll create all we need to start in nomount mode a second instance named TEST2, this one will be the cloned database, finally launching copy from RMAN utility to clone the database.

1.- The first step, creating password file for the new instance (TEST2).

[oracle@oracletest ~]$ cd $ORACLE_HOME/dbs

[oracle@oracletest dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwTEST2

Enter password for SYS:

2.- New listener for TEST2 database.

Configure the listener.ora and tnsnames.ora files, to permit database TEST2 accepting connections.

[oracle@oracletest admin]$ cd $ORACLE_HOME/network/admin

[oracle@oracletest admin]$ vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = TEST)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)

(SID_NAME = TEST)

)

(SID_DESC =

(GLOBAL_DBNAME = TEST2)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)

(SID_NAME = TEST2)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

TRACE_LEVEL_LISTENER = USER

[oracle@oracletest admin]$ vi tnsnames.ora

TEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TEST)

)

TEST2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TEST2)

3.- Create pfile.

[oracle@oracletest admin]$ cd $ORACLE_HOME/dbs

[oracle@oracletest dbs]$ vi initTEST2.ora

DB_NAME=TEST2

DB_BLOCK_SIZE=8192

CONTROL_FILES=(+DATA2/test2/controlfile/control01.ctl)

DB_FILE_NAME_CONVERT=(+DATA,+DATA2)

LOG_FILE_NAME_CONVERT=(+DATA,+DATA2)

Only these above parameters are in the new database pfile, DB_NAME specifies the new name for cloned instance, db_block_size is the block size for database, the next parameter indicates the control files location, and the last two parameters are for the naming conversion for datafiles and redo log files, the new ASM disk location for this files will be +DATA2 instead the original +DATA1.

Export ORACLE_SID, and run into nomount mode the new instance TEST2.

[oracle@oracletest dbs]$ export ORACLE_SID=TEST2

[oracle@oracletest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 22:41:19 2012

Connected to an idle instance.

SQL> startup nomount pfile='?/dbs/initTEST2.ora';

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

SQL> create spfile from pfile;

File created.

4.- Start origin database (TEST) in mount or open mode.

[oracle@oracletest ~]$ echo $ORACLE_SID

TEST

[oracle@oracletest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 22:45:17 2012

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 313159680 bytes

Fixed Size 2212936 bytes

Variable Size 163580856 bytes

Database Buffers 142606336 bytes

Redo Buffers 4759552 bytes

Base de datos montada.

5.- Connect to RMAN and executing DUPLICATE command to start with the clone process.

Connect to RMAN on the origin database (TEST) , from here connecting to the new database using the following command: CONNECT AUXILIARY.

Once DUPLICATE command is running, RMAN is responsible for doing all the job.

[oracle@oracletest ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 22:46:32 2012

RMAN> connect target sys@TEST

target database Password:

connected to target database: TEST (DBID=2093094325, not open)

RMAN> connect auxiliary sys@TEST2

auxiliary database Password:

connected to auxiliary database: TEST2 (not mounted)

RMAN> duplicate target database to TEST2 from active database;

Starting Duplicate Db at 29-SEP-2012 22:57:20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''TEST'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''TEST2'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '+DATA2/test2/controlfile/control01.ctl';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''TEST2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

Starting backup at 29-SEP-2012 22:59:41

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/db/dbs/snapcf_TEST.f tag=TAG20120929T225952 RECID=15 STAMP=795308405

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

Finished backup at 29-SEP-2012 23:00:28

database mounted

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

set newname for datafile 1 to

"+data2";

set newname for datafile 2 to

"+data2";

set newname for datafile 3 to

"+data2";

set newname for datafile 4 to

"+data2";

backup as copy reuse

datafile 1 auxiliary format

"+data2" datafile

2 auxiliary format

"+data2" datafile

3 auxiliary format

"+data2" datafile

4 auxiliary format

"+data2" ;

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 29-SEP-2012 23:01:06

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/test/datafile/system.276.794947707

output file name=+DATA2/test2/datafile/system.257.795308479 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/test/datafile/sysaux.277.794947709

output file name=+DATA2/test2/datafile/sysaux.256.795308565 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/test/datafile/undotbs1.278.794947711

output file name=+DATA2/test2/datafile/undotbs1.259.795308631 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/test/datafile/users.259.795088363

output file name=+DATA2/test2/datafile/users.260.795308657 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 29-SEP-2012 23:04:21

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=15 STAMP=795308662 file name=+DATA2/test2/datafile/system.257.795308479

datafile 2 switched to datafile copy

input datafile copy RECID=16 STAMP=795308662 file name=+DATA2/test2/datafile/sysaux.256.795308565

datafile 3 switched to datafile copy

input datafile copy RECID=17 STAMP=795308662 file name=+DATA2/test2/datafile/undotbs1.259.795308631

datafile 4 switched to datafile copy

input datafile copy RECID=18 STAMP=795308663 file name=+DATA2/test2/datafile/users.260.795308657

contents of Memory Script:

{

set until scn 1092216;

recover

clone database

noredo

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 29-SEP-2012 23:04:24

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 device type=DISK

Finished recover at 29-SEP-2012 23:04:39

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''TEST2'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

sql statement: alter system set db_name = ''TEST2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST2" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '+data2' ) SIZE 50 M REUSE,

GROUP 2 ( '+data2' ) SIZE 50 M REUSE,

GROUP 3 ( '+data2' ) SIZE 50 M REUSE

DATAFILE

'+DATA2/test2/datafile/system.257.795308479'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"+data2";

switch clone tempfile all;

catalog clone datafilecopy "+DATA2/test2/datafile/sysaux.256.795308565",

"+DATA2/test2/datafile/undotbs1.259.795308631",

"+DATA2/test2/datafile/users.260.795308657";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data2 in control file

cataloged datafile copy

datafile copy file name=+DATA2/test2/datafile/sysaux.256.795308565 RECID=1 STAMP=795308819

cataloged datafile copy

datafile copy file name=+DATA2/test2/datafile/undotbs1.259.795308631 RECID=2 STAMP=795308819

cataloged datafile copy

datafile copy file name=+DATA2/test2/datafile/users.260.795308657 RECID=3 STAMP=795308820

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=795308819 file name=+DATA2/test2/datafile/sysaux.256.795308565

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=795308819 file name=+DATA2/test2/datafile/undotbs1.259.795308631

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=795308820 file name=+DATA2/test2/datafile/users.260.795308657

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 29-SEP-2012 23:07:24

miércoles, 26 de septiembre de 2012

PRCR-1709 / ORA-01031 Insufficient privileges en Oracle Standalone ASM

Al realizar una instalación de Oracle Database 11g con ASM, previamente instalando Grid Infraestructure, con roles separados, por un lado Grid infraestructure con el usuario grid, y Oracle Database con usuario Oracle, nos podemos encontrar con un error como este al crear la base de datos.

PRCR-1079 : Failed to start resource ora.test.db
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
CRS-2674: Start of 'ora.test.db' on 'oracletest' failed

Sucede al intentar levantar automáticamente el recurso "ora.test.db" por parte del usuario grid, por falta de permisos, para solucionarlo debemos agregar al usuario grid al grupo "dba", o hacer toda la instalación (Grid Infraestructure y Oracle Database) con el mismo usuario.

miércoles, 1 de agosto de 2012

Oracle RAC CRS Resources operations

On the previous post we saw how to install an Oracle RAC environment for testing purposes, in this section you will find some commands to manage your Oracle RAC infraestructure.

Stop CRS

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop crs

CRS-2791: Iniciando cierre de los recursos gestionados por los Servicios de Alta Disponibilidad de Oracle en 'racnode1'

CRS-2673: Intentando parar ''ora.crsd'' en ''racnode1''

CRS-2790: Iniciando cierre de los recursos gestionados por Cluster Ready Services en 'racnode1'

CRS-2673: Intentando parar ''ora.CRS.dg'' en ''racnode1''

CRS-2673: Intentando parar ''ora.racdb.servicetest.svc'' en ''racnode1''

CRS-2677: La parada de ''ora.gipcd'' en ''racnode1'' se ha realizado correctamente

CRS-2677: La parada de ''ora.diskmon'' en ''racnode1'' se ha realizado correctamente

CRS-2793: Ha terminado el cierre de los recursos gestionados por los Servicios de Alta Disponibilidad de Oracle en 'racnode1'

CRS-4133: Oracle High Availability Services has been stopped.

Start CRS

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl start crs

CRS-4123: Oracle High Availability Services has been started.

CRS resources status

[root@racnode2 ~]# /u01/app/11.2.0/grid/bin/crsctl stat res -t

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

NAME TARGET STATE SERVER STATE_DETAILS

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

Local Resources

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

ora.CRS.dg

ONLINE ONLINE racnode2

ora.DATA.dg

ONLINE ONLINE racnode2

ora.FRA.dg

ONLINE ONLINE racnode2

ora.LISTENER.lsnr

ONLINE ONLINE racnode2

ora.asm

ONLINE ONLINE racnode2

ora.eons

ONLINE ONLINE racnode2

ora.gsd

OFFLINE OFFLINE racnode2

ora.net1.network

ONLINE ONLINE racnode2

ora.ons

ONLINE ONLINE racnode2

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE racnode2

ora.oc4j

1 OFFLINE OFFLINE

ora.racdb.db

1 ONLINE OFFLINE

2 ONLINE ONLINE racnode2

ora.racdb.servicetest.svc

1 ONLINE ONLINE racnode2

ora.racnode1.vip

1 ONLINE INTERMEDIATE racnode2

ora.racnode2.vip

1 ONLINE ONLINE racnode2

ora.scan1.vip

1 ONLINE ONLINE racnode2

The Legacy way.

root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crs_stat -t

Name Type Target State Host

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

ora.CRS.dg ora....up.type ONLINE ONLINE racnode1

ora.DATA.dg ora....up.type ONLINE ONLINE racnode1

ora.FRA.dg ora....up.type ONLINE ONLINE racnode1

ora....ER.lsnr ora....er.type ONLINE ONLINE racnode1

ora....N1.lsnr ora....er.type ONLINE ONLINE racnode2

ora.asm ora.asm.type ONLINE ONLINE racnode1

ora.eons ora.eons.type ONLINE ONLINE racnode1

ora.gsd ora.gsd.type OFFLINE OFFLINE

ora....network ora....rk.type ONLINE ONLINE racnode1

ora.oc4j ora.oc4j.type OFFLINE OFFLINE

ora.ons ora.ons.type ONLINE ONLINE racnode1

ora.racdb.db ora....se.type ONLINE ONLINE racnode1

ora....est.svc ora....ce.type ONLINE ONLINE racnode2

ora....SM1.asm application ONLINE ONLINE racnode1

ora....E1.lsnr application ONLINE ONLINE racnode1

ora....de1.gsd application OFFLINE OFFLINE

ora....de1.ons application ONLINE ONLINE racnode1

ora....de1.vip ora....t1.type ONLINE ONLINE racnode1

ora....SM2.asm application ONLINE ONLINE racnode2

ora....E2.lsnr application ONLINE ONLINE racnode2

ora....de2.gsd application OFFLINE OFFLINE

ora....de2.ons application ONLINE ONLINE racnode2

ora....de2.vip ora....t1.type ONLINE ONLINE racnode2

ora.scan1.vip ora....ip.type ONLINE ONLINE racnode2

Stop cluster on current node.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster

Stop cluster on a node.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster -n racnode2

Stop cluster on both nodes.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster -all

Start cluster.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster -all

Start Cluster on a node.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster -n racnode2

Check Cluster status.

root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl check cluster

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

Check CRS status.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

Check OHASD Daemon.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl check has

CRS-4638: Oracle High Availability Services is online

Disable CRS Daemon.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl disable crs

Enable CRS Daemon.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl enable crs

Disable Cluster resource.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl disable resource ora.rcat.db

Enable Cluster resource.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl enable resource ora.rcat.db

Delete Cluster resource.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl delete resource ora.rcat.db

Get permissions from a cluster resource.

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl getperm resource ora.racdb.db -g dba

Name: ora.racdb.db

r--

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crs_getperm ora.racdb.db -g dba

Name: ora.racdb.db

r--

Get Oracle Clusterware versions.

root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl query crs softwareversion

Oracle Clusterware version on node [racnode1] is [11.2.0.1.0]

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.2.0.1.0]

[root@racnode1 ~]# /u01/app/11.2.0/grid/bin/crsctl query crs releaseversion

Oracle High Availability Services release version on the local node is [11.2.0.1.0]