comandos huawei cisco

AIT Pequiven Morn Soporte Especializado

Fichero: Oracle1 Fecha: 30.08.2007 Versin: V1.0.0 Pagina 1 de 15

Estrategia para la copia Homognea en Oracle

Autor: Equipo BASIS Ral Colmenares

AIT Morn Soporte E.



Procedimiento para homogneo system copy en Oracle

Del servidor origen crear un controlfile de manera de texto, con la instruccin:

1. Entrar en oracle con sqlplus "/ as sysdba"

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oracle/backup/control.bkp';

2. Se toma el archivo que se produjo en la direccin que Ud. escogi y lo Modifica colocndole el nuevo ID del servidor destino En este caso es prod , recuerde borrar las lneas sobrantes solo dejar apartir del comando CREATE y al final del scripts borrar tod lo que esta despus de ARCHIVELOG.

3. CREATE CONTROLFILE SET DATABASE "PPQ" RESETLOGS NOARCHIVELOG

CREATE CONTROLFILE SET DATABASE prod LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', '/u01/oracle/prod/redo01_02.log'), GROUP 2 ('/u01/oracle/prod/redo02_01.log', '/u01/oracle/prod/redo02_02.log'), GROUP 3 ('/u01/oracle/prod/redo03_01.log', '/u01/oracle/prod/redo03_02.log') NORESETLOGS DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M, '/u01/oracle/prod/rbs01.dbs' SIZE 5M, '/u01/oracle/prod/users01.dbs' SIZE 5M, '/u01/oracle/prod/temp01.dbs' SIZE 5M MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXLOGHISTORY 400 MAXDATAFILES 200 MAXINSTANCES 6 ARCHIVELOG;

4. Copiar este archivo al servidor destino

5. Hacer backup de todos los file system sapdata.

En el servidor destino



1. Hacer restore de los file system ///sapdataxxxx 2. Verificar o cambiar el ID nuevo en los filesystem. 3. Copiar y modificar con el ID del archivo initID.ora en $ORACLE_HOME/dbs

a. Si el arhivo se llama diferente renmbrelo con el Id correspondiente. 4. Entrar en oracle con sqlplus "/ as sysdba" 5. Se sube la base de datos en modo nomunt

sqlplus > startup nomount

6. Se ejecuta el archivo que se modifico, por ejemplo si se llama controlfile.bck

sqlplus > start controlfile.bck o @controfile.sql parado en el directorio si la extensin es SQL

# Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE # Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; # No tempfile #

Cuando termine ya tiene un nuevo control file

7. Baja la base de datos con un shutdown immediate entries found to add. sqlplus > shutdown immediate

8. Copie los controlfile en los sitios que indica el init de base de datos. .

9. Se sube la base de datos

sqlplus > startup

Si no hay problemas con los nombres de los archivos y sus direcciones en los filesystem, la base de datos sube sin problemas.

Crear PSAPTEMP

alter tablespace PSAPTEMP add tempfile '/oracle/Q01/sapdata1/temp_3/temp.data3' size 10000M

Si hay problema de coneccin realice los siguiente:



ejecutar R3trans -d y verifique si genera error 0012 si es asi entonces debe realizar la siguiente tarea:

1.- Crear el usurio OPS$$SIDADM 2.- Crear Tabla SAPUSER 3.- copie los datos de la tabla anterior del otro usuario ID.

Y vuelva aprobar.

Problemas al conectarse con la base de datos NOTA 403004 ORA-01004: default username feature not supported; logon denied ORA-01017: Invalid username/password; logon denied ORA-01031: insufficient privileges ORA-01403: No data found ORA-00942: Table or view does not exist ORA-00980: synonym translation is no longer valid

BR301W SQL error -1017 at location BrDbConnect-1 BR310W Connect to database instance failed

Reason and Prerequisites Note: This note does NOT deal with configuring the OPS$ user for JAVA-based applications. These do not use an OPS$ user as standard. If you still require an OPS$ user to execute BR*TOOLS, however, you can create it in the context of the sapdba_additional.sql script as described in Notes 675940 (6.40 WINDOWS), 668604 (6.40 UNIX), 849141 (NW2004S WINDOWS) and 851169 (NW2004S UNIX).

R/3 work processes and R/3 tools such as R3trans often need the option of connecting to the database using the user sapr3. This can happen in one of the following ways:

1. Connect via OPS$ user a) Logon using the OPS$ user ("connect /@") to determine the sapr3 password defined in the database table SAPUSER b) Logon using the given password as sapr3, if 1a) was successful: "connect sapr3/@"

2. If the default password "sap" is still set for sapr3: direct logon using "connect sapr3/sap@" to the database

The OPS$ user is thus a safety mechanism, which allows a password stored in the database for sapr3 to be determined, in order to use this in the next step for the actual logon to the database using sapr3. The connect using the OPS$ user ("connect /@") can only work if a user exists in the database



which is created from the concatenation of OS_AUTHENT_PREFIX = ops$ (see init.ora) and the name of the operating system user being used. For example:

OS user: c11adm OS_AUTHENT_PREFIX: ops$ DB user: ops$c11adm

On the other hand, in the following three cases the OPS$ connect would fail:

OS user: c11adm orac11 c11adm os_authent_prefix: ops ops$ ops$ DB user: ops$c11adm ops$c11adm ops$prdadm

Of course, it is also possible to create more than one OPS$ user in the database.

On NT with Oracle 8.1.5 or higher, make sure that the OPS$ user also contains the domain name (or the local host, if the database server does not belong to any domain): OPS$\. You can determine the domain or host name using the WINDOWS environment variable USERDOMAIN.

If the connect in accordance with 1a) and 1b) was successful, the corresponding log file (for example dev_wX or trans.log) contains the following entries:

Logon as OPS$-user to get SAPR3's password Connecting as /@ on connection 0 ...



password from the SAPUSER table was used in the first case and the standard password sap in the second case. The preceding line, which specifies which of the passwords is used for the connect, is important here.

If one of the two connect methods fails, this is usually not a problem, as long as the other method works. This mean, to ensure that the connect is successful, either set the standard password for sapr3 or set up the mechanism correctly using the OPS$ user.

Note that the log messages may vary slightly in the case of different R/3 releases. Besides, the reverse sequence was valid with earlier R/3 releases: first the connect was carried out using the standard password and the OPS$ mechanism was only used if that failed.

Notes 50088 (NT) and 361641 (UNIX) describe ways of setting up the OPS$ mechanism. However, problems often occur that cannot be corrected without background knowledge of the available tools. For this reason, there now follows a collection of typical errors and remedies.

BR*TOOLS such as BRBACKUP, BRCONNECT and BRRECOVER also access the database using the OPS$ user if you also specify the option "-u /". This is always the case from R/3 (for example, from Transaction DB13). Unlike the R/3 work processes, however, the system uses the OPS$ user directly, rather than only using it to determine the sapr3 password. For this reason, the system only executes step 1a) for these tools (logon as the OPS$ user). After correctly creating the OPS$ user, the current version of the SAPDBA role must be imported (as described in Note 134592) to ensure that the BR*TOOLS function properly.

If the OPS$ user has been set up correctly or the BR*TOOLS, the corresponding operating system user should be able to log on using "sqlplus /". In addition, the OPS$ user must have the SAPDBA role assigned. This can be checked using the following call:

CONNECT / AS SYSDBA; SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '';

The result must contain a line with SAPDBA.

As of R/3 6.10, you CAN also use an alternative user and tablespace setup. The most important change in this regard is the use of the SID-dependent SAP instead of user SAPR3. You can use the following query to determine the user to which the R/3 objects actually belong:

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'T000';

The reason for this change is the option if keeping several R/3 systems with different SIDs in one database where each system naturally needs its own user. If the new layout is used, SAPR3 is no longer used even for the connect, but SAP is used. To make this also known to R3trans / saplicense and the work processes, the following environment variable must be set:



dbs_ora_schema = SAP

In particular, make sure that "SAP" is written in uppercase letters only. With 6.10, the log entries described above have also slightly changed. The basic mechanism remains, however, and you can quite easily map the new entries in trans.log or in the dev traces to the logs described in this note.

The user is also influenced by variable auth_shadow_upgrade = 1 which, in an upgrade with access to the shadow instance, switches the normal user "sapr3" to "sapr3shd". This variable must NEVER (not even during an upgrade) be set in the environment or in the registry. Solution Execute all of the SQL statements stated as ora (UNIX) or adm (NT) with SQLPLUS and "CONNECT / AS SYSDBA".

On NT with Oracle >= 8.1.5, complete the domain at all places in the following SQL commands in which the OPS$ user occurs. For example: OPS$ \ADM rather than OPS$ADM. In the case of a non-domain user, the name of the local host replaces the domain name.

Below, refers to the owner of the R/3 tables, that is SAPR3 or SAP (depending on the setup). General checks

Check whether creating the OPS$ mechanism according to Note 50088 (WINDOWS) or 361641 (UNIX) solves the problem.

Table SAPUSER should occur in the system once only and be assigned to user OPS$ADM. To check this, use the following query:

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

If the system returns an owner other than OPS$ADM, then delete the corresponding SAPUSER tables:

DROP TABLE "".SAPUSER;

If the system does not return OPS$ADM, create the SAPUSER table as adm and enter the password:

CREATE TABLE "OPS$ADM".SAPUSER (USERID VARCHAR2(256), PASSWD VARCHAR2(256)); INSERT INTO "OPS$ADM".SAPUSER VALUES ('', '');



This section applies to NT in particular. But if more than one OPS$ user is used under UNIX as well, the checks need to be carried out in the same way.

Under NT, it is required that user sapservice can also access the SAPUSER table. In order to avoid problems with the data consistency, it does not make sense to create an additional SAPUSER table having the same contents. Instead, you should define a synonym. Check if a suitable synonym exists by using the following call:

SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME = 'SAPUSER';

As first value, this call should return either OPS$SAPSERVICE or PUBLIC, followed by OPS$ADM and SAPUSER. If this is not the case, recreate the synonym after you have deleted it:

If PUBLIC is returned as first value:

DROP PUBLIC SYNONYM SAPUSER;

If another name is returned as first value:

DROP SYNONYM "".SAPUSER;

Now you can recreate the synonym (It is best not to use PUBLIC):

CREATE SYNONYM "OPS$SAPSERVICE".SAPUSER FOR "OPS$ADM".SAPUSER;

To allow access to the synonym (or the associated table), a grant has to be executed. Only the OPS$ user to whom the actual table belongs has the authorization to do this - that is OPS$ADM. Therefore, you must log on with the corresponding operating system user (adm) and execute the following commands:

CONNECT / GRANT SELECT, UPDATE ON SAPUSER TO "OPS$SAPSERVICE";

By default, R3trans uses SAPR3 as database user. With the environment variable, you can define another database user. This makes sense in particular with R/3 6.x, if SAPR3 is replaced by SAP. If with R/3



The OPS$ mechanism is not activated in Oracle. To make the general option of an OPS$ connect available, proceed as follows:

Set parameter REMOTE_OS_AUTHENT = TRUE

in init.ora.

Restart the database.

On Windows, you must also set the parameter "SQLNET.AUTHENTICATION_SERVICE = (NTS)" in sqlnet.ora. ORA-01017: Invalid username/password; logon denied Message ORA-01017 can occur both in step 1a) and in step 1b) or 2). Depending on this, the problem can be corrected as follows:

1a) log entries: Logon as OPS$ user to get 's password Connecting as /@ on connection 0 ... *** ERROR => OCI-call 'olog' failed: rc = 1017 *** ERROR => CONNECT failed with sql error '1017' If you intend to use the standard password for anyway, you can ignore the error message at this point, as the OPS$ mechanism is not needed at all and the connect is carried out using /sap successfully afterwards. Note, however, that BR*TOOLS require a working OPS$ mechanism when executing via DB13! Otherwise, make sure that an appropriate OPS$ user is set up. To do this, proceed as follows:

Check whether the parameter

os_authent_prefix = ops$

is set correctly in init.ora. If you have to make a change, restart the database afterwards.

On Windows, make sure that the following parameter is set in sqlnet.ora: SQLNET.AUTHENTICATION_SERVICES = (NTS)

Determine which operating system user wants to create the connection. If this involves a connect initiated out of the R/3 System (for example, work process connect, DB13 actions), user adm is used under UNIX and user sapservice under NT. If you manually called the program that executes the connect, the user you are using is decisive here.

Use SVRMGRL to check whether the user OPS$ (or OPS$\ for NT with Oracle >= 8.1.5) has been created at database level:



SELECT * FROM DBA_USERS WHERE USERNAME = 'OPS$'; (for , always use upper case)

If the system does not return an entry, create the user:

CREATE USER "OPS$" DEFAULT TABLESPACE SAPSR3USR TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;

Note that the name of the OPS$ user must be specified completely in uppercase letters (also the host and domain names in case of NT and W2K), since a logon as OPS$ user will not work if it is not:

wrong: CREATE USER "OPS$c11adm" DEFAULT TABLESPACE ... CREATE USER "OPS$sapdom\C11ADM" DEFAULT TABLESPACE ... correct: CREATE USER "OPS$C11ADM" DEFAULT TABLESPACE ... CREATE USER "OPS$SAPDOM\C11ADM" DEFAULT TABLESPACE ...

Tablespace is used when newly creating objects, when no explicit tablespace name is specified. In connection with R/3, this is almost never the case. Therefore, you can specify any tablespace. Proposal:

R/3 = 6.10: = PSAPUSR

Check whether the OPS$ user has sufficient authorizations. The following statement must at least return CONNECT and RESOURCE:

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'OPS$';

If CONNECT and/or RESOURCE is missing, you can grant these authorizations as follows:

GRANT CONNECT, RESOURCE TO "OPS$"; If all the settings described above have been made correctly on NT/ Windows 2000, but the

error ORA-01017 is still displayed during the OPS$ Connect, the cause may be a start user set incorrectly for the SAP Service. Change to the services overview of the operating system and check the user who starts the service SAP_. If the user is not sapservice, enter sapservice. Other users (such as the local administrator) do not have an OPS$ user in the database, which leads to error ORA-01017. Check also whether the start user sapservice is a local user or a domain user. Domain users can be identified by the preceding domain name. Depending on this, the following OPS$ user should exist on database level:



o Domain user: \sapservice OPS$ user: OPS$\sapservice

o Local user: sapservice OPS$ user: OPS$\sapservice

1b) log entries: Logon as OPS$ user to get 's password Connecting as /@ on connection 0 ... Now I'm connected to ORACLE Got 's password from table SAPUSER Disconnecting from connection 0 ... Now I'm disconnected from ORACLE Try to connect with the password I got from OPS$-user Connecting as /@ on connection 0 *** ERROR => OCI-call 'olog' failed: rc = 1017 *** ERROR => CONNECT failed with sql error '1017' An ORA-01017 message at this point indicates an incorrect password in the SAPUSER table. In this situation, proceed as follows.

Set the password consistently in SAPUSER and in the Oracle DDIC using the following BRCONNECT call (BRCONNECT >= 6.10): brconnect -u system/ -f chpass -o -p

Otherwise, change the password in the SAPUSER table:

DELETE FROM "OPS$ADM".SAPUSER; INSERT INTO "OPS$ADM".SAPUSER VALUES ('', '');

Important: The SAPUSER table should only exist once in every system: for OPS$ADM. For this reason, only execute the above-mentioned statements for OPS$ADM - irrespective of the operating system user actually being used. All other OPS$ users can access the SAPUSER table using a synonym.

If the connect still terminates in the same way, check once more the section "General checks".

2) log entries: Try to connect with default password Connecting as /@ on connection 0 *** ERROR => OCI-call 'olog' failed: rc = 1017 *** ERROR => CONNECT failed with sql error '1017'

If you want to use the standard password sap and do not need the OPS$ mechanism, reset the password to sap:

ALTER USER IDENTIFIED BY sap; sapowner = sapr3



If you do not want to use the standard password, the error cannot be avoided at this point. Set up the OPS$ mechanism as described above instead.

If the error ORA-01017 occurs when setting up a secondary database connection, refer to Note 569302.

ORA-01031: insufficient privileges

Log entries: Logon as OPS$-user to get 's password Connecting as /@ on connection 0 ... Now I'm connected to ORACLE *** ERROR => ORA-1031 when accessing table SAPUSER

Check whether the grants (especially SELECT) on SAPUSER are set correctly for the OPS$ user as described in the above section "General checks". ORA-01403: No data found


The main cause for the ORA-01403 message is that table SAPUSER does not contain any entries. Proceed as follows:

Set the password consistently in SAPUSER and the Oracle Dictionary using the following BRCONNECT call (BRCONNECT >= 6.10):

brconnect -u system/ -f chpass -o -p

Alternatively, you can add it to SAPUSER manually.

INSERT INTO "OPS$ADM".SAPUSER VALUES ('', ''); Another possible cause is that incorrect entries are contained in SAPUSER. Selection from SAPUSER is always made using the user name. For this reason, make sure that the user name is always written in uppercase (SAPR3 or SAP) and that it exactly corresponds to the value of the environment variable dbs_ora_schema if SAP is used.

If the ORA-01403 still occurs, check once more the above-mentioned section "General checks".

ORA-00942: Table or view does not exist

Log entries: Logon as OPS$-user to get 's password



Connecting as /@ on connection 0 ... Now I'm connected to ORACLE *** ERROR => ORA-942 when accessing table SAPUSER

Message ORA-00942 is issued when table SAPUSER does not exist. Proceed as follows:

Create the SAPUSER table as adm using the following command:

CREATE TABLE "OPS$ADM".SAPUSER (USERID VARCHAR2(256), PASSWD VARCHAR2(256)); INSERT INTO "OPS$ADM".SAPUSER VALUES ('', '');

If the error still occurs, check once more the above-mentioned section "General checks". ORA-00980: synonym translation is no longer valid


ORA-00980 occurs, if a synonym for SAPUSER exists but is no longer valid for any reason (for example because it points to a non-existant object).

Therefore, create the synonym for SAPUSER again as described in the above section "General checks".

Header Data

Release Status: Released for Customer Released on: 29.03.2006 06:39:00 Priority: Recommendations/additional info Category: Help for error analysis Primary Component: BC-DB-ORA Oracle Secondary Components: BC-DB-ORA-DBA Database Administration with Oracle Affected Releases

Release-Independent

Related Notes

969519 - 6.20/6.40 Patch Collection Installation : Oracle/UNIX



951167 - ORA-28000: the account is locked

700548 - FAQ: Oracle authorizations

675940 - SAP Web AS 6.40 Installation on Windows: Oracle

668604 - SAP Web AS 6.40 ABAB/Java Installation on UNIX: Oracle

662644 - Composite SAP Note: ORA-00942

650796 - Composite Note: ORA-00980

569302 - Loading data in BW fails with ORA-01017

562863 - FAQ: Logon mechanisms

437648 - DB13: External program terminated with exit code 1/2

403004 - Database connect using R3trans fails

361641 - Creating OPS$ users on UNIX

134592 - Importing the SAPDBA role ( sapdba_role.sql) Print Selected Notes (PDF)

Con la transaccin BDLS cambiar el sistema lgico

To change the password for the SAPR3 or SAP user you have to use the following command: brconnect -u system/ -f chpass -o SAPR3 -p



or brconnect -u system/ -f chpass -o sap< sid> -p

alter database OPQ noarchivelog;

comandos huawei cisco

Documents